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

[asarolkar]

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 😞 )

[mikebd]

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.

[rtadams89 · Accepted solution]

Try:

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

[asarolkar]

worked out well !

[rtadams89]

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

[dart]

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