Splunk Search

How to remove values of 0 from my search results?

Contributor

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?

Tags (3)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

SplunkTrust
SplunkTrust

what is the query/transforming command to calculate duration?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Contributor
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)
0 Karma

SplunkTrust
SplunkTrust

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.

View solution in original post

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Contributor

thanks very helpful

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!