Splunk Search

Issues in creating the join

aayushisplunk1
Path Finder

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 .

Tags (2)
0 Karma
1 Solution

aayushisplunk1
Path Finder

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*

View solution in original post

0 Karma

aayushisplunk1
Path Finder

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*

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

woodcock
Esteemed Legend

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)
0 Karma

aayushisplunk1
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

jnudell_2
Builder

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

0 Karma

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

aayushisplunk1
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

aayushisplunk1
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

aayushisplunk1
Path Finder

Hello richgalloway,

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

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...

Customer success is front and center at .conf25

Hi Splunkers, If you are not able to be at .conf25 in person, you can still learn about all the latest news ...

.conf25 Global Broadcast: Don’t Miss a Moment

Hello Splunkers, .conf25 is only a click away.  Not able to make it to .conf25 in person? No worries, you can ...