Show a result even if no events match

[DaleFRice]

As part of a larger project, one of the things we want to do is to let the user build tables with one search criteria at a time. So for example, the table might ultimately be defined with a search like so:

source="log.log" foo="foo" OR foo="bar" OR foo="eggs" OR foo="spam" | chart count by foo

That part is already finished. The problem we run into is if, for this example, there are no records where foo="eggs". Splunk will, by default, not show any results for "eggs". Is there a way to get it to show "eggs" anyways with a count of 0 by using the basic modules, or will we have to build our own to add that behavior?

[dwaddle · Accepted solution]

Here's another wacky way, presuming you know all of the possible values of foo in advance. Make a lookup table as follows:

foo,count
foo,0
bar,0
eggs,0
spam,0

And use it like so

source=log.log foo="foo" OR foo="bar" OR foo="eggs" OR foo="spam" 
| stats count by foo 
| inputlookup append=t foolookup.csv
| stats max(count) as count by foo

The values in the lookup make a 'sentinel' of sorts, making sure your results always exist. And the max picks out either your real count or the sentinel. Unless your real count is negative .. but that would be weird.

[sideview]

This is the sort of question that feels like it has a UI answer, but the answers are all going to be search language answers.

here's one wacky way:

source="log.log" foo="foo" OR foo="bar" OR foo="eggs" OR foo="spam" | eval count="1" | append [| stats count | fields - count | eval foo=split("foo,bar,eggs,spam",",") | eval count="0" | mvexpand foo] | chart sum(count) as count by foo

In english, you basically append another really tiny static data set where the counts for the four values are all going to be zero.

Here's another way that I thought might be simpler, but it ended up even weirder looking.

source="log.log" foo="foo" OR foo="bar" OR foo="eggs" OR foo="spam" | eval count="1" | append [| stats count | fields - count | eval foo="no value" | eval count="0" ] | stats sum(count) as count by foo | eval magic="1" | xyseries magic foo count | fillnull foo bar eggs spam value="0" | untable magic foo count | fields - magic

We basically use xyseries to get a set that is "fillnull-able", then untable to switch it back into its original form.

[0waste_splunk]

thank you.

[Thomas_Aneiro]

What about a | fillnull value=0? Would that fit the bill?

[dwaddle]

For the purpose of visualizing the problem, think of splunk results and fields in terms of "rows" and "columns". When you do a search, and do something like | stats max(foo) by bar then you get a new "row" for each value of bar, and a "column" for max(foo). If a particular value of bar does not have any related value of foo, then fillnull is perfectly appropriate. But, you can't fillnull what does not exist.

Drawing an example, suppose you have a brick-and-mortar store and wanted to compute | stats sum(sales) by day_of_month for December. On December 25th the store was closed. The results of our stats cannot possibly have a row for "25". We cannot fillnull to make a result row that does not exist. Does this make sense?