Splunk Search

Stats avg() to include zero values

malcolmtkelly
Explorer

Hi I'm trying to calculate the average number of requests for a user, lets say user1 across the space of a week. Lets say the data is

Sun user1 0
Mon user 5
Tue user1 5
Wed user1 0
Thu user1 10
Fri user1 0
Sat user1 0

If I do:

... | stats avg(Requests) by user

I get 6.66, which is correct if I'm only counting three values. But I want to include values where the number of requests is zero. So the real sum should be (0+5+5+0+10+0+0)/7 = 2.85.

How do I get stats to include the zeros?

Tags (2)
0 Karma

jonuwz
Influencer

That probably doesn't represent your data. stats avg works perfectly fine on what you posted.

Are you sure that the real data doesn't contain a NULL value for the numerical field ?

Example with NULL data:

* | head 2 | eval user="john" | streamstats count as count| eval num=if(count==2,20,NULL) | table user num | eventstats avg(num) as avg by user

With non-null-data :

* | head 2 | eval user="john" | streamstats count as count| eval num=if(count==2,20,0) | table user num | eventstats avg(num) as avg by user

You want to look at fillnull

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...