topic Re: Emulating FULL OUTER JOIN in Splunk Search

Emulating FULL OUTER JOIN

andreafebbo — Wed, 02 Nov 2016 14:07:28 GMT

Hi,
I have to tables:

ID name
1..A
2..B

ID Error
1..bla1
1..bla2

so Id like a table which is like the following

ID name error
1..A........bla1
1..A........bla2
2..B........(empty)

In SQL this is called FULL OUTER JOIN but i cannot find a way to replicate it since the parameter

type=outer

in the command join means LEFT JOIN in the reality.

I found some questions that try to resolve the issue with the search command but in my case it does not work.

Thank you

Re: Emulating FULL OUTER JOIN

cmerriman — Wed, 02 Nov 2016 14:30:27 GMT

can you provide syntax with how you get current tables? You might be able to use appendpipe.

Re: Emulating FULL OUTER JOIN

andreafebbo — Wed, 02 Nov 2016 14:37:17 GMT

index=MYINDEX source=MYSOURCE 
                  | eval Day = strftime(_time,"%F")
                  | eval DateTime = strftime(_time,"%Y-%m-%d %H:%M:%S")
                  | stats Latest(LogType) as status Latest(Result) as Result Latest(DateTime) as When by PackageName  ,ExecutionInstanceGUID, Day
                  | sort When
                  | streamstats  count as "Execution Nr" by PackageName, Day
                  | sort - When
                  | table When, PackageName, "Execution Nr",  status, Result, ExecutionInstanceGUID
                  | eval AlertLevel = case(Result=="OK",1,Result=="WARNING",2,Result=="KO",3)
                  | rangemap field=AlertLevel low=1-1 elevated=2-2 severe=3-3 default=guarded
                  | fields - AlertLevel
                  | join type=outer ExecutionInstanceGUID[
                         search index=MYINDEX source=MYSOURCE
                         | rename ExplodedPackages{}.Error AS Error, ExplodedPackages{}.Package AS Package, ExplodedPackages{}.TimeStamp AS TimeStamp
                         | eval x=mvzip(TimeStamp,mvzip(Package,Error))
                         | mvexpand x
                         | eval y=mvzip(ExecutionInstanceGUID,x)
                         | mvexpand y
                         | eval z=split(y,",") 
                         | eval ExecutionInstanceGUID=mvindex(z,0)
                         | eval TimeStamp=mvindex(z,1)
                         | eval Package=mvindex(z,2)
                         | eval Error=mvindex(z,3)
                         | table ExecutionInstanceGUID,TimeStamp, Package, Error
                  ]

With this syntax I get just one error for each row in the first table

Re: Emulating FULL OUTER JOIN

cmerriman — Wed, 02 Nov 2016 15:15:23 GMT

try something like this, maybe:

UPDATED

 index=MYINDEX source=MYSOURCE 
                   | eval Day = strftime(_time,"%F")
                   | eval DateTime = strftime(_time,"%Y-%m-%d %H:%M:%S")
                   | stats Latest(LogType) as status Latest(Result) as Result Latest(DateTime) as When by PackageName  ,ExecutionInstanceGUID, Day
                   | sort When
                   | streamstats  count as "Execution Nr" by PackageName, Day
                   | sort - When
                   | table When, PackageName, "Execution Nr",  status, Result, ExecutionInstanceGUID
                   | eval AlertLevel = case(Result=="OK",1,Result=="WARNING",2,Result=="KO",3)
                   | rangemap field=AlertLevel low=1-1 elevated=2-2 severe=3-3 default=guarded
                   | fields - AlertLevel
|appendpipe [stats count by ExecutionInstanceGUID | join type=outer ExecutionInstanceGUID [
                   search index=MYINDEX source=MYSOURCE
                   | rename ExplodedPackages{}.Error AS Error, ExplodedPackages{}.Package AS Package, ExplodedPackages{}.TimeStamp AS TimeStamp
                   | eval x=mvzip(TimeStamp,mvzip(Package,Error))
                   | mvexpand x
                   | eval y=mvzip(ExecutionInstanceGUID,x)
                   | mvexpand y
                   | eval z=split(y,",") 
                   | eval ExecutionInstanceGUID=mvindex(z,0)
                   | eval TimeStamp=mvindex(z,1)
                   | eval Package=mvindex(z,2)
                   | eval Error=mvindex(z,3)
                   | table ExecutionInstanceGUID,TimeStamp, Package, Error]]
|stats values(PackageName) as PackageName by ExecutionInstanceGUID Error

Re: Emulating FULL OUTER JOIN

andreafebbo — Thu, 03 Nov 2016 09:55:03 GMT

THe result of this query is the following:

1..A
2..B
1.......bla1
1.......bla2

and not:

1..A..bla1
1..A..bla2
2..B........

Re: Emulating FULL OUTER JOIN

davebrooking — Thu, 03 Nov 2016 12:19:12 GMT

I understand your requirement is more involved, but given the basic datasets from above the following search produces similar results to that shown:

index=MYINDEX source=MYSOURCE | stats values(error) as error values(name) as name  by id | eval error=if(isnull(error),"NULL",error)  | mvexpand error | table id name error

Dave

Re: Emulating FULL OUTER JOIN

cmerriman — Thu, 03 Nov 2016 12:27:59 GMT

try my updated syntax. I used a stats command at the bottom to bring back the values of the PackageName by the ID and Error, so it should join the 1s and bla1/bla2s.

Re: Emulating FULL OUTER JOIN

andreafebbo — Fri, 04 Nov 2016 10:21:00 GMT

Nothing: now it shows just 3 columns:
ExecutionInstanceGUID
Error
PackageName (empty column).

In the example, what i call the column name(A, B) is one column but in the reality are many columns(as you can see in the query). I need all of them

Re: Emulating FULL OUTER JOIN

cmerriman — Fri, 04 Nov 2016 12:10:09 GMT

you might need to play with the stats command at the bottom, join by any of the column names that might be in common, or do a latest instead of values.

|stats values(*) as * by ExecutionInstanceGUID

Re: Emulating FULL OUTER JOIN

tdiestel — Wed, 26 Jul 2017 19:35:16 GMT

This seems like you just want to do a Left join. so

index=A|table ID Name | join type=left max=0 ID [search index=B |table ID Error]

Re: Emulating FULL OUTER JOIN

arlington — Wed, 05 Feb 2020 09:11:28 GMT

Thanks, man, that's indeed the great answer, which helped me a lot. But what is the max=0 indicates here? Event If there is not any data in the index B, still it shows 1, without max=0 it calculates all of them as 1.

Re: Emulating FULL OUTER JOIN

sandrosov_splun — Fri, 04 Mar 2022 10:02:43 GMT

The author needed a left join as mentioned before, but I see demand for this task is still high.

I spent a little bit of time on this. So, try the following.

| inputlookup tableA | eval key=localfield1
| append [|inputlookup tableB | eval key=localfield2]
| selfjoin max=0 keepsingle=yes key

Re: Emulating FULL OUTER JOIN

carbdb — Fri, 02 Aug 2024 12:04:23 GMT

absolutely great, because it is also expandable to more than two datasets to be compared. here's what i done for a "triff":

| append [ | makeresults format=csv data="PSP_Element
F-330751
F-330755
F-330758
"