topic Query on stats value = 0 or null in Splunk Search

Query on stats value = 0 or null

christay — Tue, 23 Jul 2019 09:46:55 GMT

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!

Re: Query on stats value = 0 or null

HiroshiSatoh — Tue, 23 Jul 2019 15:14:30 GMT

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.

Re: Query on stats value = 0 or null

christay — Wed, 24 Jul 2019 03:22:09 GMT

Hi Hiroshi,

Thanks for the query, it works as intended.

Cheers.