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!
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.
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
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.
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.
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...