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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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)

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...