Help creating JOIN search

[NAVEEN_CTS]

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.

[grittonc]

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

[HiroshiSatoh · Accepted solution]

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

[NAVEEN_CTS]

Thank you .This worked as i expected

[adonio]

try this:

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

[NAVEEN_CTS]

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

[niketn]

@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"

[NAVEEN_CTS]

Ok got it . Thank you @niketnilay

[niketn]

@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 🙂