topic Re: Full outer join in Splunk Search

Full outer join

smolcj — Tue, 02 Apr 2013 05:47:04 GMT

How can I meet full outer join requirement in my search??
table a and table b with only one filed in two rows are same i want to display all rows from both the tables:
like , table a with 5 rows
and table b with 6 rows both with a common field thredname
and thread name is same in 2 rows. when i am using join type=outer i am getting 6 rows including the details from table a.
but my need is like i want 9 table where rows with common Threadname will have fully filled fields and other may have their own data.
example

Table a 
 Threadname  adata     
a1          aaa
a2          aa
a3          aa
ab1         aabb     
ab2         ab     



Table b

Threadname  bdata 
ab1         aabbb
ab2         aabbb
b1          bb
b2          bbb
b3          bbb
b4          bbb

Expected Resultant Table
Threadname  adata     bdata  
a1          aaa
a2          aa
a3          aa
ab1         aabb      aabbb
ab2         ab        aabbb
b1                    bb
b2                    bbb
b3                    bbb
b4                    bbb

please help
thank you

Re: Full outer join

kristian_kolb — Tue, 02 Apr 2013 13:25:05 GMT

Hmm, not sure I fully understand, but you might not require a join, but that depends on how you get your data...

If the 'tables' you mention are not tables, but sourcetypes, each row could be seen as an event, with adata and bdata being fields. Then something like;

... | stats first(adata) as adata first(bdata) as bdata by Threadname

would be something that could work.

You should probably give us a few sample events or a better description of your data.

Re: Full outer join

smolcj — Wed, 03 Apr 2013 06:34:18 GMT

Sorry if i confused you. edited the question. kindly please check it !!

Re: Full outer join

kristian_kolb — Mon, 28 Sep 2020 13:39:36 GMT

Thanks for that, but I was more interested in where the data comes from. Are they csv files, or the output of some splunk reporting command, or in separate events (i.e. table_a in one event, and table_b in another)?

Re: Full outer join

smolcj — Wed, 03 Apr 2013 08:44:10 GMT

they are the values extracted from log files . Threadname adata and btada are the fields extracted from 2 different log files. table a from file1 and table b from file 2..
search may look like
index=main sorce=file1 |top Threadname adata | join type=outer Threadname [ search index=main sorce=file2 |top Threadname bdata]
but i am not able to obtain full outer join the output looks like
Threadname adata bdata

a1 aaa
a2 aa
a3 aa
ab1 aabb aabbb
ab2 ab aabbb

Re: Full outer join

kristian_kolb — Wed, 03 Apr 2013 10:00:37 GMT

So.. then you could try:

index=main source=file1 OR source=file2 | stats first(adata) as adata first(bdata) as bdata by Threadname

assuming that there can be only one (or less) adata per Threadname, and only one (or less) bdata per Threadname.

Re: Full outer join

smolcj — Thu, 04 Apr 2013 12:24:51 GMT

mmm i have to use a join for this search because i want to extract same field in two different column names so that i can differentiate it as data from different files so i need a join method that can provide full outer join values.. please help 😞

Re: Full outer join

davebrooking — Thu, 04 Apr 2013 16:15:44 GMT

I think this is needs improving, but it does produce the results you've described.

I created a sourcetype of joinq that has 2 sources adata.txt and bdata.txt

The following search yields the table with 9 rows.

sourcetype=joinq source="adata.txt" | join threadname [search sourcetype=joinq source!="adata.txt"] | append [search sourcetype=joinq source="adata.txt" | join threadname type=outer [search sourcetype=joinq source!="adata.txt"]] | append [search sourcetype=joinq source!="adata.txt" | join threadname type=outer [search sourcetype=joinq source="adata.txt"]] | table threadname adata bdata | dedup threadname | sort threadname

Dave

Re: Full outer join

sideview — Thu, 04 Apr 2013 19:41:40 GMT

The join command's outer join is actually a "left outer" join technically.

To do what you want, you should either

1) don't use join at all, but use append and then stats

index=main source=file1 | stats count by Threadname adata | append [ search index=main source=file2 | stats count by Threadname bdata] | stats values(adata) as adata values(bdata) as bdata by Threadname

2) better, do it in a single search pipeline.

index=main (source=file1 OR source=file2) | stats values(adata) as adata values(bdata) as bdata by Threadname

I understand what you're saying about having to extract fields differently, and do different search language for one side versus the other, but too often people assume there's no way to do such things in a single pipeline. I recommend posting the full details of the searches in a separate question, asking whether you can get the desired end result without using join or append.

Re: Full outer join

jaxjohnny — Fri, 11 Nov 2016 16:17:02 GMT

This worked for me also! Thank you!

Re: Full outer join

jaxjohnny — Fri, 11 Nov 2016 18:29:55 GMT

This worked for me also. Thank you

Re: Full outer join

jaxjohnny — Fri, 11 Nov 2016 18:30:17 GMT

This worked for me . Thank you

Re: Full outer join

mad4wknds — Wed, 31 May 2017 23:18:49 GMT

Worked like a charm

Re: Full outer join

nick405060 — Thu, 13 Feb 2020 22:37:34 GMT

Why are people complacent about outer join functionality? Shouldn't we be questioning the SPL development skills here? Why do they even provide you with the outer option, if it's just going to be a left outer join? I would logically assume that the word "outer" means "full outer" here, especially considering I know there is already a command for an outer left join...

I understand why stats is better than join but why even offer the outer option with join...?

Re: Full outer join

nick405060 — Fri, 14 Feb 2020 22:01:28 GMT

Also, some people looking at this solution may want to be using stats list not stats values here, depending on your outer join use case