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

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

akarivaratharaj — Mon, 18 Jun 2018 07:40:59 GMT

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.

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

FrankVl — Mon, 18 Jun 2018 08:03:51 GMT

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

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

somesoni2 — Mon, 18 Jun 2018 17:42:22 GMT

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

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

akarivaratharaj — Tue, 29 Sep 2020 20:01:55 GMT

@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!!

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

Shera — Thu, 17 Feb 2022 11:56:09 GMT

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

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

yuanliu — Wed, 24 Sep 2025 01:29:05 GMT

I just realized that the key to the solution is sort + dedup. sort alone would not fix the problem; in fact, sort 10 could misfire in some datasets, and by host is not a useful addition to sort. So, the solution should be

| sort -Requests | dedup 10 host