Splunk Search
Highlighted

Replacing Null values

Path Finder

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

Tags (3)
0 Karma
Highlighted

Re: Replacing Null values

Motivator

Hello

You can try with usenull=f

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

Regards

0 Karma
Highlighted

Re: Replacing Null values

Legend

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

0 Karma
Highlighted

Re: Replacing Null values

Motivator

Ok, thanks for the correction

0 Karma
Highlighted

Re: Replacing Null values

Legend

This is exactly what the fillnull command is for.

... | fillnull Total
Highlighted

Re: Replacing Null values

Path Finder

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?

0 Karma
Highlighted

Re: Replacing Null values

Path Finder

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?

0 Karma
Highlighted

Re: Replacing Null values

Motivator

Correct syntax for more clarity:

... | fillnull value=NULL

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

0 Karma
Highlighted

Re: Replacing Null values

Path Finder

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.

0 Karma
Highlighted

Re: Replacing Null values

Path Finder

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.

0 Karma