Splunk Search

How to exclude results from a search by referencing a lookup table?

ezmo1982
Path Finder

Hi,

I have a search that is returning values from certain fields of an index. I would like the search to use a lookup table and check if the values exist in the lookup table. If they do, I need them to be excluded from the search results.

My search term is below and returns 3 fields of the index in question:

index=duo
| fields user location.country location.city | table user location.country location.city

My lookup table is named locations.csv, which has 3 columns - user, country, city  So as an example, values for one row could be John France Paris . If the search returns a result where user=John, location.country=France and location.city=Paris, I want that to be excluded from the search results as it exists in the lookup file. 

It is important that all 3 values must exist on a row in the lookup file csv, for it to be excluded in the search results.

Can someone please help me on this?

Thanks!

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Try a subsearch.

index=duo NOT [ | inputlookup locations.csv | rename country as 'location.country', city as 'location.city' | fields user 'location.country' 'location.city' | format ]
| fields user location.country location.city | table user location.country location.city
---
If this reply helps you, Karma would be appreciated.

View solution in original post

rnowitzki
Builder

Hi @ezmo1982 ,

This should do it. I had to rename user, because I needed it to have another name as the one in the lookup and i had to replace the dot in location.city and location.country with a "_", because my where did not work correct when the fields had the dot (don't know why...)

| makeresults 
| eval user="John", location.country="France", location.city="Paris"
| rename user AS search_user, location.country AS location_country, location.city AS location_city
| appendcols [| inputlookup locations.csv]
| where NOT (location_country==country AND user==search_user AND location_city==city)
| fields user, location_country, location_city

You used need the lines starting with the rename command, the 2 above them where just to make up an event with a sample dataset.

Hope it works with your data.

BR
Ralph

 

--
Karma and/or Solution tagging appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try a subsearch.

index=duo NOT [ | inputlookup locations.csv | rename country as 'location.country', city as 'location.city' | fields user 'location.country' 'location.city' | format ]
| fields user location.country location.city | table user location.country location.city
---
If this reply helps you, Karma would be appreciated.
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 ...