topic Re: Replacing Null values in Splunk Search

Replacing Null values

samsplunkd — Tue, 15 Jan 2013 10:10:21 GMT

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

Re: Replacing Null values

gfuente — Tue, 15 Jan 2013 10:33:52 GMT

Hello

You can try with usenull=f

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

Regards

Re: Replacing Null values

Ayn — Tue, 15 Jan 2013 10:52:09 GMT

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

Re: Replacing Null values

Ayn — Tue, 15 Jan 2013 10:53:05 GMT

This is exactly what the fillnull command is for.

... | fillnull Total

Re: Replacing Null values

gfuente — Tue, 15 Jan 2013 11:06:35 GMT

Ok, thanks for the correction

Re: Replacing Null values

samsplunkd — Tue, 15 Jan 2013 18:09:48 GMT

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?

Re: Replacing Null values

samsplunkd — Tue, 15 Jan 2013 19:08:23 GMT

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?

Re: Replacing Null values

MartinHarper — Tue, 15 Jan 2013 19:29:15 GMT

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.

Re: Replacing Null values

samsplunkd — Tue, 15 Jan 2013 19:35:28 GMT

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.

Re: Replacing Null values

MartinHarper — Tue, 15 Jan 2013 19:47:46 GMT

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

Re: Replacing Null values

jawaharas — Thu, 02 Aug 2018 00:29:44 GMT

Correct syntax for more clarity:

... | fillnull value=NULL

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