How to get the maximum count by host and time

[spkriyaz]

Hi,

I am trying to query to pick the maximum TPS count of each host(three hosts) and the time when the maximum count was reported. There maybe many instance where the count will be same for more than one time, in that case I would chose the count for the latest time. The below query picks the maximum count per host but I am unable to write the time when it was maximum.

index= xyz 
| timechart span=1s cont=false count BY host_name 
| untable _time host_name count
| stats max(count) as count BY host_name

So I need help to know how to include the _time with my output(as I said count might be same for different time in that case I need the latest time written to the output along with host and maximum count)
OUTPUT:

[ITWhisperer]

You could try something like

index= xyz 
| bin span=1s _time
| stats count BY host_name 
| eventstats max(count) as max BY host_name
| where count=max
| stats values(count) as count, latest(_time) as _time by host_name
| fields host_name, count, _time

[spkriyaz]

It won't work as the query is not picking the maximum count of each second(Transaction per second for each host), it does the overall count and the _time is not considered in the initial stats so the _time is not considered anywhere down the line

[ITWhisperer · Accepted solution]

index= xyz 
| bin span=1s _time
| stats count BY host_name, _time
| eventstats max(count) as max BY host_name
| where count=max
| stats values(count) as count, latest(_time) as _time by host_name
| fields host_name, count, _time

Good point - just add _time to the first stats (I forgot to add it after changing from timechart to stats)