how can i determine which events contain values that are > the avg value for all the events? I'd also like to count the values (using eval) so for example : if the count of events whose value > avg(value * 1.2 ) its HIGH, if the count of events whose value > avg(value * 2) its VERY HIGH
You can use eventstats
to calculate the avg on each row, then use a where
to limit your results:
... | eventstats avg(<your_field>) AS avg | where <your_field> > avg
Or if you don't want to limit your results, and simply want a new field to "label" as 'high' or 'very high' you can use an eval after the eventstats
:
... | eventstats avg(<your_field>) AS avg | eval label = case(<your_field> > avg * 2, "VERY HIGH", <your_field> > avg, "HIGH", 1=1, "NORMAL")
If you need to calculate the average by a specific field, just add a by
statement
... | eventstats avg(<your_field>) AS avg by <your_by_field> | eval label = case(<your_field> > avg * 2, "VERY HIGH", <your_field> > avg, "HIGH", 1=1, "NORMAL")
Example:
value host
2 A
4 A
2 B
After using the last code snippet I gave you, the results would look like this:
value host avg label
2 A 3 NORMAL
4 A 3 HIGH
2 B 2 NORMAL
Hope this helps