Group results by Main Domains in Splunk Proxy Log Results

[NeonFlash]

I am parsing the Blue Coat Proxys logs using Splunk Queries.

For an ip address, xxx.xxx.xxx.xxx, I am interested in viewing all the sites they visited for the past 7 days. However, I want to view only the Main Domains in the results. To make it more clear, I want to group the results like following:

Right now my search which looks something like this:

sourcetype="bcoat_proxysg" | table _time, dest_host, http_method, uri_path, uri_query | stats count by dest_host

It will show all the dest_host unique entries. It looks like this:

a1.apple.com - 21
a2.apple.com - 10
a3.apple.com - 15

and so on.

I want to group the results by main domain like apple.com and the count now should say the sum of all (21+10+15) instead. Something like:

apple.com - 46

How do I go about doing this in Splunk? Does this require some regular expression to be able to do it?

Thanks.

[Ayn · Accepted solution]

Create a new field that only includes the top domain. This can be challenging as you need to find a suitable definition of what the "top domain" is - it's not always just the last part before and after the last dot of the destination host, for instance many countries add a mandatory portion under the ccTLD, such as UK where there is "co.uk", "org.uk" and so on.

That said, to keep things simple this would work for "regular" addresses that don't use this kind of scheme:

sourcetype="bcoat_proxysg" | rex field=dest_host "(?<dest_domain>[^.]+\.[^.]+)$" | stats count by dest_domain