Compare .csv file data against indexed event data

[thejamesvolta]

I have a .csv file containing a list of email addresses (approximately 35k addresses/rows). I'm trying to compare the email addresses within the .csv, against email addresses in my events to see if we have ever delivered to one of these addresses.

Thanks!

[siraj198204]

Hi,

index =casm_prod source =/opt/siteminder/log/smtracedefault.log sourcetype=smtrace supportcentral | rex "([[^]]]){10}[(?P[^]])]" | dedup sso_id | lookup identity_lookup sso as sso_id OUTPUT sso as matched_email | where matched_email!="unmatched"

this will work good ? ... i need the unmatched data with lookup table with my event ...

Thank u ..

[lguinn2 · Accepted solution]

First, set up the CSV file as a lookup table. Instructions for lookups

Note that the first line of the CSV file must be a header. For my example, I assume that the lines of your CSV file look like this:

email
[email protected]
[email protected]

When you set up the lookup definition, choose Advanced Options. Set the minimum and maximum number of matches to 1. Set the default value to "unmatched". You need to do this so that you can easily determine if there is no match for an address. For my example, I assume that you named your lookup "email_lookup".

I will assume that your events contain a field called "to_email". This search should work:

searchforevents
| dedup to_email
| lookup email_lookup email as to_email OUTPUT email as matched_email
| where matched_email!="unmatched"

Note that I dedup-ed the events before searching for a match; this will reduce the number of lookups.

HTH - please post back if this doesn't work!

[thejamesvolta]

This worked perfectly! Thank you!