Need to compare the contents of a lookup file with search results

[jcaron9999a]

Hi,

   Brand new to splunk here.  I've been using it about 1 month.  I have a lookup file, all_identities_prod.csv, that has a single column of identities (these look like email addresses) and the column name is 'identity'.  What I've been trying to do is to compare the identities in the file with the result of a 30 day search to find identities that are in the lookup file but not in the search results.  I've tried this many different ways.  Maybe using a lookup file for this isn't the best idea.  I don't know.  Here's an example of what I've been trying.  A subsearch seemed like the best idea.

| inputlookup all_identities_prod.csv where NOT [search index=sec ab_id=cvo host=xxx-xxxx* identity="*" | dedup identity] | table identity

I have to obfuscate the host name understandably.  What I get back from this is the full list of the identities in the lookup file.  I think the comparison function is where I'm lost.   Any ideas?  Maybe a better way of doing this altogether?

Thanks in advance.

[gcusello · Accepted solution]

Hi @jcaron9999a,

let me understand: you want to know the identities from your lookup not present in the search results, is it correct?

If this is your need, please try this:

index=sec ab_id=cvo host=xxx-xxxx* identity="*" 
| eval identity=lower(identity)
| stats count BY identity
| append [ 
     | inputlookup all_identities_prod.csv 
     | eval identity=lower(identity), count=0
     | fields identity count
     ]
| stats sum(count) AS total BY identity
| where total=0 

Ciao.

Giuseppe

[jcaron9999a]

Thanks Giuseppe.  Works perfectly.

[gcusello]

Hi @jcaron9999a,

good for you, see next time!

Please accept the answer or the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[richgalloway]

Try adding the format command to the subsearch.

| inputlookup all_identities_prod.csv where NOT [search index=sec ab_id=cvo host=xxx-xxxx* identity="*" | dedup identity | format] 
| table identity