Splunk Search
Highlighted

How to search the difference between the current time and an indexed timestamp?

Builder

alt text
Hello Experts,

I have an indexed timestamp createdate and I want to find the difference between the latest timestamp of createdate with the current date. I am using the search below:

index=abc |stats latest(createddate) as latest|  eval current_time=strftime(now(),"%B %d, %Y %H:%M") | table current_time,latest

result:

current_time                   latest
January 10, 2017 10:40         2017-01-10 15:02:56.08

I want to find the difference between them ...thank you

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

SplunkTrust
SplunkTrust

To be able to find the difference, both timestamp should be in epoch format. The current time now() is already epoch so you just need to convert (at least during calculation of different) createdate field to epoch. Try something like this

Update
The field name of createdate was changed to latest and we should be using latest in difference calculation.
Fixed timestamp format.

index=abc |stats latest(createddate) as latest | eval difference=now()-strptime(latest,"%Y-%m-%d %H:%M:%S.%N") | eval current_time=strftime(now(),"%B %d, %Y %H:%M") | table current_time,latest,difference

View solution in original post

Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

Builder

it did not work

current_time latest difference
January 10, 2017 11:12 2017-01-10 15:33:42.493

The difference was blank no result and above are the current and latest time for the query ran

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

Builder

Hello Somesh,I have attached an image which shows the results

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

SplunkTrust
SplunkTrust

typo problem, missing d in the second createddate -

index=abc |stats latest(createddate) as latest | eval difference=now()-strptime(createddate,"%B %d, %Y %H:%M") | eval current_time=strftime(now(),"%B %d, %Y %H:%M") | table current_time,latest,difference
0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

SplunkTrust
SplunkTrust

The name of the field createddate was changed to latest after the stats. Try the updated search.

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

Builder

Well I tried the updated one but it is still the same the difference field is blank and none of fields are converted

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

SplunkTrust
SplunkTrust

My bad, I followed the wrong field for timestamp format. Please try the updated answer now.

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

Builder

Thanks a lot somesh it worked well ,but I have a time stamp in another source type which is different ..how can we do for that

currenttime=January 10, 2017 14:05
latest
time=2017-01-10T19:01:41.2649252Z

How to find the difference for this @someshsoni2?

0 Karma
Highlighted

Re: How to search the difference between the current time and an indexed timestamp?

SplunkTrust
SplunkTrust

This is the place where we're converting the latest_time which is human-readable format to epoch, using strftime. YOu need to ensure that the timeformat specified in the strftime command is matching the format of the field.

eval difference=now()-strptime(latest,"%Y-%m-%d %H:%M:%S.%N")

For latest_time=2017-01-10T19:01:41.2649252Z, timeformat will be "%Y-%m-%dT%H:%M:%S.%NZ". See below link for different portions for time format that should be used.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

0 Karma