Knowledge Management

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

Path Finder

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

Tags (1)

Splunk Employee
Splunk Employee

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

Splunk Employee
Splunk Employee

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

0 Karma

Path Finder

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.

0 Karma

Path Finder

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..

0 Karma

Path Finder

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.

0 Karma

Path Finder

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma