Splunk Search

Help creating JOIN search

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

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

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

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

Path Finder

Thank you .This worked as i expected

0 Karma

SplunkTrust
SplunkTrust

try this:

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

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

@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!!!"
0 Karma

Path Finder

Ok got it . Thank you @niketnilay

0 Karma

SplunkTrust
SplunkTrust

@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