Any way to return zero result count stats of a field such as the host or sourcteype field?

[robjordan_boa]

It's quite easy to report in splunk on what type of events you have but how to report on what types of events you don't have?

What I know how to do: Summarize results by host and soucetype where there are 1 or more events.
What I don't know how to do: Summarize results by host and sourcetype where there are 0 events.

Example search which works for event matches but doesn't include zero matches:

host=hosta OR host=hostb OR host=hostc|stats count(host) by sourcetype host

Is there a way I can return a zero count result for a host or sourcetype in splunk which doesn't have events?

sourctype host     count(host) 
access    hosta    500
access    hostb    250
access    hostc    0

NOTE: I will be running for over 1,000 hosts. Some of these hosts have never sent data to splunk.

This is quite important when you want to audit an environment to ensure you are collecting events.

Thanks,

Rob

[gkanapathy]

stats won't count by field values that don't exist. But you could force a value to exist, then remove it from the count:

host=A OR host=B OR host=C 
| append [ stats count as host 
           | eval host=split("A;B;C",";") 
           | mvexpand host ] 
| stats count by sourcetype, host
| eval count=count-1

[gkanapathy]

If you have a lookup table (say, it's called hosttable), then instead of the subsearch I used, you can just use:

index=myindex [ inputlookup hosttable | fields host ]
| append [ inputlookup hosttable | fields host ]
| stats count by sourcetype,host
| eval count=count-1

[Najmeddine]

Thanks, works perfectly!

[Najmeddine]

Update:
you can replace
"index=myindex [ inputlookup hosttable | fields host ] | append [ inputlookup hosttable | fields host ]"
with "index=myindex | inputlookup append=t hosttable"
same results, better performance.

[robjordan_boa]

Thanks for your post, Gerald! Initally I will test with about 10 hosts, however I will be running this with a list of 1,000 or more hosts. I do have the hosts already in a primary key column of of a lookup table where the header of that column is SERVERNAME. It's not an issue to pass the list of hosts as a search, however I'm not sure how I will do the split command I see above with so many hosts.

Thanks,

Rob

[reed_kelly]

How about this:
Create a list of hosts that you are interested in and make it a lookup file. Read the lookup file with inputlookup. Use eval to set a count variable to 0. Append the output of a stats command that generates your list. Use stats on the appended results to get the sum of count.

e.g.

|inputlookup myhosts |eval count=0 |append [search index=foo [inputlookup myhosts] | stats count by host] |stats sum(count) by host

If you leave out the inner [inputlookup myhosts], then you can get your initial list plus any new hosts that pop up.

[robjordan_boa]

Thanks again, I will test out. Once working, this will a great solution to the "what's missing?" question that I frequently get asked 🙂

[reed_kelly]

Since you want to include 0 for sourcetype / host pairs, you could you a hybrid of my answer and Gerald's by adding:

|inputlookup myhosts |rename SERVERNAME as "host"|eval sourcetype=split("access;error;...",";")|mvexpand sourcetype| eval count=0| append [search ... [inputlookup myhosts]|rename SERVERNAME as "host" | stats count by host, sourcetype] |stats sum(count) by host, sourcetype

[reed_kelly]

Just for clarification, myhosts (./lookup/myhosts.csv) would have one field called host.

[robjordan_boa]

Thanks for your post, Reed! Will test out and let you know.

Rob