topic Re: JOIN on some of the columns in Splunk Search

JOIN on some of the columns

michael_vi — Thu, 06 Jan 2022 11:30:22 GMT

I have a table (that is a spitted URL) in the following format:

field1	field2	field3	field4	field5	field6
aaaaa	11111	qqqqq	aaaaaa	tttttt	yyyyyy
aaaaa	11111	cccccc	rrrrrrr
bbbbb	22222	rrrrrrrrr	iiiiiiiiiii	vvvvvv
ccccc	22222	wwwww	ttttttttt
ddddd	33333	444444	5555555

Ans the other table has only some of the columns:

field1	field2	field3	field4	Name	Description
ccccc	22222			Mickey	Mouse
aaaaa	11111			Pinky	Brain
ddddd	33333	444444		ZZ	Top

I need that the rows in the second table with be marched to first one, when the values of the second table have only a "base" values. This is what I expect to get:

field1	field2	field3	field4	field5	field6	Name	Description
aaaaa	11111	qqqqq	aaaaaa	tttttt	yyyyyy	Pinky	Brain
aaaaa	11111	cccccc	rrrrrrr			Pinky	Brain
aaaaa	11111	qqqqq	aaaaaa	tttttt	yyyyyy	Pinky	Brain
aaaaa	11111	cccccc	rrrrrrr			Pinky	Brain
bbbbb	22222	rrrrrrrrr	iiiiiiiiiii	vvvvvv		ZZ	Top
ccccc	22222	wwwww	ttttttttt			Mickey	Mouse
ddddd	33333	444444	5555555			ZZ	Top

| join type=left field1 field2... []

It make sense that when I do a left join it looks for a corresponding values in all fields, and if it's not there... I have no results

How can I solve it?
Thanks

Re: JOIN on some of the columns

ITWhisperer — Thu, 06 Jan 2022 12:10:22 GMT

It is not clear how you get your expected results.

Why are the first two lines repeated?

Why is bbbbb ZZ Top?

Re: JOIN on some of the columns

michael_vi — Sun, 09 Jan 2022 06:49:31 GMT

@ITWhisperer

They are repeated, since it answers the JOIN condition.

aaaaa

11111

Can be applied to:

field1	field2	field3	field4	field5	field6
aaaaa	11111	qqqqq	aaaaaa	tttttt	yyyyyy
aaaaa	11111	cccccc	rrrrrrr

The only way, I see that it can be done, is by each time to make a another join... something like:

| join type=left max=0 field1 [...]
| join type=left max=0 field1 field2 [...]
| join type=left max=0 field1 field2 field3 [...]

I was looking for more elegant solution

Re: JOIN on some of the columns

ITWhisperer — Sun, 09 Jan 2022 09:59:41 GMT

This still doesn't explain why bbbbb "joins" with ZZ Top?

Re: JOIN on some of the columns

michael_vi — Sun, 09 Jan 2022 18:27:37 GMT

@ITWhisperer

You're right. My mistake. Should be NULL

Re: JOIN on some of the columns

ITWhisperer — Sun, 09 Jan 2022 20:47:32 GMT

search 1 | append [ search 2 ] | stats values(*) as * by field1 field2 | mvexpand field3 | mvexpand field4