How to exclude internal ips from a lookup file

[webnair]

|inputlookup internal_ip.csv gives me list of all internal IP's. I need to exclude these IP's in my below search query:

index=test_web | rex field=targetfile "(/[^/]+){1}/(?P.*)" | where (sourcetype="access_combined" AND like(filename,"%tar") AND (method="GET" OR method="HEAD")) OR (sourcetype="owncloud") | eval username=ifnull(username,user,username) | rename filename as "TAR Name" | lookup owncloudpackages.csv "TAR Name" output "Product Name" | rename "Product Name" as productname | transaction clientip username maxevents=-1 maxpause=3600 | eval productnames=replace(productname,"SWname 7", "SW 7") | stats count(duration) as Downloads by productnames | where Downloads > 1 | sort -Downloads | rename Downloads as "Product Downloads" | rename productnames as "Product Names"

[martin_mueller · Accepted solution]

You could do this:

index=test_web NOT [inputlookup internal_ip.csv | rename ip_field_from_lookup as clientip | fields clientip | dedup clientip] | ...

Different topic, the search seems convoluted in places...
Does that index only contain those two sourcetypes? If not, you should add sourcetype=access_combined OR sourcetype=owncloud to the base search to boost performance.
You can incorporate both rename calls into the lookup by using the as keyword.
You can also replace if(isnull(A),B,A) by coalesce(A,B) - this takes more than two fields as well, in case you have huge if(isnull(),,)-trees.

[martin_mueller]

Great, don't forget to mark this as solved.

[webnair]

Thanks a bunch.