Top 10 of a top 10

[BigShak]

Hello there,

I want to make a top 10 of applications based on top 10 of categories.

Here is an example:

Category	Nb of alert / category	Application	Nb of alert (by app for this category)
Cat1	8000	App1	1000
	8000	App2	100
	8000	App3	10
Cat2	5000	App1	10000
	5000	App2	688
Cat3	300	App4	4560

So I know how to get the top 10 categories but from that I don't know how to get the top 10 applications for each category found previously.

Here is what i've done so far:  (note that the 2nd column in my example doesn't exist in my query, it's just to make the example clearer)

index=my_index action=block [search index=my_index action=block | top category | table category] | stats count by category, app | stats values(app) AS apps, values(count) AS total by category

It gives me the 10 categories but they are sorted by alphabetic order instead of by number of block action and I have more than 10 applications on the second column, not sorted.

Does anyone has a solution for that? It'd be lovely.

Thanks in advance.

[ITWhisperer]

Try something like this

index=my_index action=block [search index=my_index action=block | top category | table category] 
| stats count by category, app
| sort 0 category -count
| streamstats count as rank by category
| where rank < 11

[BigShak]

Hi ITWhisperer,

Many thanks for you answer! It is working almost as I expected. The apps are properly sorted for each category and limited to 10, great! However, I still have the issue with the categories... they are ordered in alphabetical order, do you have an idea?

Thanks.

[ITWhisperer · Accepted solution]

index=my_index action=block [search index=my_index action=block | top category | table category] 
| stats count by category, app
| sort 0 category -count
| streamstats count as rank by category
| where rank < 11
| eventstats sum(count) as total by category
| sort 0 -total category -count

[BigShak]

Many thanks, have a great day 🙂