Grab max value and associated value

[feickertmd]

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?

[DalJeanis · Accepted solution]

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)

[feickertmd]

Ah, thank you. I forgot about bucket!

[DalJeanis]

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

[somesoni2]

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.

[feickertmd]

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