topic Re: How do you exclude an item from a lookup table and an additional condition? in Splunk Search

How do you exclude an item from a lookup table and an additional condition?

yemyslf — Thu, 28 Feb 2019 22:32:45 GMT

I have a lookup table that I'm using to exclude some devices from search results.

index = my_index  
    | lookup my_table local=true device_id OUTPUT device_id as ignore  
    | where isnull(ignore)

This works great, but I need to add an additional condition to only exclude devices if they are in the lookup table and the value of the field "code" = 0001. So an event shouldn't be excluded even if it is in the lookup table unless code=0001 and events with code=0001 should be included if they are not in the lookup table.

I've tried the following but this also removes all items where

 code=0001
index = my_index  
    | lookup my_table local=true device_id OUTPUT device_id as ignore  
    | where (isnull(ignore) AND code!=0001)

I assume this is a dumb mistake in my logic but can't figure out what I'm doing wrong?

Re: How do you exclude an item from a lookup table and an additional condition?

integratorz — Fri, 01 Mar 2019 01:14:51 GMT

If you are looking to filter out events where ignore is null you can actually do this as well: ignore="*"

This essentially means ignore needs to have a value.

Also, to do the second filter I would do it this way:

index=my_index 
| lookup my_table local=true device_id OUTPUT device_id as ignore 
| search ignore="*" code!=0001

In the last line, anything after the search command implies AND

Re: How do you exclude an item from a lookup table and an additional condition?

yemyslf — Fri, 01 Mar 2019 13:37:44 GMT

Thanks for the response. I'm actually trying to exclude events where the device_id is in my lookup table. So doing | search ignore="*" retrieves events that I want to exclude. Because "ignore" isn't a field on the other events, I can't do ignore !="*". I was using isnull(ignore) because that returns events where there is no ignore field.