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 .
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*
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*
@aayushisplunk1 If your problem is resolved, please accept an answer to help future readers.
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)
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""
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.
You forgot to put the table headers (field names) for your Table A & Table B.
@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.
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.
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
.
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.
Have you verified the two parts of the join
work as expected?
Hello richgalloway,
Yes, the two parts of the join are working as expected.