topic Re: Lookup not working, it is generating a "NOT ()" query for some reason. in Splunk Search

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

Cuyose — Mon, 09 Dec 2019 19:39:17 GMT

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?

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

aberkow — Mon, 09 Dec 2019 19:48:45 GMT

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
]

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?

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

oscar84x — Mon, 09 Dec 2019 19:56:08 GMT

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
]

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

Cuyose — Tue, 10 Dec 2019 00:30:53 GMT

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.

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

Cuyose — Tue, 10 Dec 2019 00:36:56 GMT

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.