topic Re: dedup, distinct, similar in Splunk Search

dedup, distinct, similar

jgauthier — Thu, 31 Mar 2011 00:25:44 GMT

All,

I am trying to remove duplicate values in a list of email addresses. First, I am loading this from a CSV, inside that CSV is a semi-colon delimited list of email recipients. In that list, some of the email recipients are duplicated. Obviously, they only received the email one time, and want that metric.

So, I've put together a query to start that looks like this:

sourcetype="email" sender="jgauthier*" subject="Specific email" | eval recipientlist=split(recipient, ";")

So, my recipientlist now contains the duplicate email addresses. (from recipient, but split up)

For one email, it appears that it's gone to the same person twice (since they are in the list twice).

I've played with dedup, but it doesn't seem to work in this regard.

Thanks for the help.

Re: dedup, distinct, similar

hazekamp — Thu, 31 Mar 2011 00:32:17 GMT

This is likely caused by the way dedup is behaving with the MV field created by eval-split. Try:

sourcetype="email" sender="jgauthier*" subject="Specific email" | eval recipientlist=split(recipient, ";") | stats count by recipientlist | fields - count

If you want the "count" just remove "| fields - count" above...

Re: dedup, distinct, similar

hazekamp — Thu, 31 Mar 2011 00:37:30 GMT

Looks like Sorkin also addressed this in:

http://answers.splunk.com/questions/12396/dedup-and-multivalued-fields

Re: dedup, distinct, similar

jgauthier — Thu, 31 Mar 2011 00:46:57 GMT

I saw Sorkin's thread before I posted and worked with it.
But neither that effort or the one above seemed to accomplish this. Simply doing "stats count by recipientlist" gives me a count of when the string exists multiple times in the recipientlist. "| fields - count" just gives me the field. Even if I have 100 distinct events, with a field containing duplicate values.
Thanks!

Re: dedup, distinct, similar

hazekamp — Mon, 28 Sep 2020 09:26:52 GMT

I am not sure why stats is not satisfying the use case of "trying to remove duplicate values...". I tested this locally and if you have:

Event 1:
recipient=a;b;c;c

Event 2:
recipient=x;y;z;a

Produces:

a 2
b 1
c 2
x 1
y 1
z 1

Which is a consolidate list of email addresses.