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.
Heh...which is exactly why I put all four there. Sometimes
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
coalesce(a,b,c) is the equivalent of
...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
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.
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)