topic Re: Lookup - Filter a search with lookup content in Splunk Search

Lookup - Filter a search with lookup content

NunnuN — Wed, 06 Sep 2023 14:03:54 GMT

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.

Re: Lookup - Filter a search with lookup content

gcusello — Wed, 06 Sep 2023 14:21:37 GMT

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

Re: Lookup - Filter a search with lookup content

bowesmana — Wed, 06 Sep 2023 22:47:56 GMT

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