Display top 25 url_host's per Userid

[aaronnicoli]

Hi all,

I've been working for the last week or two with content keeper logs, they're csv based and contain the following relevant fields:

Date/Time, Userid, Data (bytes), URL

Now, what I am trying to do is this. Run a search that strips the base domain from the URL (easy done via regex extraction), then calculates a the total amount of data used on each specific domain for each specific user.

Then! - Provide an output showing the Userid and top 25 BaseDomain's listed in order of Data logged against each.

Anyone able to help?

Thanks,
Aaron.

[olegsmartling · Accepted solution]

|stats sum(data) as totaldata by userid url
|sort 0 +userid +totaldata desc
|top limit=25 totaldata url by userid showcount=false showperc=false
|table userid url totaldata

This is based on a similar problem I solved. I had to modify it slightly to fit into your problem so some small errors are possible.

[aaronnicoli]

WOW!

Flawless what I have been after for near on 12 months now, thanks so much for your help!
Only issue I see is it's sorted by userid only and not userid and then 1st most used site, 2nd most used... etc.

But, thanks again!

[sideview]

So, amongst all the combinations of UserId AND BaseDomain values, you want to see the top 25 combinations by data logged?

assuming your regex is either done in the config or in a separate rex command (not shown)

<your search> | stats sum(Data) as bytes by Userid, BaseDomain | sort - bytes | head 25

But you might mean other things, like for a particular user as typed into a field or selected from a pulldown, show the top 25 BaseDomain values in order of data logged... I'll be happy to update if I've misinterpreted.

[aaronnicoli]

Not quite what I am after, this is simply displaying the top 25 domains by data by user.

I'm after the top 25 domains by data for every user.

As in user a has these top 25, user b has these top 25, user c has these etc. etc.

Hard to explain sorry.