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

[landen99]

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.

[somesoni2 · Accepted solution]

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]

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