Issues in creating the join

[aayushisplunk1]

Hello,
I am facing issues joining the two table A & B given below:

Table A:

A | email@xxx
1 | abcd@xxxx
2 | efgh@xxxx
3 | xyza@xxxx
4 | mno@xxxx
5 | pqrs@xxxx

Table B:

B | name
3 | ijk
5 | lmn

Required output table: Table C:

B | email@xxx | name
3 | xyza@xxxx | ijk
5 | pqrs@xxxx | lmn

My query:

mysearch
| table A, email@xxxx
| join
[ search mysearch2
| table B, name]
| table B, email@xxx, name | where B=A

Is this the correct way to implement it ? if not, then please help me out with it .

[aayushisplunk1 · Accepted solution]

The problem was not with the join but with the Table A which was coming as the output of single event that's why the join was not successful. To split the entries row wise, i have to apply the query as given below.

index="index1" host="host1" source="source1" sourcetype="json"
| head 1
| table data.users{}.number, data.users{}.email
| rename data.users{}.number as number, data.users{}.email as "Email"
*| eval temp=mvzip(number,Email,"#")
| mvexpand temp
| makemv delim="#" temp
| eval number = mvindex ( temp, 0)
| eval Email = mvindex ( temp, 1)
| fields - temp**
| join number
type=outer
[ search index="index2" host="host2" source="source2" sourcetype="csv" "somekeyword"
| table name, number1
| rename number1 as number ]
| table number, email, name*

[richgalloway]

@aayushisplunk1 If your problem is resolved, please accept an answer to help future readers.

[woodcock]

Try this:

|makeresults
| eval raw="A=1,email=abcd@xxxx A=2,email=efgh@xxxx A=3,email=xyza@xxxx A=4,email=mno@xxxx A=5,email=pqrs@xxxx B=3,name=ijk B=5,name=lmn"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval B = coalesce(B, A)
| table  B email name
| stats list(*) AS * BY B
| where isnotnull(name)

[aayushisplunk1]

Hello,

Thank you for responding !

Since my input is in tabular format, i am not sure if i can use this.
If i wish to use this procedure then how will i convert my tabular data to the format given below :

" eval raw="A=1,email=abcd@xxxx A=2,email=efgh@xxxx A=3,email=xyza@xxxx A=4,email=mno@xxxx A=5,email=pqrs@xxxx B=3,name=ijk B=5,name=lmn""

[woodcock]

No, no, no. Just use lines 10-13 of my answer after your ((index=iA AND sourcetype=sA) OR (index=iB AND sourcetype=sB)) line. The other stuff is just what I used to test. Read the COMMENT AS stuff.

[jnudell_2]

You forgot to put the table headers (field names) for your Table A & Table B.

[renjith_nair]

@aayushisplunk1,

Try using stats instead of join

Assuming that mysearch is index="index1" and mysearch2 is index="index2"

(index="index1" OR index="index2") | eval common_field=if (index=="index1",A,B)
|stats values(email) as email, values(name)  as name by common_field

If it's not working for you , please share the search terms you are currently using.

[aayushisplunk1]

Thank you for the quick response!

mysearch & mysearch2 are detailed out below:

index="index1" host="host1" source="source1" sourcetype="json"
| head 1
| spath output=object path=data.users{}
| foreach object
[ spath output=number path=data.users{}.number
| spath output=email path=data.users{}.email]
| table number, email
| join number
type=outer
[ search index="index2" host="host2" source="source2" sourcetype="csv" "somekeyword"
| table name, number1
| rename number1 as number ]
| table number, email, name

Please let me know the possible resolution.

[richgalloway]

First, the two tables need names for their fields. For Table A I'll call them 'number' and 'email'; for Table B I'll call them 'number' and 'name'. Using these names, here is a sample query using join. Notice the where clause is absent. That's because join does it implicitly.

mysearch
| fields number, email
| join number [ search mysearch2 | fields number, name] 
| table number, email, name

The join command is very inefficient. If you share mysearch and mysearch2 we may be able to help create a search that combines them without using join.

[aayushisplunk1]

Thank you for the quick response!

I tried your resolution but it didn't work for me.

Please have a look at the exact query:

index="index1" host="host1" source="source1" sourcetype="json"
| head 1
| spath output=object path=data.users{}
| foreach object
[ spath output=number path=data.users{}.number
| spath output=email path=data.users{}.email]
| table number, email
| join number
type=outer
[ search index="index2" host="host2" source="source2" sourcetype="csv" "somekeyword"
| table name, number1
| rename number1 as number ]
| table number, email, name

Here i have tried using outer join also. Please let me know the possible resolution.

[richgalloway]

Have you verified the two parts of the join work as expected?

[aayushisplunk1]

Hello richgalloway,

Yes, the two parts of the join are working as expected.