Error while joining two lookups based on condition

[rohankin]

Hi ,
I want to join the two lookups based on one field that I am creating conditionally in the second lookup.
So, Lookup_A has "name"
In Lookup_B, I need to created "name" based on condition which is listed in the eval statement below. But when I run this, I get error around Search Statement.
(Error in 'search' command: Unable to parse the search: Comparator '=' is missing a term on the left hand side.)

Also, is there a way to do the wildcard match on the name field ?

Thanks !

|inputlookup Lookup_A where status=Live
|join type=left name [search= [|inputlookup Lookup_B|eval name = case
(
( (isnull(ModelName)) AND (isnotnull(hostname)) ),hostname,
( (isnull(hostname)) AND (isnotnull(ModelName)) ),ModelName,
( (isnotnull(hostname)) AND (isnotnull(ModelName)) ),hostname
)
]]

[gcusello]

Hi @rohankin,
did you tried something like this?

|inputlookup Lookup_B
|eval name = case(
     ( (isnull(ModelName)) AND (isnotnull(hostname)) ),hostname,
     ( (isnull(hostname)) AND (isnotnull(ModelName)) ),ModelName,
     ( (isnotnull(hostname)) AND (isnotnull(ModelName)) ),hostname
     )
| lookup lookup_A name OUTPUT status <and other fields if needed>
| search status=Live
| table name <other fields>

Ciao.
Giuseppe

[richgalloway]

You don't need two subsearches.

|inputlookup Lookup_A where status=Live
|join type=left name [|inputlookup Lookup_B|eval name = case
(
( (isnull(ModelName)) AND (isnotnull(hostname)) ),hostname,
( (isnull(hostname)) AND (isnotnull(ModelName)) ),ModelName,
( (isnotnull(hostname)) AND (isnotnull(ModelName)) ),hostname
)
]

You also don't need join.

|inputlookup Lookup_B|eval name = case
(
( (isnull(ModelName)) AND (isnotnull(hostname)) ),hostname,
( (isnull(hostname)) AND (isnotnull(ModelName)) ),ModelName,
( (isnotnull(hostname)) AND (isnotnull(ModelName)) ),hostname
)
|inputlookup append=true Lookup_A where status=Live
| stats values(*) as * by name

[aberkow]

A couple of thoughts:

Your first command is an |inputlookup within the subsearch, so you don't need the search call. Removing it removed the error for me.

You can simplify your case statement to be a simple if clause, since if hostname is not null, you're taking hostname, else you're taking modelname, at least currently. You should also add a catch all if neither are populated.

You can update a lookup's definition by going to settings -> lookups -> lookup definitions -> click the lookup -> advanced options -> Match type -> WILDCARD(name)

I think this should work, but if you can avoid the join you should try to, since they aren't very performant:

{code}
| inputlookup Lookup_A where status=Live
| join type=left name
[| inputlookup Lookup_B
| eval name = case(isnotnull(hostname), hostname, isnotnull(ModelName), ModelName, 1==1, "Neither Model nor host names are
provided - this case was not previously addressed")
]
{code}

Hope this helps!