Splunk Search

Fillnull not working on my search

jevenson
Path Finder

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.

Tags (2)
1 Solution

sideview
SplunkTrust
SplunkTrust

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.

View solution in original post

sideview
SplunkTrust
SplunkTrust

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.

tiny3001
Path Finder

'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

0 Karma

jevenson
Path Finder

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...