Splunk Search

Joining values from two indexes and returning just specific values

splunker1981
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

DavidHourani
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

kmaron
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

splunker1981
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

kmaron
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

splunker1981
Path Finder

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

0 Karma

kmaron
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...