stats for user internet traffic

[Justin]

I am trying to write a query that filters our users' network traffic. I would like the query to return information on the top 2 users in each type of web site category and when available provide the most frequently accessed URL in that category. Sometimes the URL value is NULL.

For example, results could be:

Count    Category    User    URL

27       news        bob     msn.com

15       news        sally   news.google.com

80       games       joe     gamespot.com

33       games       alice   giantbomb.com

150      social-net  jill    facebook.com

12       social-net  bob     twitter.com

60       unknown     joe

So far I have come up with this, but it does not return the top two and I know the URL value returned does not necessarily correspond with the user.

search... | stats mode(User) mode(URL) count by Category

Thanks for any help.

Here is some sample raw data ("web-browsing" is the Category and the URL exists in the second sample only):

Feb 16 16:14:54 10.10.10.21 Feb 16 16:14:54 1,2012/02/16 16:14:54,000000000,TRAFFIC,end,1,2012/02/16 16:14:53,10.10.10.3,74.125.45.94,216.221.226.40,74.125.45.94,PermitAlloutgoingwithScanning,bob,,web-browsing,vsys1,Trust,Untrust,ethernet1/2,ethernet1/1,syslog,2012/02/16 16:14:53,99723,1,55628,80,5238,80,0x400000,tcp,allow,2157,2157,2157,10,2012/02/16 16:12:27,116,allow-list,0,0,0x0,10.0.0.0-10.255.255.255,United States,0


Feb 16 16:14:54 10.10.10.21 Feb 16 16:14:53 1,2012/02/16 16:14:53,000000000,THREAT,url,1,2012/02/16 16:14:52,10.10.10.4,66.161.82.146,216.221.226.40,66.161.82.146,PermitAlloutgoingwithScanning,sally,,web-browsing,vsys1,Trust,Untrust,ethernet1/2,ethernet1/1,syslog,2012/02/16 16:14:53,52281,1,51046,80,14287,80,0x408000,tcp,alert,"www.cdgnow.com/resources/images/theme/content/secondary-nav-li-a-bg.png",(9999),business-and-economy,informational,client-to-server,0,0x0,10.0.0.0-10.255.255.255,United States,0,text/html

[kristian_kolb · Accepted solution]

If I understand you correctly, this should do what you want

your_search | stats c AS Requests mode(URL) AS Most_Popular by User, Category | sort - Requests | dedup 2 Category | sort Category

Hope this helps,

Kristian

[Justin]

Thanks Kristian, that did the trick.

[sbrant_tt]

Can you post some of the raw data that the above results will be drawn from?