List above average values

[geoff_still]

Hi Folks.

I'm not experienced with Splunk but have gone through the Search tutorial and have checked this blog trying to find someone with a similar issue with no luck.

I'm looking at trying to show values that are above the average of the same set of values.
The first bit I'm doing is ... | top src limit=0 countfield=MAX which works fine.
The second bit I'm doing is ... | top src limit=0 countfield=MAX | stats avg(MAX) as AVG which works fine and gives me the average of MAX.

What I'm then trying to do is compare MAX to AVG and only show those MAX value which are higher than AVG. The following search doesn't work but it kind of shows what I'm trying to do.
...top src limit=0 countfield=MAX | stats avg(MAX) as AVG | where MAX>AVG

I think the issue is that the stats avg command is filtering out the MAX results, so there's nothing left for the "where" at the end. Once I get this working, I'll probably use something like "anomalies" to just show MAX values a particular distance from AVG.

[Drainy · Accepted solution]

Well, another way you could spin this is to do something like;

| stats max(metric) as MAX, avg(metric) as AVG

And then stick your where/filter after that

[geoff_still · Accepted solution]

Thanks Drainy, as the metrics not a number but an IP address I don't think that's going to work for me as is, but it may have shown me the way. I've combined my search and your suggestion to get:
top metric limit=0 countfield=MAX | stats max(MAX), avg(MAX) as AVG

It's not what I was originally aiming for as "top" gives me a list and max just gives me the highest value, but I'm actually mostly interested in that highest value and its distance from the average. Thanks!

[Drainy · Accepted solution]

Well, another way you could spin this is to do something like;

| stats max(metric) as MAX, avg(metric) as AVG

And then stick your where/filter after that