How to clean up query for top 10 urls of top users?

[tayvionp]

I'm currently building a query that reports the top 10 urls of the top 10 users. Although my current query works, I would like a cleaner look.

Query:

index="zscaler" sourcetype="zscalernss-web" appclass!=Enterprise user!=unknown
| stats count by user, url
| sort 0 user -count
| streamstats count as standings by user
| where standings < 11
| eventstats sum(count) as total by category
| sort 0 -total user -count

The results look like this

user.                     url.         count  rank
john.doe@example.com.    example.com.   100.    1 
john.doe@example.com.    facebook.com.   99.    2
john.doe@example.com.    twitter.com.    98.    3
john.doe@example.com.    google.com.     97.    4
john.doe@example.com.    splunk.com.     96.    5

jane.doe@example.com.    example.com.   100.    1 
jane.doe@example.com.    facebook.com.   99.    2
jane.doe@example.com.    twitter.com.    98.    3
jane.doe@example.com.    google.com.     97.    4
jane.doe@example.com.   splunk.com.     96.    5

and so forth 
I would like for i to look like this 
user.                     url.         count 
john.doe@example.com.    example.com.   100.    
                         facebook.com.   99.    
                         twitter.com.    98.    
                         google.com.     97.    
                         splunk.com.     96.    

user.                     url.         count 
jane.doe@example.com.    example.com.   100.    
                         facebook.com.   99.    
                         twitter.com.    98.    
                         google.com.     97.    
                         splunk.com.     96.   

and so forth

[jhamot23]

I'm trying to run a similar query, but using src_ip instead of users. When I try the above queries it's giving me every src_ip instead of just the top 10.

Any suggestions on how to limit the search for just the top src_ip by top url?

I've been running something like this:

index=firewall | stats count by src_ip, url
| sort 0 src_ip -count
| streamstats count as standings by src_ip
| where standings < 11
| eventstats sum(count) as total by category
| sort 0 -total src_ip -count

[ITWhisperer]

This is a slightly different question - having said that, try something like this (assuming category should have been src_ip)

index=firewall 
| stats count by src_ip, url
| sort 0 src_ip -count
| streamstats count as standings by src_ip
| where standings < 11
| eventstats sum(count) as total by src_ip
| sort 0 -total src_ip -count
| streamstats count(eval(standings=1)) as rank
| where rank < 11

[ITWhisperer · Accepted solution]

| stats list(url) as url list(count) as count by user

[tayvionp]

Thanks, this worked