topic Re: How to find the difference between two timestamp string fields in two different indexes? in Splunk Search

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

varunnair26 — Wed, 18 Mar 2015 08:50:44 GMT

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

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

gfuente — Wed, 18 Mar 2015 08:55:55 GMT

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

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

varunnair26 — Wed, 18 Mar 2015 10:02:57 GMT

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

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

gfuente — Wed, 18 Mar 2015 10:41:56 GMT

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

Thanks

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

gfuente — Wed, 18 Mar 2015 10:43:34 GMT

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

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

varunnair26 — Wed, 18 Mar 2015 11:09:00 GMT

@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

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

ppablo — Wed, 18 Mar 2015 18:01:50 GMT

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

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

somesoni2 — Wed, 18 Mar 2015 18:24:02 GMT

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.

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

varunnair26 — Thu, 19 Mar 2015 07:32:24 GMT

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

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

varunnair26 — Thu, 19 Mar 2015 08:07:54 GMT

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.

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

varunnair26 — Wed, 25 Mar 2015 06:23:10 GMT

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