topic Re: Show a result even if no events match in Splunk Search

Show a result even if no events match

DaleFRice — Thu, 01 Aug 2013 17:00:15 GMT

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?

Re: Show a result even if no events match

sideview — Thu, 01 Aug 2013 18:52:53 GMT

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:

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.

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

Re: Show a result even if no events match

dwaddle — Thu, 01 Aug 2013 19:10:09 GMT

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.

Re: Show a result even if no events match

0waste_splunk — Thu, 01 Aug 2013 20:57:03 GMT

thank you.

Re: Show a result even if no events match

Thomas_Aneiro — Fri, 16 Sep 2016 19:38:42 GMT

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

Re: Show a result even if no events match

dwaddle — Mon, 19 Sep 2016 05:03:50 GMT

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?