Splunk Search

how to fill the missing values to nearest previous non-zero value in streamstat result

kalitbri
Explorer

Hello,

I am using streamstats to produce hourly category accumulate total to date by :

... | bucket _time span=1hour | stats sum(value) as total by _time,category | streamstats global=f current=t sum(total) as accu_total by category

However I found that if there's missing values for the hour for some categories, the value will be empty for the respective slot in the streamstat result, causing the chart to produce unwanted result, because the missing value is treated as 0.

Though I can config the chart to connect missing value, the result is still not correct enough . I think it should be correct to treat the missing value to the same as nearest previous non-zero value.

Can anyone help me to solve this? Thanks!

1 Solution

sideview
SplunkTrust
SplunkTrust

Sounds like there's a case that maybe this is a bug in streamstats but I'll leave that to steveyz.

As a way forward i suspect that the fillnull command can work around it. fillnull can be used to put in explicit values (like 0) when a field is undefined.

It looks a little bizarre because your field is literally called 'value', but here you go:

... | bucket _time span=1hour | fillnull value=0 value | stats sum(value) as total by _time,category | streamstats global=f current=t sum(total) as accu_total by category

http://www.splunk.com/base/Documentation/4.1.3/SearchReference/Fillnull

NOTE: If you use bucket directly it will end up discarding time buckets that have NO values of any kind. In that case, I could be wrong but I think that you should just use timechart instead of using bucket and stats. Timechart just does the two steps at once, and it will also fill nulls for you.

... | timechart span=1hour sum(value) as total by category | streamstats global=f current=t sum(total) as accu_total by category

Hopefully this helps.

View solution in original post

landen99
Motivator

The much more elegant solution uses the SPL command filldown:

... | bucket _time span=1hour | filldown value | stats sum(value) as total by _time,category | streamstats global=f current=t sum(total) as accu_total by category

sideview
SplunkTrust
SplunkTrust

Sounds like there's a case that maybe this is a bug in streamstats but I'll leave that to steveyz.

As a way forward i suspect that the fillnull command can work around it. fillnull can be used to put in explicit values (like 0) when a field is undefined.

It looks a little bizarre because your field is literally called 'value', but here you go:

... | bucket _time span=1hour | fillnull value=0 value | stats sum(value) as total by _time,category | streamstats global=f current=t sum(total) as accu_total by category

http://www.splunk.com/base/Documentation/4.1.3/SearchReference/Fillnull

NOTE: If you use bucket directly it will end up discarding time buckets that have NO values of any kind. In that case, I could be wrong but I think that you should just use timechart instead of using bucket and stats. Timechart just does the two steps at once, and it will also fill nulls for you.

... | timechart span=1hour sum(value) as total by category | streamstats global=f current=t sum(total) as accu_total by category

Hopefully this helps.

sideview
SplunkTrust
SplunkTrust

Oh I see. Well then you dont want to use bucket and stats manually, but just use timechart. unless im missing something. I'll add some description to my answer.

0 Karma

kalitbri
Explorer

Hello, I am sorry but I can't make it from your suggestion. Because the fillnull command seems only fill the nulls by values. However seems that it can't add new value-0 rows for certain time bucket and certain category which value is missing...

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