Splunk Search

Distinct count of all unique email addresses which do not end with certain domain names

landen99
Motivator

I want to count all unique email addresses in a multi-value "to" field which do not end with certain domain names.

stats dc(to) by mid

should count the number of unique to recipients per email message (mid). Correct me if it only counts 1 per event instead of per value in the multi-value field.

Tags (3)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

The command "| stats dc(to) by mid" will give you the distinct count of values of field 'to' by mid by per value in the multival field per event, not 1 per event.

You can use mvfilter command to count the email address NOT ending with certain domain.

E.g. to filter out all email Id from gmail.com and get distinct count of others.

Your base search | stats dc(eval(mvfilter(NOT match(to,"gmail\.com$")))) as dcNonGmail by mid

A rename of the aggregate field (dcNonGmail in above example) is required for it to work.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

The command "| stats dc(to) by mid" will give you the distinct count of values of field 'to' by mid by per value in the multival field per event, not 1 per event.

You can use mvfilter command to count the email address NOT ending with certain domain.

E.g. to filter out all email Id from gmail.com and get distinct count of others.

Your base search | stats dc(eval(mvfilter(NOT match(to,"gmail\.com$")))) as dcNonGmail by mid

A rename of the aggregate field (dcNonGmail in above example) is required for it to work.

landen99
Motivator

The method creating a multivalue field to be counted by message ID (mid) works. Thank you for finding the mvfilter.

base search | eval to_ext=mvfilter(NOT match(to,"(@|\.)abcd\.com")) | stats list(to) AS to list(to_ext) AS external_to dc(to_ext) AS to_ext_dc by mid | where to_ext_dc>15 | sort -to_ext_dc
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...