How to inner join indexed data with lookup data on multiple fields and return yet another field from the lookup?

[paulito123]

Hey experts!

I'm relatively new to Splunk, so if this is a stupid question, mea culpa. That being said, I have a solid SQL background and I'm in need of a solution for this seemingly easy problem. I have indexed data and I would like to link it to a lookup. The purpose of this lookup is both to limit the final output, as well as enrich the final output with some extra fields. The key for linking the lookup with the indexed data, consists of multiple fields. I was thinking about something like this:

index=ringelingdong sourcetype=ring
| eval testfield=strftime(_time,'%Y%m%d%T')."#".some_id
| where testfield=[| inputlookup lookup_csv.csv
| eval LKPFIELD=strftime(_time,'%Y%m%d%T')."#".my_lkp_id
| where checked!=0
| fields checked, changerequest
| rename checked as ck, changerequest as CR]
| table _time some_id ck CR

This is what I came up with so far, but I'm still missing:

1. how to link the lookup output with the indexed data on multiple keys
2. how to have the lookup restrict the final output, so that only checked!=1 is shown
3. how to add both checked and changerequest fields from the lookup in the result

Many thanks in advance
Paul

[to4kawa]

ANS:

1. | lookup your_lookup host as host host as hostname OUTPUTNEW
   use as option.
   https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup

2. now , you don't display sample logs and lookup detail. no one resolve the issue.

3. same above.

[richgalloway]

Whenever you have a problem with a query containing a subsearch, try running the subsearch by itself with | format added to it. That will tell you want is being returned to the main search. Then you can verify the field names match those expected by the main search and the format is correct.