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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...