Hi,
In my Splunk instance there are two indexes which I need to use for arithmetic operations on the timestamp fields of the logs. For example, first index contains logs set with timestamp field "In Swipe" in format "dd/mm/yy hh:mm:ss", and the other index logs set have timestamp field "Login Time" in same format "dd/mm/yy hh:mm:ss". I need to take the difference between these two fields and check whether it is a negative or a positive value (below mentioned operation).
Login Time - In Swipe > 0
I was trying with WHERE and EVAL command, but both of them take only numbers for any arithmetic operation.
Please suggest.
Thanks
Hello
You need to transform those timestamps to epoch and then do the operations. Like:
... yourbase search | eval swipe=strptime(swipe_time,"%d/%m/%y %H:%M:%S) | eval login=strptime(login_time,"%d/%m/%y %H:%M:%S) | eval diff= swipe_time - login_time | where diff >0
Regards
Queries:
1) Do the field "In Swipe" and "Login Time" available on same event OR they come from different event? [Any eval operation is done per event/row so if both fields are not available in same row, you'll not get the desired result.]
2) Format of datetime fields is expected to be"%d/%m/%y %H:%M:%S" (based on your search). But based on your sample data in one of comments, the data is in "%d/%m/%y %H:%M" (no seconds). The strptime function need the format to match exactly.
1) Both events are from different indexes.
2) I have corrected the format. (It is working when I tried for same index comparison)
But I am trying to compare two events from different indexes, using the "EmpID" as a reference which is present in both the index logs.
Thanks
Hello
You need to transform those timestamps to epoch and then do the operations. Like:
... yourbase search | eval swipe=strptime(swipe_time,"%d/%m/%y %H:%M:%S) | eval login=strptime(login_time,"%d/%m/%y %H:%M:%S) | eval diff= swipe_time - login_time | where diff >0
Regards
This issue is resolved. i have used transaction to group the indexes then compared their timestamps.
Thank you @gfuente, @ppablo_splunk for your help regarding the timestamp format.
rgds
Varun
@gfuente: I tried this also.
|eval swipe=strptime($In Swipe$,'%d/%m/%y %H:%M:%S') | eval login=strptime(LoginTime,'%d/%m/%y %H:%M:%S') | eval diff = swipe-login | where diff > 0
Still it says, " no result"
Below are the sample of logs:
Index 1(TimeStamp field=In Swipe😞
EmpID,Asset,EmpName,3/11/2015 23:55
Index 2(TimeStamp field=LoginTime😞
EmpID,CardNumber,EmpName,3/11/2015 22:18
In these logs, I need difference of the timestamp fields, so that I can check the sequence of logs for the same EmpID, whether LoginTime is prior to In Swipe.
Thanks
Hi @varunnair26
Are you sure the format of your timestamps is actually "dd/mm/yy hh:mm:ss"? I'm guessing these logs are not from November 3, 2015, but are actually March 11, 2015 if you are using real dates in the sample logs you provided. If yes, I bet that's the problem because you're attempting to transform a future timestamp with %d/%m when really it should be %m/%d. Can you try:
...your base search... | eval swipe=strptime($In Swipe$,'%m/%d/%y %H:%M:%S') | eval login=strptime(LoginTime,'%m/%d/%y %H:%M:%S') | eval diff = swipe-login | where diff > 0
Also, are there seconds in any of the timestamps? If not then you should use:
...your base search... | eval swipe=strptime($In Swipe$,'%m/%d/%y %H:%M') | eval login=strptime(LoginTime,'%m/%d/%y %H:%M') | eval diff = swipe-login | where diff > 0
Yes, @ppablo_splunk, you are right. I have corrected the format. Now it is working( for same indexes), but I want to compare for two different indexes using "EmpID" which is present on both index logs.
Please suggest.
Thanks.
@gfuente: Thanks for the reply. Below is the exact search which I am trying:
|eval swipe=strptime("In Swipe",'%d/%m/%y %H:%M:%S') | eval login=strptime(LoginTime,'%d/%m/%y %H:%M:%S') | eval diff = swipe-login | where diff > 0
But it says, "no result". I have tried reverted where condition (where diff < 0) also, still it says "no result". It should populate result in either ways.
Please suggest what I am doing wrong here.
Thanks.
Can you post some sample events? or at least the contents of those fields
Thanks
By the way there is an error on the first eval:
("In Swipe"
You can't use " as splunk thinks is a string. If your field has spaces, use $ to surround it, like: $In Swipe$
Regards