I have a search that calculates a time duration for windows events logon and logout.
....| eval duration=tostring(logoff_time-logon_time,"duration")
I get a lot of time values for duration which is 00:00:00 and I would like to drop / remove from the results.
What is the best way to remove those values?
Take your pick...
| where duration!=0
| where duration>0
| search duration!=0
| search duration>0
...but do it before reformatting/calculating, to save the mips.
what is the query/transforming command to calculate duration?
index=wineventlog sourcetype=WinEventLog
Security_ID="some_Name_ID" (EventCode=4624 OR EventCode=4634)
|sort Logon_ID
| stats
latest(eval(if(EventCode=4624,_time, null()))) as logon_time,
latest(eval(if(EventCode=4634,_time,null()))) as logoff_time,
latest(eval(if(EventCode=4624,Source_Network_Address, null()))) as Src_Network_Address,
latest(eval(if(EventCode=4624,Logon_GUID, null()))) as LgnGUID,
by Logon_ID
| eval logoff_time = if(logoff_time<logon_time OR isnull(logoff_time), "Session in Progress",logoff_time)
| eval logon_time = if(isnull(logon_time),"Logon time out of range", logon_time)
| eval duration=tostring(logoff_time-logon_time,"duration")
| eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time)
| eval logoff_time=if(isint(logoff_time),strftime(logoff_time, "%b %d, %I:%M %p"),logoff_time)
| where duration>"00:01:00" OR isnull(duration)
Take your pick...
| where duration!=0
| where duration>0
| search duration!=0
| search duration>0
...but do it before reformatting/calculating, to save the mips.
actually in this case the "where" syntax did not work... but search did, just fyi
Heh...which is exactly why I put all four there. Sometimes where
and search
are a bit finnicky.
You can use coalesce to get rid of the nulls, which simplifies the code slightly -
coalesce(a,b)
is the equivalent of if(isnull(a),b,a)
or if(isnotnull(a),a,b)
.
coalesce(a,b,c)
is the equivalent of if(isnull(a),if(isnull(b),c,b),a)
or if(isnotnull(a),a,if(isnotnull(b),b,c))
...so the coalesce version looks like this...
| eval logon_time = coalesce(logon_time,"Logon time out of range")
...or, with simple null fields like that, you could also use the fillnull
verb. In this case it doesn't save anything, but if you had a list of fields to all default to the same thing, then fillnull
can be much more efficient to code.
| fillnull value="Logon time out of range" logon_time
Also, minor note, sort
has a default number of records that it will return if you don't tell it to return all of them, so get in the habit of putting the number 0 after every sort verb...
|sort 0 Logon_ID _time
However, you don't need to sort
anything before that stats
command anyway.
yes thank you for "search", I was using "where" and got stuck.
I got it to work with ... | where duration > "00:00:00", quotes were needed as it was a string... I believe
I posted the entire search above... probably could be cleaned up
thanks again
Yep, see the rest of my comment on how to clean it up. I tend to edit heavily until my spelling and thinking is all straight.
thanks very helpful