Splunk Search

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

tayvionp
Explorer

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

 

 

 

 

Labels (4)
Tags (1)
0 Karma
1 Solution

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats list(url) as url list(count) as count by user
0 Karma

tayvionp
Explorer

Thanks, this worked

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...