topic Re: How to group similar domain/URL patterns? in Splunk Search

How to group similar domain/URL patterns?

ail321 — Thu, 03 Feb 2022 20:23:04 GMT

I would like to group URL fields and get a total count.

When I do this:

index=example source=example_example dest="*.amazonaws.com" OR dest="*.amazoncognito.com" OR dest="slack.com" OR dest="*.docker.io" | dedup dest | table dest | stats count by dest

the output is this:

dest count

352532535.abc.def.eu-xxxxx-1.amazonaws.com	1
abc.auth.xx-aaaa-1.amazoncognito.com	1
aaa1-stage-login-abcdef.auth.xx-abcd-1.amazoncognito.com	1
346345452.abc.def.us-abcd-2.amazonaws.com	1
autoscaling.xx-east-4.amazonaws.com	1
slack.com	1
registry-1.docker.io	1
auth.docker.io	1

I wanted to group them by similar patterns like this:

gruopedURL count

.amazonaws.com	3
.amazoncognito.com	2
slack.com	1
.docker.io	2

I've tried other possible queries based on some postings here, but no luck. It was mostly after the '.com'

Re: How to group similar domain/URL patterns?

PickleRick — Thu, 03 Feb 2022 22:26:47 GMT

You do way too many things in your search which actually slow it down.

Lose the dedup and lose the table. Just search and stat. That's for starters.

Secondly, use rex to extract the top part of the domain. Then do your stats

<base search>
| rex field=dest "(.*\.)?(?<base>[^.]+\.[^.]+)"
| stats count by base

Re: How to group similar domain/URL patterns?

ITWhisperer — Thu, 03 Feb 2022 22:52:39 GMT

Since you appear to already know what "common" parts of the urls you are interested in since they are part of our search filter, you could just count them

| stats count(eval(match(dest,"amazonaws\.com"))) as amazonaws.com count(eval(match(dest,"amazoncognito\.com"))) as amazoncognito.com count(eval(match(dest,"slack\.com"))) as slack.com count(eval(match(dest,"docker\.io"))) as docker.io

Re: How to group similar domain/URL patterns?

ail321 — Fri, 04 Feb 2022 14:26:54 GMT

this worked. What if I add to search something like this 170.51.31.0/22

Re: How to group similar domain/URL patterns?

johnhuang — Fri, 04 Feb 2022 17:53:45 GMT

If you're searching for the literal string "170.51.31.0/22":

index=example source=example_example "170.51.31.0/22" | stats count by <field_name>

If you're searching for ip addresses that falls into the CIDR range "170.51.31.0/22"

index=example source=example_example | search cidrmatch("170.51.31.0/22", <dest_ip_field> )