Complex query on IIS log

[mamoSplunk]

Hi all,

I would like extract from intranet weblog (IIS log) top pages grouped by departments to see which pages are most viewed by each department.
I can use the cs_username field to identify the department and with the following query I can count the total activity by depertment:

sourcetype="iis" index=intranet | fields cs_username |
   rex field=cs_username "(?i)mydomain\\\(?<username>[^\s]*)" |
   stats count as events by username |
   table username events |
   lookup address.csv Email as username | fillnull value=- |
   stats sum(events) as total_events by department

Now I would like to extract the most viewed pages (cs_uri_stem) grouped by department. How can I do that?

Thank you in advance!

[mamoSplunk]

Thank you very much ITWhisperer. It works!
May I ask you if is it possible to limit the number of total_events by department to, let's say, the first ten? I would like to display the top 10 cs_uri_stem by for each department.

[ITWhisperer · Accepted solution]

sourcetype="iis" index=intranet 
| fields cs_username cs_uri_stem 
| rex field=cs_username "(?i)mydomain\\\(?<username>[^\s]*)" 
| stats count as events by username cs_uri_stem 
| table username cs_uri_stem events 
| lookup address.csv Email as username 
| fillnull value=- 
| stats sum(events) as total_events by cs_uri_stem department
| sort 0 department -total_events
| streamstats count as row by department
| where row <= 10

[mamoSplunk]

@ITWhispererI am really impressed! Thank you very much.

[ITWhisperer]

sourcetype="iis" index=intranet 
| fields cs_username cs_uri_stem 
| rex field=cs_username "(?i)mydomain\\\(?<username>[^\s]*)" 
| stats count as events by username cs_uri_stem 
| table username cs_uri_stem events 
| lookup address.csv Email as username 
| fillnull value=- 
| stats sum(events) as total_events by cs_uri_stem department