Lookup - Filter a search with lookup content

[NunnuN]

Greetings.

I am quite new to Splunk and read a lot of sources.

However, I have a hard time to find my answer about the join and eval functions.

I have a first search on an index. I want to filter this search with values of one field in a csv I import as lookup.

Example:

index="data" sourcetype="entities" | table EMAIL EXTERNAL_EMAIL CATEGORY

And I have the inputlookup

 inputlookup 20230904_NeverLoggedIn.csv

How do I compare the field EXTERNAL_EMAIL from the index to the E_MAIL field in the csv file as a filter?

Many thanks for the help.

[bowesmana]

As @gcusello points out you can do this with a subsearch/inputlookup to the outer search or you can do it with a lookup + where clause - try both and use the one that gives you the best performance

index="data" sourcetype="entities" 
``` This will lookup the EXTERNAL_EMAIL field in the data against the E_MAIL
    field in the CSV ```
| lookup 20230904_NeverLoggedIn.csv E_MAIL as EXTERNAL_EMAIL OUTPUT E_MAIL as Found
``` If the EXTERNAL_EMAIL is Found in this case, it will give you the result
    Changed to isnull(Found) to find users that do NOT exist in the CSV ```
| where isnotnull(Found)
| table EMAIL EXTERNAL_EMAIL CATEGORY

[gcusello · Accepted solution]

Hi @NunnuN,

let me understand: do you want to take only the E_MAILs in the index that match with the lookup or what else?

if you want the matching E-MAILs, you have to use a subsearch like the following:

index="data" sourcetype="entities" [ | inputlookup 20230904_NeverLoggedIn.csv | rename E_MAIL  AS EXTERNAL_EMAIL | fields EXTERNAL_EMAIL ]
| table EXTERNAL_EMAIL CATEGORY

 Ciao.

Giuseppe