topic How to write a search to display a table of counts, including fields with 0 results? in Splunk Search

How to write a search to display a table of counts, including fields with 0 results?

ceng — Tue, 23 Feb 2016 23:45:33 GMT

Hi,

How can I create this kind of table?

MissingA : 0
MissingB : 100
MissingC : 200

I'd like to create a dashboard that has rows for MissingA, MissingB and MissingC. These row values are static, meaning that I search my logs for this data and if it's available, I can do a count. I've tried some evals/stats, but I can't get it to display the MissingA (because the count is 0, this row disappears instead of displaying as 0). Do you know how I could create this table?

Here are some sample records:
type=MissingC msg="Data is missing for type1"
type=MissingC msg="Data is missing for type1"
type=MissingD msg="Data is missing for type500"
type=MissingD msg="Data is missing for type500"

Thanks

Re: How to write a search to display a table of counts, including fields with 0 results?

ceng — Wed, 24 Feb 2016 00:30:07 GMT

I've found a similar answer that can do it --

https://answers.splunk.com/answers/229049/display-a-result-when-the-count-0.html

Re: How to write a search to display a table of counts, including fields with 0 results?

sideview — Wed, 24 Feb 2016 00:55:07 GMT

One way is to let stats count by type build the potentially incomplete set, then append a set of "dummy" rows on the end where each row has a count of "0", then do a stats sum(count) as count at the end.

<<your search terms here>>  
| stats count by type 
| append [| stats count | fields - count | eval type=split("MissingA,MissingB,MissingC,MissingD",",") | mvexpand type | eval count=0]
| stats sum(count) as count by type

This will give a table that looks like this, and that includes "0" rows even when the given type is missing entirely from the raw data.

------------------------------------
|    type         |    count       |
|   MissingA      |    12          |
|   MissingB      |     0          |
|   MissingC      |    3           |
|   MissingD      |    0           |

There's another way that you might find easier, or that you might find interesting. That's to create a "chart style" set of output rows first, use a simple fillnull command to add your "0" entries if they're missing (instead of that append command), then use untable to turn the "chart style" output into "stats style" rows like the above.

<<your search terms here>>  
| eval foo="1"
| chart count over foo by type 
| fillnull MissingA MissingB MissingC MissingD value="0"
| fields - NULL
| untable foo type count 
| fields - foo

The benefit of this approach is that there's no append. If you have moral/ethical/religious objections to append (not unreasonable), then this approach may be preferred!
Admittedly the little "foo" trick is clunky and funny looking. untable and xyseries don't have a way of working with only 2 fields so as a workaround you have to give it a dummy third field and then take it away at the end.

Re: How to write a search to display a table of counts, including fields with 0 results?

chimell — Wed, 24 Feb 2016 10:17:57 GMT

Hi
I saved the following record in missing.txt file and indexed it in my splunk 6.3.2 instance

type=MissingC msg="Data is missing for type1"
type=MissingC msg="Data is missing for type1"
type=MissingD msg="Data is missing for type500"
type=MissingD msg="Data is missing for type500"

Then i perfomed the following search

source="missing.txt" sourcetype="missing"|rex max_match=0 field=_raw "type\=(?<Missing_field>[^\s+]+)"|mvexpand Missing_field|stats count(_raw) as count by Missing_field

look at the result

Re: How to write a search to display a table of counts, including fields with 0 results?

ceng — Fri, 26 Feb 2016 17:55:34 GMT

This worked, thanks!