Splunk Search

Show count of zero on chart

AlexMcDuffMille
Communicator

Hi Everyone,

I have a search that creates a chart that shows the counts of different errors for each item, but if there are no errors at all for a particular item it does not show "0" but rather just a blank cell.

I have a list of items that all have different errors associated with them. Sometimes the same item can have multiple errors, and the same error can be true on multiple items.

Item "Description"

1 "off"

1 "off"

1 "on"

2 "off"

2 "fail"

4 "fail"

I would like the chart to show the errors on the left side of the page, and the item numbers on the top of the page. However, if there are no errors for an item it does not say there are 0 errors, but rather leaves the column empty.

My current search looks similar to this:

<index,source,sourcetype,etc.> |  chart count(item) over Description limit=0 by item | fields Description 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Please see this image to better understand what I'm talking about. I'd like items numbered "6","7", and "9" to show all zeros.

picture

Here's a link to the picture: http://s30.postimg.org/cj9v49h3l/splunknulltozero.png
Thank you!

0 Karma
1 Solution

the_wolverine
Champion

I use a lookup file to provide a list of the field names:

count.csv
1,2,3,4,5,6,7,etc

|inputlookup count.csv | eval COUNT=0 | join type=outer [ search | chart count(item) over Description limit=0 by item | fields Description 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ] | eval count=if(count>0,count,COUNT)

View solution in original post

grijhwani
Motivator

What happens to your search without the field selection?

As a minimum I would expect count (logically) to return a value of zero. If it was a sum() function I could understand it returning nulls if all the individual field values were null, but a count - by definition - starts at zero. I think you need to debug the underlying table before performing a field selection.

By convention I always put the limit after the ordering selections, but it doesn't seem to make a material difference. I just tried a similar search which returned zeros across the whole table for all instances where there was no count.

0 Karma

AlexMcDuffMille
Communicator

If I remove the "| fields Description 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 " stanza then it completely then it completely omits the columns for the items that have a count of zero. To me it does make sense that Splunk would give a null value instead of zero because it never actually knew those fields existed.

Thanks for your help!

0 Karma

the_wolverine
Champion

I use a lookup file to provide a list of the field names:

count.csv
1,2,3,4,5,6,7,etc

|inputlookup count.csv | eval COUNT=0 | join type=outer [ search | chart count(item) over Description limit=0 by item | fields Description 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ] | eval count=if(count>0,count,COUNT)

somesoni2
Revered Legend

Try this workaround

|stats count | eval item="1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20" | table item | makemv item | mvexpand item | join type=left max=0 item [search <index,source,sourcetype,etc.> | stats count(item) as count by Description, item]| chart first(count) as count over sourcetype limit=0 by item | fillnull
0 Karma

AlexMcDuffMille
Communicator

Still isn't returning anything

0 Karma

somesoni2
Revered Legend

I was missing field renames in stats and chart. I updated it. See if that works..

0 Karma

AlexMcDuffMille
Communicator

I think I get what you're doing here, more or less telling Splunk that all of those fields do indeed exist so the fillnull command will work on them. When I tried the exact search you gave me it came back with no results.

I removed the "| chart first(count) over sourcetype limit=0 by crane" stanza and it appears to be close to working. I see three columns "item" "Description" and "count(item)". The Items that have zero errors do indeed show 0 now (instead of just being blank). It would be great if I could somehow get that first column to be the first row.

Thanks for your help!

0 Karma

somesoni2
Revered Legend

I think I got the issue. When you run the chart command, (without fields command) you must not be getting the results for item=6,7,9. And when you add the fields command with 6,7,9, its the fields that doesn't exists and even fillnull will not work. I am working on a workaround for it. stay tuned

0 Karma

AlexMcDuffMille
Communicator

I got rid of the (item) after count and it did not make a difference.

I tried "| chart count over Description | fields..."
And everything was empty, except for the Description.

0 Karma

somesoni2
Revered Legend

Can you try the chart command as "| chart count over Description..." instead of count(item)?

0 Karma

AlexMcDuffMille
Communicator

Correct, if I add just | fillnull at the end (or anywhere) or | fillnull value=0 it does not change anything.

0 Karma

somesoni2
Revered Legend

Just to be sure, did you try adding "|fillnull" at the end of your search. [note: your picture is not available in the post]

0 Karma

AlexMcDuffMille
Communicator

I tried using the fillnull command but it does not seem to work, no matter where I put it in the search.

0 Karma

somesoni2
Revered Legend

You can use filnull.
your search | fillnull value=0 fieldname

Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...