Can I write this search with stats instead of left join?

[vjzone]

Hi All, I have a join query that works perfectly fine for my use case, but I was trying to see if I can write this using the stats or a more performative command.

I'm trying to pull a report for transactions with their status. These are from a single source file. A log entry is created when the event is started, and another log is created when the event completes. There are also possibilities of the start event repeating itself since it did not complete the first time.

Here's my query with a join

index=en source="/merchant.log" host="merc.com" event="start"
| dedup src_key
| join type=outer joinkey
 [search source="/merchant.log" host="merc.com" event='complete" success="true" 
| table joinkey, resultcode] 
| table src_key, area, resultcode, _time, txn_amt

The closest I got using stats was with https://community.splunk.com/t5/Splunk-Search/Alternative-method-to-using-Join-command/m-p/532978#M1....

[yuanliu]

Something like

index=en source="/merchant.log" host="merc.com" (event="start" OR (event="complete" success="true"))
| eval _time = if(event=="start", _time, null())
| stats values(src_key) as src_key, values(area) as area, values(resultcode) as resultcode, values(_time) as _time, values(txn_amt) as txn_amt by joinkey

[isoutamo]

Here is explanation between joins in splunk. https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...