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

[subhrangshu]

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:56

08/11/20 08:01

3035102.875

1594078003.853

02/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.

[richgalloway · Accepted solution]

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