topic Re: Issues in creating the join in Splunk Search

Issues in creating the join

aayushisplunk1 — Fri, 28 Jun 2019 10:31:36 GMT

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

Table A:

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:

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

Re: Issues in creating the join

richgalloway — Fri, 28 Jun 2019 12:42:59 GMT

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.

Re: Issues in creating the join

renjith_nair — Fri, 28 Jun 2019 12:49:24 GMT

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

Re: Issues in creating the join

aayushisplunk1 — Fri, 28 Jun 2019 15:12:13 GMT

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.

Re: Issues in creating the join

aayushisplunk1 — Fri, 28 Jun 2019 15:16:45 GMT

Thank you for the quick response!

mysearch & mysearch2 are detailed out below:

Please let me know the possible resolution.

Re: Issues in creating the join

jnudell_2 — Fri, 28 Jun 2019 15:19:13 GMT

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

Re: Issues in creating the join

richgalloway — Sat, 29 Jun 2019 12:40:50 GMT

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

Re: Issues in creating the join

woodcock — Fri, 05 Jul 2019 23:00:43 GMT

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)

Re: Issues in creating the join

aayushisplunk1 — Mon, 08 Jul 2019 04:53:49 GMT

Hello richgalloway,

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

Re: Issues in creating the join

aayushisplunk1 — Mon, 08 Jul 2019 05:12:34 GMT

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

Re: Issues in creating the join

woodcock — Mon, 08 Jul 2019 14:36:43 GMT

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.

Re: Issues in creating the join

aayushisplunk1 — Tue, 09 Jul 2019 03:44:07 GMT

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*

Re: Issues in creating the join

richgalloway — Tue, 09 Jul 2019 12:07:24 GMT

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