Include zero count in stats count

[johandk]

I have a search like this:

sourcetype="wineventlog:security" (host="Server1" OR host="server2" OR host="server3") | stats count(host) by host

This returns server1 and server2 and their counts but not server3 cause there is no events for it. How do I force it for server3 to display in the table with a zero count??

[samkidman]

Heres a way to do it if you have a large number of hosts that match a regex without using a lookup file:

|stats count | eval [|metadata type=hosts |regex host="<matching hosts>"| fields + host | mvcombine delim="," host | nomv host | format "","","","","",""] | makemv delim="," host | mvexpand host | append [search sourcetype="wineventlog:security" | regex host="<matching hosts>" ] | stats sum(eval(if(isnull(_time),0,1))) as count by host

[bwooden · Accepted solution]

This search would show a count of those servers:

|stats count | eval host="Server1,Server2,Server3" | makemv delim="," host | mvexpand host | append [search sourcetype="wineventlog:security" (host="Server1" OR host="Server2" OR host="Server3")] | stats sum(eval(if(isnull(_time),0,1))) as count by host 

If there are many servers, it may be easier to maintain the list in a lookup file.

Alternatively, if you want to show counts of all servers Splunk has seen you can lead with a metadata command and obviate the need to specify servers.