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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...