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!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...