Splunk Search

How do you exclude null values from streamstats command?

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

SplunkTrust
SplunkTrust

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