topic Re: Stats avg() to include zero values in Splunk Search

Stats avg() to include zero values

malcolmtkelly — Tue, 03 Sep 2013 10:28:18 GMT

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?

Re: Stats avg() to include zero values

jonuwz — Tue, 03 Sep 2013 12:01:40 GMT

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