dedup, distinct, similar

[jgauthier]

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.

[hazekamp · Accepted solution]

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...

[hazekamp]

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

This search:
index=_internal | head 1 | eval recipient="a;b;c;c" | append [search index=_internal | head 1 | eval recipient="x;y;z;a"] | eval recipientList=split(recipient, ";") | stats count by recipientList

Produces:

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

Which is a consolidate list of email addresses.

[jgauthier]

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!

[hazekamp]

Looks like Sorkin also addressed this in:

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