How to use aggregates to filter data and join the result to another search?

[cjoelly]

Hello,

I have an index with events, where events belong to a transaction (transaction_id). I am interested in transactions which contain exact two event with specific eventtype (type1 and type2).

When I have all the transaction(_ids) which contain those two eventtypes, I want to join to the events again to get those complete transactions, including all their events.

There might be events with other eventtypes as well which need to be retrieved.

This is what I tried:

index="data" 
| stats values(eventtype) as eventtype by transaction_id
| search eventtype="TYPE1" AND eventtype="TYPE2" 
| table transaction_id
| join type=inner transaction_id[search index="data"]
| table *

But this query returns only a fraction of the available matching transactions.

I read some other posts with all kind of approaches, is it really so hard in Splunk to get such tasks done?

[richgalloway]

I wonder if an outer join might have worked, but join is rarely the best answer because it performs poorly.

One other approach is to use a subsearch to find the interesting transaction IDs and then search for those IDs.

index="data" [search index="data" 
  | stats values(eventtype) as eventtype by transaction_id
  | search eventtype="TYPE1" AND eventtype="TYPE2" 
  | fields transaction_id
  | format ]
| table *