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.
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
@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, 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.
| search index="A" OR index="B"
| search index="A" AND index!="B"
| search index!="A" AND index="B"
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 🙂