Splunk Search

how can i determine which events contain values that are > the avg value for all the events?

pc1234
Engager

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

Tags (2)
0 Karma

aholzer
Motivator

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

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!