Splunk Search

Grab max value and associated value

feickertmd
Communicator

I have a stats table of max hits by API for a given time period.

index="ml_summary" report=api_stats earliest=-1w@w latest=now()  | stats count as hit by date_minute, date_hour,date_mday,date_month,date_year, api| stats max(hit) as maxhit by api 

This returns multiple lines, showing each api name and the max hits for that api.

How do I now reduce the results to a single line with the max hits from that set AND its associated api label?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Somesoni2's solution is good for what you asked.

There is the possibility that you might have two or more of the same highest-hit minute, and you might also like to know WHEN you got the most hits on that , so you might consider something like this -

 index="ml_summary" report=api_stats earliest=-1w@w latest=now()  
| bucket span=1m _time 
| stats count as hit by _time api
| eventstats max(hit) as overallmaxhit 
| where hit=overallmaxhit
| table api hit _time 
| eval _time=strftime(_time,"%Y-%m-%d %H:%M)

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

Somesoni2's solution is good for what you asked.

There is the possibility that you might have two or more of the same highest-hit minute, and you might also like to know WHEN you got the most hits on that , so you might consider something like this -

 index="ml_summary" report=api_stats earliest=-1w@w latest=now()  
| bucket span=1m _time 
| stats count as hit by _time api
| eventstats max(hit) as overallmaxhit 
| where hit=overallmaxhit
| table api hit _time 
| eval _time=strftime(_time,"%Y-%m-%d %H:%M)

View solution in original post

feickertmd
Communicator

Ah, thank you. I forgot about bucket!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

The first three lines were cribbed from somesoni2's response. I usually use the synonym "bin" instead of "bucket".

0 Karma

somesoni2
Revered Legend

Give this a try (slighly modified the initial part)

index="ml_summary" report=api_stats earliest=-1w@w latest=now()  | bucket span=1m _time | stats count as hit by _time api| stats max(hit) as maxhit by api | sort 1 maxhit

If you want to keep the original, just add the | sort 1 maxhit at the end of your version.

0 Karma

feickertmd
Communicator

Duh. I knew I was looking too deep and complex. Thanks!

0 Karma
.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!