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?
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