get rows in lookup that does not have entry in search result

[dchoubey]

I have a Lookup "Consumer_Lookup.csv" (30 rows approx)

Consumer     Restricted

A                         Y

B                         Y

C                         N

Search - Index = "xyz" |stats count(Status) AS Total by ClientId|Table ClientId TOtal

ClientId         Total

A                       500

C                       200

my requirement is to find any Consumer in Lookup with Restricted = "Y" not in the search result. Can you please advise on how to proceed. Should I use Join or any other alternative .

Thanks for your help!!

[dchoubey]

Thank you @abowesman it worked perfectly

[abowesman · Accepted solution]

@dchoubey 

base_search
| append [
  | inputlookup Consumer_Lookup.csv
  | rename Consumer as ClientId
]
| stats values(*) as * by ClientId
| where isnull(Total)

will do the trick