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

[neilsussman]

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?

[Ron_Naken · Accepted solution]

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]

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?

[neilsussman]

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