topic Re: Joining values from two indexes and returning just specific values in Splunk Search

Joining values from two indexes and returning just specific values

splunker1981 — Tue, 29 Sep 2020 22:03:45 GMT

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

Re: Joining values from two indexes and returning just specific values

kmaron — Wed, 14 Nov 2018 20:40:06 GMT

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

Re: Joining values from two indexes and returning just specific values

splunker1981 — Wed, 14 Nov 2018 20:52:47 GMT

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

Re: Joining values from two indexes and returning just specific values

kmaron — Wed, 14 Nov 2018 20:55:50 GMT

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

Re: Joining values from two indexes and returning just specific values

splunker1981 — Wed, 14 Nov 2018 21:28:30 GMT

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

Re: Joining values from two indexes and returning just specific values

kmaron — Wed, 14 Nov 2018 21:29:41 GMT

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

Re: Joining values from two indexes and returning just specific values

DavidHourani — Wed, 14 Nov 2018 23:15:16 GMT

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