Inputlookup used twice in 1 search with a NOT

[willadams]

I have a problem with a 2nd NOT inputlookup that doesn't work.  If I break out of the 2nd inputlookup and run this within SPL it works.  For example the following search would work

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest
]
| search NOT interest IN ("*jump*","*sheet*","*hang*","*worry*")
| table interest

 however if I then move this into a lookup it ignores the CSV file and shows  me data that I have omitted

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest
]
| search NOT [|inputlookup myexcludedstuff.csv | rename field1 AS interest | fields interest]
| table interest

[richgalloway]

Try adding format to the subsearch.

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest
]
| search NOT [|inputlookup mystuff.csv | rename field1 AS interest | fields interest | format]
| table interest

[willadams]

Apologies I have corrected the 2nd XML example (copy and paste fail).  I tried to add a | format to my subsearch but it still operates as though the NOT for the lookup doesn't exist.

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest
]
| search NOT [|inputlookup myexcludedstuff.csv | rename field1 AS interest | fields interest | format]
| table interest

[richgalloway]

Where is the XML?

Another format command may be needed.

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest | format
]
| search NOT [|inputlookup myexcludedstuff.csv | rename field1 AS interest | fields interest | format]
| table interest

Or you can use a single statement

index=foo sourcetype=foosource
[| inputlookup mystuff.csv
| rename field1 AS interest
| fields interest | format
] 
NOT [|inputlookup myexcludedstuff.csv | rename field1 AS interest | fields interest | format]
| table interest

The objective is to turn each lookup table into a valid expression such as (interest="foo" OR interest="bar") and the final query becomes

index=foo sourcetype=foosource (interest="foo" OR interest="bar")
NOT (interest="baz" OR interest="bat")
| table interest

You can verify what the final query looks like in the search log.

Run each inputlookup command separately to verify the results are valid expressions.