Splunk Search

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

jcaron9999a
Explorer

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.

 

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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
Explorer

Thanks Giuseppe.  Works perfectly.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

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
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...