Splunk Search

How do I search for the top 10 users top 10 URL hits per month?

neilsussman
Explorer

I'm fairly new to Splunk search strings so hopefully someone can help. I'm trying to create a three column search:
1. Users (top 10 by the sum of the URL hits)
2. URL (top 10 by hits)
3. Hit count for each of the top 10 URL's

Here is where I'm at with the search string:

index="websense_events" category_name="*" sourcetype=websense_incoming user_login_name="*" ou_name="*OU=*"| top limit=10 tld by user_login_name | sort -count| fields - percent | head 10

I can't seem to figure out how to get more than one URL and hit count to appear in the output. Can anyone help?

Tags (5)
1 Solution

Ron_Naken
Splunk Employee
Splunk Employee

If you are looking for an output that looks like this:

Dopey    15    snowwhite.com
         10    evilqueen.com
Sneezy   20    evilqueen.com

...and you want the inner lists of URL's sorted in descending order, then you want the list sorted in descending order by total URL hits for a user, your search would look like this:

index="websense_events" category_name="*" sourcetype=websense_incoming user_login_name="*" ou_name="*OU=*" | eventstats count AS total by user_login_name | stats count first(total) AS total BY user_login_name tld | sort - count | stats list(count) AS count list(tld) AS url first(total) AS total by user_login_name | sort - total | fields - total | head 10 | eval url=mvindex(url, 0, 9) | eval count=mvindex(count, 0, 9)

This assumes that your URL field is 'tld'. This should provide a sorted Top 10 URL's per user with individual hit count, as well as a sorted list by total counts for each user, also filtered to show only the Top 10 Users by Total.

Cheers,
Ron

View solution in original post

Ron_Naken
Splunk Employee
Splunk Employee

If you are looking for an output that looks like this:

Dopey    15    snowwhite.com
         10    evilqueen.com
Sneezy   20    evilqueen.com

...and you want the inner lists of URL's sorted in descending order, then you want the list sorted in descending order by total URL hits for a user, your search would look like this:

index="websense_events" category_name="*" sourcetype=websense_incoming user_login_name="*" ou_name="*OU=*" | eventstats count AS total by user_login_name | stats count first(total) AS total BY user_login_name tld | sort - count | stats list(count) AS count list(tld) AS url first(total) AS total by user_login_name | sort - total | fields - total | head 10 | eval url=mvindex(url, 0, 9) | eval count=mvindex(count, 0, 9)

This assumes that your URL field is 'tld'. This should provide a sorted Top 10 URL's per user with individual hit count, as well as a sorted list by total counts for each user, also filtered to show only the Top 10 Users by Total.

Cheers,
Ron

lguinn2
Legend

Try

index="websense_events" category_name="*" sourcetype=websense_incoming user_login_name="*" ou_name="*OU=*" |
top user | fields user |
appendcols [search index="websense_events" category_name="*" sourcetype=websense_incoming user_login_name="*" ou_name="*OU=*" |
top url | rename count as urlHitCount | fields url urlHitCount ]

However, I think that this is a bit misleading, as it makes it look like the users have some relationship to the URLs that are listed, and they do not. I would prefer a dashboard with 2 panels, where the first panel shows the top users and the second panel shows the top URLs. For me, that would be more clear.

But perhaps I misunderstood the question?

0 Karma

neilsussman
Explorer

Thanks Iguinn. While this wasn't exactly what I was looking for it did put me on the right track.

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 ...