topic Re: How to exclude results from a search by referencing a lookup table? in Splunk Search

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

ezmo1982 — Tue, 01 Sep 2020 15:00:35 GMT

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!

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

richgalloway — Tue, 01 Sep 2020 15:19:38 GMT

Try a subsearch.

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

rnowitzki — Tue, 01 Sep 2020 15:30:48 GMT

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