topic Help creating JOIN search in Splunk Search

Help creating JOIN search

NAVEEN_CTS — Tue, 23 Jul 2019 14:38:35 GMT

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.

Re: Help creating JOIN search

adonio — Tue, 23 Jul 2019 15:00:54 GMT

try this:

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

Re: Help creating JOIN search

NAVEEN_CTS — Tue, 23 Jul 2019 15:10:35 GMT

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

Re: Help creating JOIN search

HiroshiSatoh — Tue, 23 Jul 2019 15:32:01 GMT

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

Re: Help creating JOIN search

niketn — Tue, 23 Jul 2019 15:36:27 GMT

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

Re: Help creating JOIN search

grittonc — Tue, 23 Jul 2019 15:40:49 GMT

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

Re: Help creating JOIN search

NAVEEN_CTS — Tue, 23 Jul 2019 16:10:52 GMT

Thank you .This worked as i expected

Re: Help creating JOIN search

NAVEEN_CTS — Tue, 23 Jul 2019 16:12:22 GMT

Ok got it . Thank you @niketnilay

Re: Help creating JOIN search

niketn — Tue, 23 Jul 2019 17:57:55 GMT

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

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 🙂