Splunk Search

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

Cuyose
Builder

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?

Tags (2)
0 Karma

oscar84x
Contributor

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
]
0 Karma

Cuyose
Builder

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.

0 Karma

aberkow
Builder

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?

0 Karma

Cuyose
Builder

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.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.