https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520298#M146508 <P>You could try something like</P><LI-CODE lang="markup">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</LI-CODE><P>&nbsp;</P> Fri, 18 Sep 2020 10:17:01 GMT ITWhisperer 2020-09-18T10:17:01Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520294#M146507 <P>Hi,</P><P>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.</P><P>&nbsp;</P><LI-CODE lang="c">index= xyz | timechart span=1s cont=false count BY host_name | untable _time host_name count | stats max(count) as count BY host_name</LI-CODE><P>&nbsp;</P><P>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)<BR /><STRONG>OUTPUT:</STRONG></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.JPG" style="width: 999px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/10882i9D75FECE580D9ACA/image-size/large?v=v2&amp;px=999" role="button" title="Capture.JPG" alt="Capture.JPG" /></span></P> Fri, 18 Sep 2020 10:02:02 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520294#M146507 spkriyaz 2020-09-18T10:02:02Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520298#M146508 <P>You could try something like</P><LI-CODE lang="markup">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</LI-CODE><P>&nbsp;</P> Fri, 18 Sep 2020 10:17:01 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520298#M146508 ITWhisperer 2020-09-18T10:17:01Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520299#M146509 <P>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</P> Fri, 18 Sep 2020 10:29:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520299#M146509 spkriyaz 2020-09-18T10:29:48Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520300#M146510 <LI-CODE lang="markup">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</LI-CODE><P>Good point - just add _time to the first stats (I forgot to add it after changing from timechart to stats)</P> Fri, 18 Sep 2020 10:32:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-maximum-count-by-host-and-time/m-p/520300#M146510 ITWhisperer 2020-09-18T10:32:48Z