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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...