How to calculate the average based on fields.

[theouhuios]

Hello

I think this should be simple enough but somehow I am not able to understand how to approach it.
Here is the search which I am using

sourcetype="xxxx" record.eventType="create"|stats count by record.affectedCI

and the data looks ;like.

record.affectedCI   count

1 LT95DB10 1
2 SNMX2646005T 1
3 SNMX2649003N 1
4 SNMX265100A8 1
5 SNUSE717N4A3 1
6 SNUSE722N6PM 1

What I need to know is to find the average of count over all affected CI's. I did use the stats avr() but somehow that isn't giving me the output which I wanted. This shouldn't be difficult,just that I am not able to think on how to approach it now 😛

Regards

theou

[yannK · Accepted solution]

if you want an average of all the counts you already calculated from the first search :

sourcetype="xxxx" record.eventType="create"|stats count by record.affectedCI | stats avg(count)

[jviray]

Curious as to why stats has to be ran twice.  Even using table before stats doesn't work to get the proper average.

[Taruchit]

Hello Sir,

I tried following your post and tried to fetch average number of errors during 09/7/21 12:00:00:000 AM to 09/14/21 12:00:00:000 AM.

index=* <search condition>|stats count by error | stats avg(count) by error

I got two columns: error and avg(count). However, I am unable to comprehend how the values were calculated in second column. I tried taking the error counts for each day from 09/7 to 09/13, and calculated the average,  the result did not match with the result obtained from the search query. 

Thus, need your help to understand how the data was calculated and the steps to correct the query. 

Thank you

[theouhuios]

Strange. I did the same before but couldn't get the answer. I guess I mistyped something.

[jonuwz]

Add this

... | eventstats avg(count) as avg_count