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

jhamot23
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...