Getting Data In

How to lookup from results of another lookup

vrtweb
Explorer

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?

1 Solution

vrtweb
Explorer

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

View solution in original post

vrtweb
Explorer

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
Revered Legend

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

0 Karma

mkinsley_splunk
Splunk Employee
Splunk Employee

have you tried using a subsearch?

0 Karma

strive
Influencer

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

0 Karma

strive
Influencer

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

0 Karma

vrtweb
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...