How to exclude IPs through lookup

[ebs]

Hi,

I want to exclude IPs when performing this search, but despite the IPs being present in the lookup they still aren't excluded. I'm not sure what I'm doing wrong in my search. Please advise. And yes, my lookup table is correct.

sourcetype=audit dest!=secure-uat NOT ( [|inputlookup IP_Allow | rename ip as src_ip | fields src_ip | return 10000 $src_ip] )
| timechart span=1h dc(user) by src_ip useother=0 usenull=0
| stats max(*) AS *

[bowesmana]

Are you sure the max limit is 10000 on your subsearch and that it's only using part of the lookup?

You can always check with this 

| makeresults
| append [
  |inputlookup IP_Allow | rename ip as src_ip | fields src_ip | return 10000 $src_ip
]

Is there any reason you're returning $src_ip rather than src_ip. That would make a less efficient search as it's then doing the NOT with the IPs against _raw rather than src_ip

An option to validate the lookup is to move the NOT clause to a lookup/where test, i.e.

sourcetype=audit dest!=secure-uat
| lookup IP_Allow ip as src_ip OUTPUT ip as found
| where isnull(found)
| timechart span=1h dc(user) by src_ip useother=0 usenull=0
| stats max(*) AS *

It might be that this approach is faster than using a possible large NOT clause, but would depend on your data.

[ebs]

This didn't work, I'm still getting the excluded IPs

[bowesmana]

@ebs 

If the lookup statement is showing that the IP is not found (i.e. found=null) then the lookup is not working, i.e. the field 'ip' in the lookup file is NOT matching the src_ip field in the event.

I see your lookup is called IP_Allow - I assume that as it has no .csv extension, you have created a lookup definition mapped on to a CSV or kvstore. Is that right?

What is your lookup definition, have you added any settings for that lookup that might influence the lookup itself.

Also, it would be useful to see an example of the column name for your lookup file containing the IP and also an event containing the src_ip field.