Splunk Search

Finding the diff of two time stamps is returning null/ empty value

subhrangshu
Explorer

Hello,

I am trying to find two events from my log with time into consideration, as earliest and latest. Next I am trying to find the total time by doing a diff between latest and earliest, but I am getting no data back.

Here is how my query looks like:

index=test sourcetype="test:node"  "enter" OR "exit"
| stats earliest(_time) AS Earliest, latest(_time) AS Latest
| eval diff=Latest-Earliest
| eval FirstEvent=strftime(Earliest,"%m/%d/%y %H:%M") | eval LastEvent=strftime(Latest,"%m/%d/%y %H:%M")
| eval DiffEvent=strftime(diff,"%m/%d/%y %H:%M")
| eval temp = tostring(round(strptime(Latest,"%m/%d/%y %H:%M") -strptime(Earliest,"%m/%d/%y %H:%M"),0),"duration")
| eval NetTotalTime=replace(temp,"(\d*)\+*(\d+):(\d+):(\d+)","\1 days \2 hours \3 minutes \4 secs")
| table FirstEvent, LastEvent, NetTotalTime, diff, Earliest,DiffEvent

And here is what I get in my result:

FirstEvent | LastEvent|  NetTotalTime|  diff|  Earliest|  DiffEvent

07/07/20 04:5608/11/20 08:01 3035102.8751594078003.85302/05/70 08:35

 

FYI: I am only interested in FirstEvent, LastEvent, NetTotalTime (this is coming blank). The last 3 columns (diff, Earliest,DiffEvent) are just to show you how the data looks like.

Hope to get an answer soon.

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Keep in mind that the _time field is an integer, despite the appearance of it as a string in most output.  That means using strptime on _time is meaningless as the field is already "parsed".

Try this search

index=test sourcetype="test:node"  "enter" OR "exit"
| stats earliest(_time) AS Earliest, latest(_time) AS Latest
| eval diff=Latest-Earliest
| eval FirstEvent=strftime(Earliest,"%m/%d/%y %H:%M") | eval LastEvent=strftime(Latest,"%m/%d/%y %H:%M")
| eval DiffEvent=strftime(diff,"%m/%d/%y %H:%M")
| eval temp = tostring(diff,"duration")
| eval NetTotalTime=replace(temp,"(\d*)\+*(\d+):(\d+):(\d+)","\1 days \2 hours \3 minutes \4 secs")
| table FirstEvent, LastEvent, NetTotalTime, diff, Earliest,DiffEvent
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Keep in mind that the _time field is an integer, despite the appearance of it as a string in most output.  That means using strptime on _time is meaningless as the field is already "parsed".

Try this search

index=test sourcetype="test:node"  "enter" OR "exit"
| stats earliest(_time) AS Earliest, latest(_time) AS Latest
| eval diff=Latest-Earliest
| eval FirstEvent=strftime(Earliest,"%m/%d/%y %H:%M") | eval LastEvent=strftime(Latest,"%m/%d/%y %H:%M")
| eval DiffEvent=strftime(diff,"%m/%d/%y %H:%M")
| eval temp = tostring(diff,"duration")
| eval NetTotalTime=replace(temp,"(\d*)\+*(\d+):(\d+):(\d+)","\1 days \2 hours \3 minutes \4 secs")
| table FirstEvent, LastEvent, NetTotalTime, diff, Earliest,DiffEvent
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...