Query on stats value = 0 or null

[christay]

Hi Guys,

I have a question here.
Example i have a query statement that check for event logs captured by all my servers(Say total i have 30) during the last 10 mins.

if i run the following query :
index=ind_audit_log (sourcetype=linux_secure OR sourcetype=linux_audit) | stats count by host

It only returns me 25 servers which has event logs being pipe to splunk. The rest of the servers(5 in total) that does not have event logs pipe to splunk was not being reflected in the above query.

How can i craft and run a query to sieve out those servers that does not have any event logs being pipe to my splunk so that i can create an alert to notify me to check on the servers.

Thanks!

[HiroshiSatoh · Accepted solution]

I think that using LOOKUP FILE is a staple, but there are also such search sentences.

|tstats count  
      WHERE index=ind_audit_log AND (sourcetype=linux_secure OR sourcetype=linux_audit) 
      by host 
| append [| metadata type=hosts index=ind_audit_log |eval count=0|table host,count]
| stats sum(count) as count by host

Unnecessary hosts should be excluded by WHERE clause etc.

[christay]

Hi Hiroshi,

Thanks for the query, it works as intended.

Cheers.