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

[ezmo1982]

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!

[rnowitzki]

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

[richgalloway · Accepted solution]

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