How to count when there are 'no results found'

[enowak]

I have a very simple query and can't believe I can't get this to work...

The os index should have 5 sourcetypes for each host in my environment:
df, linux_audit, linux_messages,linux_secure and vmstat.

I need to know how many events each sourcetype has in the *Nix application. I need it to include a 0 if there are not events.

I've tried:
index=os sourcetype=* | fillnull | stats count by host, sourcetype
index=os sourcetype=* | fillnull value=0 count, host, sourcetype | stats count by host, sourcetype

I need to when there are 0 events for the sourcetype and host. But I'm never getting a 0

[somesoni2 · Accepted solution]

You get no result with count 0 as stats only works on data which is available in selected time range (if no event for a host-sourcetype combination,m no row for it). You'd need to explicitly provide the list of sourcetypes that may exist. The workaround would be like this.

| tstats count WHHERE index=os sourcetype=* by host sourcetype
| appendpipe [| tstats count WHERE index=os earliest="<<some large timerange where each host will have atleast one event>>  by host 
| eval count=0 | eval sourcetype="df linux_audit linux_messages linux_secure vmstat" | makemv sourcetype mvexpand sourcetype]
| stats max(count) as count by host sourcetype

Since your base search is using metadata fields (host index source sourcetype) and you're only interested in event count, the tsats command will give you results much faster. The appendpipe subsearch is generating host-sourcetype combination with count 0 and being added to current result. You'd need to update it's timerange (earliest value) to something large value where every host will have some event in index=os, else that host will not be listed. The final stats will merge original result with results from appenedpipe.

[enowak]

PERFECT -- This is working exactly as I want.. I did have to make two changes:

WHHERE -> WHERE
makemv sourcetype mvexpand sourcetype -> makemv sourcetype | mvexpand sourcetype

| tstats count WHERE index=os sourcetype=* by host sourcetype
| appendpipe [| tstats count WHERE index=os by host
| eval count=0 | eval sourcetype="df linux_audit linux_messages linux_secure vmstat" | makemv sourcetype | mvexpand sourcetype]
| stats max(count) as count by host sourcetype