Splunk Search

Replace Null values in xyseries chart

essklau
Path Finder

Hello, Splunkers

I have a search of index=sql | bucket span=1h _time | stats count by _time source | xyseries _time,source,count which is nice because I can use a where clause to act on values of count except if the value is zero/null. There are plenty of "cells" containing no value at all that I would like to replace with "0", or if I could even refer to it directly, do so.

However, I've failed with "if", "isnull", and "where count NOT >= 1" so far, and I can't figure out how to change the value of a zero/null "cell" much less how to change its value.

Any takers?

Thanks!

Tags (3)

ryhluc01
Communicator

index=sql | bucket span=1h _time | stats count by _time source | xyseries _time,source,count|fillnull

MuS
SplunkTrust
SplunkTrust

Hi essklau,

take a look at this http://answers.splunk.com/answers/176466/how-to-use-eval-if-there-is-no-result-from-the-bas-1.html to get an idea how to do such a thing if your base search returns no events.

cheers, MuS

0 Karma

Runals
Motivator

Your initial post seems to indicate you are doing a ... | stats | where | xyseries but your query doesn't read like that. The fillnull is an option but would be done as ... | stats | fillnull | xyseries. I've found that once the data is in a chart/table view (chart or timechart) you aren't able to use a where on the field count (or whatever function used) as it doesn't exist anymore once you've moved into that display mode (highly likely a more technical term for that =).

At any rate if you are using 6.x+ you could do the following. There are some nuances with fields that don't have alphanumeric names which I assume is there given you are using source. That is why you would use the single quotes around though.

index=sql | bucket span=1h _time | chart count over _time by source | foreach * [eval <<FIELD>> = if(isnull('<<FIELD>>'), 0, '<<FIELD>>')]

You don't really have to do the chart bit but wanted to show that as a different way to do the stats and then xyseries. Of course the benefit of going that route is you CAN insert a where or sort between the two unlike chart. I always get my xy mixed up when doing chart over by so might have to reverse those if you give it a try.

Incidentally it just hit me that I wonder if once you are in that 'chart' view you could do a | multikv. Probably not though.

0 Karma

chimell
Motivator

Hi essklau

try this code source it will be done

 index=sql| bucket span=1h _time | stats count by _time source | xyseries _time,source,count |fillnull value=0

somesoni2
SplunkTrust
SplunkTrust

You can use fillnull command to replace NULL values with 0.

Also, Try using timechart instead of bucket..stats...xyseries combination.

index=sql | timechart span=1h count by source | fillnull value=0

essklau
Path Finder

It's not a data type issue either, meaning that "0" isn't being read as a string unexpectedly.

0 Karma

essklau
Path Finder

No, it doesn't address the issue that I can't refer to k/v pairs where v=0, whether it's filled by fillnull or not. The crux of my problem isn't a good way to make a value=0, it that I can't use further logic to say "when value=0, do X"

0 Karma

ppablo
Retired

Hi @essklau

Did the fillnull command solve your issue?

0 Karma

essklau
Path Finder

Timechart doesn't let me use where clause, but I'll take (try) that fillnull. 🙂 THx.

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 ...