Splunk Search

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

shantu
Explorer

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

Tags (3)
0 Karma
1 Solution

acharlieh
Influencer

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

View solution in original post

acharlieh
Influencer

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
Explorer

That worked as required. Thanks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...