How to lookup from results of another lookup

[vrtweb]

I am trying to pipe the results of one lookup to another to essentially join the data. In the search below I am trying to use user_id found in apicode and lookup the corresponding email in the user lookup.

Parameters:  | rex field=_raw "apicode=>(?<apicode>.*)" | lookup apicode_table apicode output user_id | lookup user_table user_id output email

I get the following error.

Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table

I assume this is because user_id is not available from the db yet. How can I lookup from a second table based on the first table?

[vrtweb · Accepted solution]

Thanks all for the comments. I finally got the second lookup to work. I was missing the parameter.

Parameters:  | rex field=_raw "apicode=>(?<apicode>.*)" | lookup apicode_table apicode output user_id | lookup user_table id as user_id output email

[somesoni2]

It may be a long shot. Try something like this

Parameters: | rex field=_raw "apicode=>(?.*)" | lookup apicode_table apicode output user_id | eval user_id=coalesce(user_id,"")| lookup user_table user_id output email

[mkinsley_splunk]

have you tried using a subsearch?

[strive]

In my tests i have seen the error comes when column (OR CSV field) is missing but not when value is null.

[strive]

Does your user_table contains both user_id and email columns?
If value is not present then lookup command simply returns empty, it wont throw an error

[vrtweb]

The name fields are correct and capital OUTPUT doesn't seem to make a difference. The first lookup works fine lower or upper.

[somesoni2]

Verify the name of fields in the lookup tables. Also, the keyword output should in caps (OUTPUT, I think that's the problem)