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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...