Replacing Null values

[samsplunkd]

Hi,

My search looks like below:
index=foo search_name="bar" |stats sum(Count) AS Total

Sometimes Total doesn't have any value and is NULL. Is there a way this NULL can be replaced with 0?

I tried below two but none worked.
a) case(isnull(Total),0)
b) coalesce(Total,0)

Any help is greatly appreciated.

Thanks

[MartinHarper]

When I try your search, on an index with no Count fields, I don't get one result with a null. Instead I get no results. Whereas, you instead want to get one result with a zero.

• Even if none of the results has the Count field.
• Even if there are no results for the search.

I think this will do what you want:

search_name=not_found | append [ search * | head 1 | eval Count=0 ] | stats sum(Count) AS Total

This will always give you a total count unless there are no rows that match your selected time frame. It's a bit awkward, though.

[MartinHarper]

Edited, try now.
How are you consuming this search? Wondering if a better approach would be to change how it is consumed.

[samsplunkd]

Yeah the problem is it shows no results when there are no matching events. I want to show "0" in Total in that case.
Above doesn't work as field Count won't even exist if there are no matched events.

Basically I want to say if Total contains nothing, just display 0.

[Ayn]

This is exactly what the fillnull command is for.

... | fillnull Total

[jawaharas]

Correct syntax for more clarity:

... | fillnull value=NULL

Reference:
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/fillnull

[samsplunkd]

Reading through the documentation:
"Null values are those missing in a particular result, but present for some other result."

In my case there is only one value instead of multiple events with some having values and others NULL. How do we replace NULL with 0 in case of only one value?

[samsplunkd]

Thanks for your reply but when I try to use, it still doesn't show any results... Don't I expect to see "0" incase there are no results in "Total" after using this fillnull function?

[gfuente]

Hello

You can try with usenull=f

In your example: index=foo search_name="bar" |stats sum(Count) AS Total usenull=f

Regards

[gfuente]

Ok, thanks for the correction

[Ayn]

usenull isn't valid for stats, just for chart.