How to fetch only the top x rows for each category in the same search query?

[akarivaratharaj]

I am trying to fetch top 10 max Requests count of events with their corresponding response time. So using the below query:

index=x host=prod* sourcetype=y 
|timechart span=1m count(Req) as Requests, sum(Resp_Time_MS) as "Total Response Time", max(Resp_Time_MS) as "Maximum Response Time", p95(Resp_Time_MS) as "95%ile of Response Time" 
|sort -Requests 
| head 10

So the result is:

'_time' 'Requests' 'Total Response Time' 'Maximum Response Time' '95%ile of Response Time'
'2018-06-10 07:10:00'   '71653' '19141836'  '786602'      '560'
..

The above query is giving me the top 10 highest Requests in common among all hosts. But I want top 10 highest values of Requests for each host (such as ProdA, ProdB, ProdC and ProdD). I have tried to group the results with the help of 'by' clause as "by host" but it is not giving the correct results.

So expected result is:

'_time' 'host' 'Requests' 'Total Response Time' 'Maximum Response Time' '95%ile of Response Time'
'2018-06-10 07:10:00' 'ProdA' '71653'   '19141836'  '786602'      '560'
'2018-06-10 07:05:00'   'ProdA' '58407' '21815806'  '1105451'   '626'
(Should display all 10 values)
'2018-06-18 06:59:00'   'ProdB'  '42013'    '18745773'  '894743'     '622'

So on.

[somesoni2 · Accepted solution]

Try like this

index=x host=prod* sourcetype=y 
| bucket span=1m _time
| stats count(Req) as Requests, sum(Resp_Time_MS) as "Total Response Time", max(Resp_Time_MS) as "Maximum Response Time", p95(Resp_Time_MS) as "95%ile of Response Time" by host _time
| sort 10 -Requests by host

[akarivaratharaj]

@somesoni2, Awesome!! This is working fine. In addition to this I have added 'dedup', which is giving me top 10 Requests in each hosts & with their timestamp and response time.

index=x host=prod* sourcetype=y
| bucket span=1m _time
| stats count(Req) as Requests, sum(Resp_Time_MS) as "Total Response Time", max(Resp_Time_MS) as "Maximum Response Time", p95(Resp_Time_MS) as "95%ile of Response Time" by host _time
| sort 10 -Requests by host
|dedup 10 host

Thank you all for the help!!

[FrankVl]

That will be a bit tricky, since head doesn't support a by clause.

To start, you would need to add by host to your timechart command, to even make the host information available. Next step is to sort by host and within that sort by Requests. Then you need to number the lines for each host and filter out the first 10.

 index=x host=prod* sourcetype=y
| timechart span=1m count(Req) as Requests, sum(Resp_Time_MS) as "Total Response Time", max(Resp_Time_MS) as "Maximum Response Time", p95(Resp_Time_MS) as "95%ile of Response Time" by host
| sort host,-Requests
| streamstats reset_on_change=true count by host
| where count<=10

[Shera]

Thank you for this one! Saved the day for me)