How to "stats avg(field1) by field2" and display that average in a top limit=20 field2 stats page

[shantu]

I'm working with Alert logs, which spit out log events only if certain SQL queries take longer than a threshold time. Each log event has two values of interest - QueryTime and SQLQuery. I'm extracting these fields.

I'd like to run a "top limit=50 SQLQuery showperc=f" and I'd like to add a column to show "avg(QueryTime) by SQLQuery". Can't figure out a way to do both in the same search. The end result should have the following headers:

SQLQuery avg(QueryTime) Count

[acharlieh · Accepted solution]

You want the average times of the 50 most common sql queries? what if instead of top we make use of sort to sort by the count, and then limit our result set, keeping the average query times in tact like so:

... | stats count avg(QueryTime) by SQLQuery | sort 50 - count

[shantu]

That worked as required. Thanks!