topic Is this what a full outer join looks like in Splunk ? in Splunk Search

Is this what a full outer join looks like in Splunk ?

asarolkar — Fri, 25 Jan 2013 06:25:46 GMT

Hi,

I am trying to do a full outer join on banklog and creditunionlog such that I can find the timestamp difference between the earliest and last account transaction

(sourcetype="banklog") OR (sourcetype="creditunionlog" )  | rename BankAccountNo As Acct | rename CreditUnionAccountNo As Acct | stats range(_time) by Acct

This works like a charm.

Except when I try to add an account number filter like such, IT DOESNT ACTUALLY WORK (!?!?!??!!)

(sourcetype="banklog") OR (sourcetype="creditunionlog" )  | rename BankAccountNo As Acct | rename CreditUnionAccountNo As Acct | search Acct=1 OR Acct=2 | stats range(_time) by Acct

Anybody else know how a full outerjoin ought to be done in a situation like this ?

Just to state my point, I am getting the results I need, I just need to narrow it down to a few accounts (I have 10000+ accounts per day 😞 )

Re: Is this what a full outer join looks like in Splunk ?

dart — Sat, 26 Jan 2013 11:10:20 GMT

Does this search work:

(sourcetype="banklog" (BankAccountNo=1 OR BankAccountNo=2)) OR (sourcetype="creditunionlog" (CreditUnionAccountNo=1 OR CreditUnionAccountNo=2)) | rename BankAccountNo As Acct | rename CreditUnionAccountNo As Acct | stats range(_time) by Acct

Re: Is this what a full outer join looks like in Splunk ?

rtadams89 — Sat, 26 Jan 2013 17:03:38 GMT

What exactly doesn't work; does it not return any results, does it only return results from one sourcetype...

Re: Is this what a full outer join looks like in Splunk ?

rtadams89 — Sat, 26 Jan 2013 17:05:39 GMT

Try:

(sourcetype="banklog") OR (sourcetype="creditunionlog" )  | eval Acct=coalesce(BankAccountNo,CreditUnionAccountNo) | search Acct=1 OR Acct=2 | stats range(_time) by Acct

Re: Is this what a full outer join looks like in Splunk ?

asarolkar — Sat, 26 Jan 2013 18:50:27 GMT

worked out well !

Re: Is this what a full outer join looks like in Splunk ?

mikebd — Tue, 19 May 2015 13:44:00 GMT

Splunk does not actually support full outer joins with join. join type=outer is equivalent to join type=left. I've been approximating full outer joins with append followed by stats first(...):

sourcetype=st_1 search_field_1=key_1 search_field_2=key_2 | append [ sourcetype=st_2 search_field_1=key_1 search_field_2=key_2 ] | stats first(output_field_1) as output_field_1 first(output_field_2) as output_field_2 by search_field_1 search_field_2

That works fine as long as you are looking for at most one result from either side of the "join" and the two searches will not return conflicting values. Its possible this works for me in this form specifically because in my case each output field will either be null or populated at most once per key set.