Find the missing values in a lookup file from dbquery?

[Cuyose]

So I have a dbquery that returns results with a column email.

I created a lookup file with a single column, email. This lookup file only has a subset ~95% of the emails that are returned by my dbquery.

How can I easily make my dbquery only return the missing rows where email from the query does not exist in the lookup file?

[martin_mueller · Accepted solution]

You could add a column to your lookup file like this:

email,in_lookup
foo@example.com,1

and use that field to filter in your search.

[Cuyose]

I was able to get this to work, I was artificially outputting the inLookup field and it wasn't working right, so just simply doing this for the lookup |lookup {lookupDef.csv} email |fillnull value="empty" and doing a |search inLookup!=1 at the end worked.

Thanks!

[Cuyose]

I understand what you suggested and it made sense, but for some reason it appended my inLookup=1 to all the results, so doing a filter at the end of the entire results |search inLookup!=1 returned 0 results, I'm expecting to see ~200