What is the best way to have a sub-search based on event type?

[rjyetter]

Here's my problem, we have mutiple regional event types based on CIDR IP ranges - within those regions we also have location event types based on CIDR IP ranges - If I wanted to do a search based on a region and then have that region break down the events by location, how can I accomplish this?

search sourcetype="bcoat_proxysg" NOT "DENIED" cs_username!="-" cs_username!=Guest* cs_username!=Topaz* cs_username!=FWIPlayer* cs_username!=Server* cs_username!=IPelev* cs_category!="Web Advertisements" cs_host!=*.stuff.edu cs_host!=*.things.com
| eval location = mvfilter(eventtype LIKE "location-%")
| stats count(eval(sc_status=200 and rs_content_type="text/html")) as "Page Views" by cs_username
| rename cs_username as "Username"
| sort -"Page Views" limit=20 

The mvfilter(eventtype LIKE "location-%") isn't really helping me out here. It would be nice if there were a way to take a regional event and break it down by the top 20 location events. Any thoughts to this?

Thanks,

Rick

[Stephen_Sorkin]

I'll assume that you have eventtypes that look like "location-..." and "region-..." but as long as you create location and region fields, this technique will work:

search sourcetype="bcoat_proxysg" sc_status=200 rs_content_type="text/html" ...
| eval location = ...
| eval region = ...
| top 20 location by region
| rename count as "Page Views"

In response to your comment:

sourcetype="bcoat_proxysg" NOT "DENIED" cs_username!="-" cs_username!=Guest* cs_username!=Topaz* cs_username!=FWIPlayer* cs_username!=Server* cs_username!=IPelev* cs_category!="Web Advertisements" cs_host!=*.stuff.edu cs_host!=*.things.com sc_status=200 rs_content_type="text/html"
| eval location = mvfilter(eventtype LIKE "location-%")
| eval region = ...
| stats count as "Page Views" by region, location, cs_username
| dedup 20 region, location sortby -"Page Views"
| sort region location

[hulahoop]

🙂 Stephen, thank you for the help. Rick, see you tomorrow.

[rjyetter]

Freaking awesome! This does exactly what I need it to do.. now to sift through about 8 billion events and play some golf while it runs.

[rjyetter]

So the output should look kind of like this
Region,Location, Username, Page Views
Mt.West,Denver,Homer,50000
Midwest,St.Louis,Marge,26000
etc..
etc..

[rjyetter]

Vi - They "meaning senior management" Wants to see the top internet surfers broken down by location by region. So they are wanting to see a pivot table sort of output from Splunk. I'm not sure if I'll be able to get them that information.

[rjyetter]

I tried this and a number of different iterations with 0 results:
search sourcetype="bcoat_proxysg" NOT "DENIED" cs_username!="-" cs_username!=Guest* cs_username!=Topaz* cs_username!=FWIPlayer* cs_username!=Server* cs_username!=IPelev* cs_category!="Web Advertisements" cs_host!=.phoenix.edu cs_host!=.aptimus.com |stats count(eval(sc_status=200 and rs_content_type="text/html")) as "Page Views" by cs_username| rename cs_username as "Username"| eval region = mvfilter(eventtype LIKE "region-%")|eval location = mvfilter (eventtype LIKE "location-%")| top 20 location by region

[hulahoop]

Rick, it would be helpful if you posted a data sample / current output / desired output.