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?

Thanks!

Tags (3)

Communicator

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

0 Karma

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

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

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

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

Community Manager
Community Manager

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.

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!