Splunk Search

Get top 3 IP's for each user in top 10 list

splunklearner12
Path Finder

I have a list of top 10 users, but I also want the top 3 IP addresses used by those users in a table. Some users will have only used 1 IP while other users have sent traffic from more than 3 IP addresses. I can get top 3 IP's per user: "blah | top client_ip by user limit=3" but I can't get it into the top 10 overall users list. Any ideas?

Tags (1)
0 Karma
1 Solution

Vijeta
Influencer

@splunklearner1234 Below search should work. your base search will be same in subsearch and main search(index=indexname>sourcetype=sourcetypename)

<your base search>| stats count as ipcount  by user src|  sort 0  -ipcount| streamstats count as ucount by user| where ucount <=3| append[search <your base search>| top user ]| eventstats sum(count) as sum by user | where sum>0 and ucount>0|sort -sum user| fields - count percent ucount

View solution in original post

0 Karma

DavidHourani
Super Champion

Hi @splunklearner1234,

Lots of ways to do this, the easiest is :

 | top 3 IP BY users

More ways to do so described here :
https://answers.splunk.com/answers/750232/show-top-5-values-in-column-chart.html#comment-753389

Let me know if that helps.

Cheers,
David

0 Karma

Vijeta
Influencer

@splunklearner1234 Below search should work. your base search will be same in subsearch and main search(index=indexname>sourcetype=sourcetypename)

<your base search>| stats count as ipcount  by user src|  sort 0  -ipcount| streamstats count as ucount by user| where ucount <=3| append[search <your base search>| top user ]| eventstats sum(count) as sum by user | where sum>0 and ucount>0|sort -sum user| fields - count percent ucount
0 Karma

sjbriggs
Path Finder

I'm struggling to adapt this solution to my problem but I feel like it's the closest to what I'm looking for.

I'm simply trying to get the top 10 src_ips in bytes of web usage, then the top 10 sites each of those src_ips goes to.

My current solution is close but I can't seem to get to it just listing the top 10 sites for each IP, it seems to be doing the top sites overall and then spreading them over the src ips.

index=proxy bytes>0
| fields src domain bytes
|stats sum(bytes) AS totalbytes  by domain,src
|sort -totalbytes  | head 50
|stats list(domain) as Domain, list(totalbytes) AS Total BY  src
| sort -Total

I had to do the "head 50"  because when I did head 10, i was only getting the top 10 domains in terms of bytes transferred and that was usually over just 3 or 4 IPs.  By doing head 50, i was getting more domains to spread over more IPs but still not exactly what I wanted which would be 10 IPs and the top 10 sites for each IP.

0 Karma

splunklearner12
Path Finder

This worked perfectly - I didn't know the streamstats command and had to step through your solution to see how it works, very useful thanks.

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Unlock What’s Next: The Splunk Cloud Platform at .conf25

In just a few days, Boston will be buzzing as the Splunk team and thousands of community members come together ...