How to find the difference between two timestamp string fields in two different indexes?

[varunnair26]

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

[somesoni2]

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.

[varunnair26]

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

[gfuente · Accepted solution]

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

[varunnair26]

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

[varunnair26]

@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

[ppablo]

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

[varunnair26]

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.

[varunnair26]

@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.

[gfuente]

Can you post some sample events? or at least the contents of those fields

Thanks

[gfuente]

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