Splunk Search

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

akarivaratharaj
Communicator

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.

Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Communicator

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

0 Karma

FrankVl
Ultra Champion

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
Engager

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

*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>