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, an upvote 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, an upvote would be appreciated.

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.