topic Re: Fillnull not working on my search in Splunk Search

Fillnull not working on my search

jevenson — Wed, 06 Feb 2013 18:13:32 GMT

I've got a search that looks something like this:

search | eval Minutes=case(field<120,"0 to 2", field>=120 AND field<180, "2 to 3 mins", field>=180 AND field<240, "3 to 4 mins") | chart count as Sent by Minutes.

The problem is that the Send field does not always have values, and I am trying to fill those values with 0. Right now they just don't return anything, so if "2 to 3 mins" has a count of 0 it just doesn't show up in the results, like this:

.....Minutes .....Sent

1. 0 to 2 mins...503

2. 3 to 4 mins... 20

What I'd like to see is this:

.....Minutes ...... Sent

1. 0 to 2 mins ....503

2. 2 to 3 mins .... 0

3. 3 to 4 mins ....20

I've tried adding fillnull, but it doesn't work.

Re: Fillnull not working on my search

sideview — Thu, 07 Feb 2013 04:22:39 GMT

Fillnull cant do it because it can only fill null fields, not create whole rows.

Here's a way though. We can make dummy rows and add them using an append.

search | eval Minutes=case(field<120,"0 to 2", field>=120 AND field<180, "2 to 3 mins", field>=180 AND field<240, "3 to 4 mins") | stats count as Sent by Minutes | append [| stats count | eval Minutes=split("0 to 2,2 to 3 mins,3 to 4 mins",",") | mvexpand Minutes | eval Sent=0] | stats sum(Sent) as Sent by Minutes

Here's a breakdown about what the heck I'm doing inside that append::

| stats count, all by itself, will create a single row with a field called 'count'

fields - count will take away our count field. Leaving a totally empty but useful row. (hey presto!)

the next eval will create a multivalued field called "Minutes" that has each of our dummy values.

The mvexpand turns the set from one row with a multivalued "Minutes" field, into N rows with single-valued "Minutes" field.

Then to make the exercise easier to follow, we give them a "Sent" field that happens to be "0". Technically this is unnecessary as the nulls would work just as well as explicit 0's.

Picture that set of events being appended onto the set before the append command. Then the last stats clause will count them, but the zeros will make the dummy rows not affect our totals.

Note that the "0 to 2 mins" and all those have to match character for character. There were some inconsistencies in your question that I kept in there, but keep a close eye or else the last stats command wont line it all up correctly.

Re: Fillnull not working on my search

jevenson — Thu, 21 Mar 2013 16:04:44 GMT

After upgrading my search head to 5.0.2 this search no longer works. Was there a change in the way this is handled? The search still runs but now all the rows have a value of 0. Previously only the rows without values had 0's.

Re: Fillnull not working on my search

tiny3001 — Mon, 15 Apr 2013 05:57:32 GMT

'append' does seem broken with Splunk 5.0.2. See my question as well:
http://splunk-base.splunk.com/answers/76493/specific-search-not-working-after-upgrade-to-splunk-50