topic Re: How to remove values of 0 from my search results? in Splunk Search

How to remove values of 0 from my search results?

packet_hunter — Thu, 09 Mar 2017 19:02:00 GMT

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?

Re: How to remove values of 0 from my search results?

DalJeanis — Thu, 09 Mar 2017 19:26:38 GMT

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.

Re: How to remove values of 0 from my search results?

packet_hunter — Thu, 09 Mar 2017 19:37:03 GMT

actually in this case the "where" syntax did not work... but search did, just fyi

Re: How to remove values of 0 from my search results?

niketn — Thu, 09 Mar 2017 19:37:42 GMT

what is the query/transforming command to calculate duration?

Re: How to remove values of 0 from my search results?

packet_hunter — Thu, 09 Mar 2017 19:57:33 GMT

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)

Re: How to remove values of 0 from my search results?

DalJeanis — Thu, 09 Mar 2017 20:18:31 GMT

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.

Re: How to remove values of 0 from my search results?

packet_hunter — Thu, 09 Mar 2017 20:23:17 GMT

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

Re: How to remove values of 0 from my search results?

DalJeanis — Thu, 09 Mar 2017 20:45:41 GMT

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.

Re: How to remove values of 0 from my search results?

packet_hunter — Thu, 09 Mar 2017 20:49:13 GMT

thanks very helpful