Splunk Search

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

vrmandadi
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

somesoni2
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

somesoni2
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

nazanin2016
Path Finder

Thanks alot

0 Karma

ppablo
Retired

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

nazanin2016
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

somesoni2
SplunkTrust
SplunkTrust

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

nazanin2016
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

somesoni2
SplunkTrust
SplunkTrust

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.

vrmandadi
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

vrmandadi
Builder

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

0 Karma

DalJeanis
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

vrmandadi
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

vrmandadi
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

somesoni2
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...