How to replace join by stats to merge SPL based on common field?

[innoce]

Hi,

My datasets are much larger but these represent the crux of my hurdle...

Sourcetype= transaction
fields= transaction_id, user, 

sourcetype= connection
fields=x_transaction_id, user, action

Now I need to build a SPL which detects huge data sent to ext.domains in single event, for which I have all the required details in transaction sourcetype itself, but the allowed or block action is not there, those are specified under connection sourcetype..

Just need to merge the action details to the transaction sourcetype

Tried with join, results are inappropriate. 

Can this be done more efficiently with stats?

[bowesmana]

Definitely stats will be more efficient and for large data sets will work, whereas you're likely to hit limits with join - and it's a bad option generally in Splunk

(index=bla sourcetype=transaction) OR (index=bla2 sourcetype=connection)
``` Either do this to get the common txid ```
| eval txid=coalesce(transaction_id, x_transaction_id)
``` OR you could do this instead, whichever is more appropriate ```
| eval txid=if(sourcetype="transaction", transaction_id, x_transaction_id)
| stats values(action) as action by txid user

``` You can also add in 'values(*) as *' or list(*) as * depending on what
    fields you want in your output ```