Splunk Search

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
1 Solution

Revered Legend

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

Revered Legend

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

Path Finder

Thanks alot

0 Karma

Community Manager
Community Manager

Hi @nazanin2016

Please don't forget to resolve your question by clicking "Accept" directly below @somesoni2's answer. Also, upvote the answer and/or comments that were helpful.

cheers

0 Karma

Path Finder

Thanks I did it but then how I can compare the result and say for example it is greater that 24hours?

0 Karma

Revered Legend

Once you've the difference based on epoch value of the EndTime and StarTime, which will be in seconds, you can just add | where diff>=86400 to filter the results where diff is greater than 1 day.

0 Karma

Path Finder

@somesoni2: if the format time is
EndTime=strftime(_time,"%m/%d/%Y %H:%M:%S")and
StartTime=strftime(_time,"%m/%d/%Y %H:%M:%S")

why eval diff=Endtime-StartTime doesn't give any result?

0 Karma

Revered Legend

The field _time is already in epoch format (its a special field which holds value in epoch but shows in human-readable format in visualizations like table/chart). So for calculating diff, just keep EndTime=_time and StartTime=_time and then do eval diff.

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

Builder

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

0 Karma

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

Revered Legend

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

0 Karma

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

Revered Legend

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

0 Karma

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

current_time=January 10, 2017 14:05
latest_time=2017-01-10T19:01:41.2649252Z

How to find the difference for this @someshsoni2?

0 Karma

Revered Legend

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!