filter out top 10% of the results sorted in desc order

[tanujsaxena]

Hi all,

i am trying the below query.. i need result for only top 10% of the total result. the query i am using is

..| transaction correlationId |stats avg(duration) as "Average" min(duration) as "Minimum Response Time" max(duration) as "Maximum Response Time"

the query basically returns the time taken for execution. i need to extract data on weekly basis.

i need data for top 10% of the total value sorted to avg(duration).

[lguinn2]

I believe that this will do what you want, and it will be much more efficient:

yoursearchhere
| stats range(_time) as duration by correlationId
| stats avg(duration) as Average min(duration) as "Minimum Response Time" max(duration) as "Maximum Response Time"
| sort 0 -Average
| streamstats count as row 
| eventstats count as total
| where round(row/total,0) <= .1
| fields - row total

The above gives you the top 10% based on the count of results. In other words, if you have 150 correlationIds, you will get a list of the 15 with the greatest average duration.

[tanujsaxena]

thanks a lot. i tried but somehow it is giving the result for top 50% of the total result. the no of events is 269 and the output comes out to be for top 134 events.

[lguinn2]

Leave off the last 2 lines and you will be able to see the row numbers and the total count of the rows... maybe that will help you figure out what is going on

[somesoni2]

I don't completely understand the requirement here. Your stats gives you single row output, so you want to consider top 10% before the stats? OR you want to run this for multiple weeks and want to generate this single row output for top 10% week?

[tanujsaxena]

i want the top 10% of the total data. For ex: if i run my query for a week and i get 1000 results sorted in desc order i need output only for top 10% of the results. i.e. for top 100 lines in a result of 1000 lines.