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