Joining values from two indexes and returning just specific values

[splunker1981]

Hello fellow Splunkers

I'm trying to figure out how to join values from 2 indexes and return one field (from one of the indexes) based on a match. For example; we have inventory IDs (e_id) in 1 index and contact details in another - both indexes have a unique ID value that's used to group things together (e_id field)

I'd like to run a search that returns just the contact field/value when the ID matches 1 to 1 with the numerical ID coming back in a search.

For example:

indexA
e_length
e_location
e_id
e_category

indexB
weight
last_shipment
e_id
contact

I'd like to join on the e_id field and return only the contact information from indexB when both id fields match.

something like |table e_length, e_location, e_id, e_category, contact

[DavidHourani]

Hi there,

Give this a shot :
index=A OR index=B
| stats values(contact) as contact values(index) as index by e_id
| search index=A AND index=B

That will give you contacts that have their e_id in both indexes. Add the other values you need after the stats.

Cheers,
David

[kmaron]

you should be able to do that with a join

index=A ... 
| join e_id 
    [ search index=B ...      | fields contact ]
 | table e_length, e_location, e_id, e_category, contact

[splunker1981]

I should've stated that I had this working with join. However, upon further reading it seems like the recommendation is to avoid join AND append when possible. But I couldn't get it working with stats...

[kmaron]

you're not wrong. try this instead.

 index=A OR index=B
 | stats values(e_length) as e_length values(e_location) as e_location values(e_category) as e_category values(contact) as contact by e_id

[splunker1981]

Didn't work, that's what I was trying. The contact field always comes back null....

[kmaron]

that's really odd. the join works but the stats doesn't?