topic Re: Replace Null values in xyseries chart in Splunk Search

Replace Null values in xyseries chart

essklau — Tue, 22 Jul 2014 15:43:13 GMT

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!

Re: Replace Null values in xyseries chart

somesoni2 — Tue, 22 Jul 2014 15:59:09 GMT

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

Re: Replace Null values in xyseries chart

essklau — Tue, 22 Jul 2014 17:00:43 GMT

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

Re: Replace Null values in xyseries chart

ppablo — Tue, 29 Jul 2014 23:14:37 GMT

Hi @essklau

Did the fillnull command solve your issue?

Re: Replace Null values in xyseries chart

essklau — Wed, 13 Aug 2014 18:35:04 GMT

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"

Re: Replace Null values in xyseries chart

essklau — Wed, 13 Aug 2014 18:36:36 GMT

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

Re: Replace Null values in xyseries chart

chimell — Tue, 16 Dec 2014 11:24:30 GMT

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

Re: Replace Null values in xyseries chart

Runals — Tue, 16 Dec 2014 12:29:28 GMT

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.

Re: Replace Null values in xyseries chart

MuS — Tue, 16 Dec 2014 12:32:31 GMT

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

Re: Replace Null values in xyseries chart

ryhluc01 — Thu, 21 Feb 2019 19:08:09 GMT

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