Lookup not working, it is generating a "NOT ()" query for some reason.

[Cuyose]

lookup contains 3 columns DeviceId, host, and storeNumber

splunk events contain a Properties.DeviceName field that matches the DeviceId in the lookup.

When I attempt the following
baseSearch
Properties.DeviceName=*
| search Properties.DeviceName IN
[| lookup SPCClientMaster DeviceId AS Properties.DeviceName
]

Error in 'search' command: Unable to parse the search: Comparator 'IN' has an invalid term on the right hand side: NOT.

Why is this happening?

[oscar84x]

I'm not sure if this is related but if you're trying to run a subsearch your square bracket is not in the right place and you have an extra pipe. Should be like this:

baseSearch
Properties.DeviceName=*
[ search Properties.DeviceName IN
| lookup SPCClientMaster DeviceId AS Properties.DeviceName
]

[Cuyose]

This doesn't work as you get the following error
Error in 'search' command: Unable to parse the search: Comparator 'IN' is missing a term on the right hand side.

[aberkow]

I'm not sure a lookup command in a subsearch makes sense, it doesn't have anything to look at. You can do one of two things here:

baseSearch
Properties.DeviceName=*
| search 
[| inputlookup SPCClientMaster DeviceId
  | rename DeviceId as Properties.DeviceName
]

OR

baseSearch
Properties.DeviceName=*
| lookup SPCClientMaster DeviceId AS Properties.DeviceName OUTPUT fieldThatShowsExistence
| where isnotnull(fieldThatShowsExistence)

I like the first if you just want a straight IN clause, and the second if you want to extract information from the csv. Does that make sense?

[Cuyose]

Neither of these work either. This isn't making any sense whatsoever.

There is a row in my lookup that has a value for DeviceId as "ABC"

I can write the splunk query as Properties.DeviceName=ABC, and it returns a row as expected.

When I add [| lookup SPCClientMaster DeviceId as Properties.DeviceName] I get nothing. Even though I can write the query |inputlookup SPCClientMaster .csv |search DeviceId=ABC, and it returns a row.