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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...