Splunk Search

Replacing Null values

samsplunkd
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

MartinHarper
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

MartinHarper
Path Finder

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

0 Karma

samsplunkd
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

Ayn
Legend

This is exactly what the fillnull command is for.

... | fillnull Total

jawaharas
Motivator

Correct syntax for more clarity:

... | fillnull value=NULL

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

0 Karma

samsplunkd
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

samsplunkd
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

gfuente
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

gfuente
Motivator

Ok, thanks for the correction

0 Karma

Ayn
Legend

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...