Splunk Search

Replace Null values in xyseries chart

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?


Tags (3)


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


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


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


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

Revered Legend

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

Path Finder

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

0 Karma

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


Hi @essklau

Did the fillnull command solve your issue?

0 Karma

Path Finder

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

Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...