How to Display Only Count of 0

[vwilson3]

Greetings,

I've been trying to tweak an inherited report to only show the results where the count of events is blank (or zero). Here is a sanitized version of the search string for the report. It takes an input file with our host names and indexes we should see events from for the different apps and OS' and displays the counts and indexes received events into for the last xx hours. That part works well. Now, I need to show only the results where the event count is blank or zero. Any suggestions? Thanks in advance.

[vwilson3]

Thank you, Woodcock, for your answer. I tried your suggestion and it works. It shows only those systems that have no events. I noticed that the output does not show the Target data (which comes from the input file), listing the indexes for each host in which we expect to see events.

[woodcock]

The reason that it is auto-finalizing is that it is a very inefficient search. Try this optimized version:

|tstats count WHERE (index!="_internal" AND index!="_audit") BY host index 
| eval host=upper(if(match(host, "^[\d\.]+$"), host, replace(host, "\..*$", "")))
| search [| inputlookup myinput.csv | fields host] 
| stats list(index) AS index values(count) AS count BY host 
| fields host, index, count 
| append [| inputlookup lmyinput.csv | fields host] 
| dedup host 
| lookup myinput.csv host 
| fields host, sys_purpose, opsys, index, count, note app* os*
| eval Target=mvdedup(mvappend(app1, app2, app3, app4, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, os1, os2)) 
| table host, sys_purpose, opsys, Target, index, count, note 
| rename COMMENT AS "Values for 'host' from the file but not in the data have no value (null()) for 'count'"
| where isnull(count) 
| rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count AS "Event Count:", index AS "Received Events in Index:", note AS"Note:"

[vwilson3]

I finally figured it out... made a few changes:

index!=_internal index!=_audit | stats count by host,index | rex field=host "^(?\w+)\.?" | eval host=upper(host) | search [|inputlookup myinput.csv | fields host] | stats list(index) as index values(count) as count by host | fields host, index, count | append [|inputlookup lmyinput.csv | fields host] | dedup host | lookup myinput.csv host | fields host, sys_purpose, opsys, index, count, note | lookup myinput.csv host | eval Target=mvdedup(mvappend(app1, app2, app3, app4, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, os1, os2)) | table host, sys_purpose, opsys, Target, index, count, note | where isnull(count) | rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count as "Event Count:", index as "Received Events in Index:", note as "Note:"

[vwilson3]

This works for a short period of time, say the last 10 minutes, but not for the last 24 hours. The search runs for a while then auto-cancels. Alas, back to the drawing board. Anyone have any suggestions?

[vwilson3]

Sorry, hit the post button before adding the search...

| tstats count where index!=_internal AND index!=_audit by host,index | rex field=host "^(?\w+)\.?" | eval host=upper(host) | search [|inputlookup myinput.csv | fields host] | stats list(index) as index values(count) as count by host | fields host, index, count | append [|inputlookup myinput.csv | fields host] | dedup host | lookup myinput.csv host | fields host, sys_purpose, opsys, index, count, note | lookup myinput.csv host | fields - fqdn | eval Target=mvsort(mvdedup(mvappend(app1, app2, app3, app3, app5, app6, app7, app8, app9, app10, app11, app12, app13, app14, app15, os1, os2))) | fields host, sys_purpose, opsys, Target, index, count, note | rename host AS "Host Name:", sys_purpose AS "System Purpose:", opsys AS "Operating System:", Target AS "Expected Index:", count as "Event Count:", index as "Received Events in Index:", note as "Note:"