Splunk Search

How do you exclude null values from streamstats command?

acemel
New Member

The search below results in a table with 16 columns (along with column for date/time). The first eight columns show the efficiency values for positions 1 through 8. The second set of 8 columns provide the moving average of the last 100 efficiency values for each position. I do not want to include the value or increment the count (1-100) in the moving average when the value is null. I therefore expect the moving average to remain the same when one or more null values are encountered. However, although the values do remain the same some of the time, at other times they are slightly higher or lower. This seems to indicate that in some cases the (window) count is incremented when a null value is encountered. It's not obvious why this is happening. Any suggestions on how to correct this?

index="8s_perf_mon" Machine= 5002 Position!=0 
| timechart span=12h values(eval(if(Efficiency==0, null(),Efficiency))) by Position 
| streamstats window=100 AVG 

Thank you for any help you can provide.

0 Karma

somesoni2
Revered Legend

Give this a try.

index="8s_perf_mon" Machine= 5002 Position!=0 
| timechart span=12h values(eval(if(Efficiency==0, null(),Efficiency))) by Position 
| streamstats window=100 sum count(*) as "(*"
| foreach "(*" [eval "avg<<FIELD>>)"='sum<<FIELD>>)'/'<<FIELD>>']
| fields - (* sum*

Calculating count and sum separately in streamstats, with count being shown only for not null values. Foreach to calculate average by diving sum by count of non-nulls.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...