Splunk Search
Highlighted

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

Builder

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

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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
0 Karma
Highlighted

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

Contributor

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

0 Karma
Highlighted

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

Contributor

Try:

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

View solution in original post

0 Karma
Highlighted

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

Builder

worked out well !

0 Karma
Highlighted

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

Path Finder

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.