Splunk Search

Joining values from two indexes and returning just specific values

Path Finder

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

Tags (3)
0 Karma

Super Champion

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

0 Karma

Motivator

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
0 Karma

Path Finder

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

0 Karma

Motivator

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
0 Karma

Path Finder

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

0 Karma

Motivator

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

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!