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 ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...