Show count of zero on chart

[AlexMcDuffMille]

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.

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

[grijhwani]

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.

[AlexMcDuffMille]

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!

[the_wolverine · Accepted solution]

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]

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

[AlexMcDuffMille]

Still isn't returning anything

[somesoni2]

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

[AlexMcDuffMille]

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!

[somesoni2]

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

[AlexMcDuffMille]

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.

[somesoni2]

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

[AlexMcDuffMille]

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

[somesoni2]

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

[AlexMcDuffMille]

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

[somesoni2]

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