Archive

How to lookup from results of another lookup

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

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

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

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

Splunk Employee
Splunk Employee

have you tried using a subsearch?

0 Karma

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!