Splunk Search

Help creating JOIN search

NAVEEN_CTS
Path Finder

I'm trying to compare Field X from Index A with Field Y from Index B. Though the field names are different, they store the same value. IF value matches I need result from field Z from index B

Below is my search:

index = A |fields X |rename X as Y |join Y  [|search index= B] |stats values(Z) by Y

Above search doesn't work.
Is it because of subsearch result limitation?
Help with the correct search to achieve it.

Thanks in advance.

0 Karma
1 Solution

HiroshiSatoh
Champion

Try this!

index=A OR index=B | fields X Y Z index | rename X as Y
 | stats values(Z),dc(index) as count by Y
 | where count=2|table values(Z),Y

View solution in original post

0 Karma

grittonc
Contributor

Try:

index=A OR index=B
| fields X Y Z index
| eval X=coalesce(X, Y)
| stats values(Z) as Z, dc(index) as idx_count by X
| where idx_count>1

as illustrated by

| makeresults| eval X="foo", index="A"
| append 
    [| makeresults| eval Y="foo", Z="bar", index="B"]
| append 
    [| makeresults| eval Y="fo", Z="br", index="B"]
| eval X=coalesce(X, Y)
| stats values(Z) as Z, dc(index) as idx_count by X
| where idx_count>1
0 Karma

HiroshiSatoh
Champion

Try this!

index=A OR index=B | fields X Y Z index | rename X as Y
 | stats values(Z),dc(index) as count by Y
 | where count=2|table values(Z),Y
0 Karma

NAVEEN_CTS
Path Finder

Thank you .This worked as i expected

0 Karma

adonio
Ultra Champion

try this:

index=A OR index=B | fields X Y Z | eval XY =coalesce(X,Y) | stats values(Z) by XY

0 Karma

NAVEEN_CTS
Path Finder

coalesce brings all the values. I want only if value in field X matches with value in Field Y

0 Karma

niketn
Legend

@NAVEEN_CTS coalesce() will not bring all the values. It is just a pre-step to stats where you are creating a new field for correlation between Index A and Index B. Since X exists only in index A and Y exists only in index B when you perform coalesce() it will ensure that values from both index are present as XY.

Then you need to filter results which are present in both the indexes as inner join.

index=A OR index=B 
| fields index X Y Z 
| eval XY =coalesce(X,Y) 
| stats values(Z) as Z values(index) as index by XY
| search index="A" AND index="B"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

NAVEEN_CTS
Path Finder

Ok got it . Thank you @niketnilay

0 Karma

niketn
Legend

@NAVEEN_CTS, I am glad you found the comment useful. Personally, I prefer search with index values so that I can implement left , right, full outer join and other joins using stats command.

Like

| search index="A" OR index="B"

Or

| search index="A" AND index!="B"

Or

| search index!="A" AND index="B"

etc.
Using dc() aggregate you can only perform inner join. However, in your case since that is what you need your accepted answer should do the needful, which is the same as

| search index="A" AND index="B"
Do up-vote the comments if they helped 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...