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
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
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
Thanks alot
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
Thanks I did it but then how I can compare the result and say for example it is greater that 24hours?
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.
@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?
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.
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
Hello Somesh,I have attached an image which shows the results
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
The name of the field createddate was changed to latest after the stats. Try the updated search.
Well I tried the updated one but it is still the same the difference field is blank and none of fields are converted
My bad, I followed the wrong field for timestamp format. Please try the updated answer now.
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?
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