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