https://community.splunk.com/t5/Splunk-Search/How-to-use-aggregates-to-filter-data-and-join-the-result-to/m-p/691700#M235503 <P>I wonder if an outer join might have worked, but join is rarely the best answer because it performs poorly.</P><P>One other approach is to use a subsearch to find the interesting transaction IDs and then search for those IDs.</P><LI-CODE lang="markup">index="data" [search index="data" | stats values(eventtype) as eventtype by transaction_id | search eventtype="TYPE1" AND eventtype="TYPE2" | fields transaction_id | format ] | table *</LI-CODE><P>&nbsp;</P> Wed, 26 Jun 2024 15:14:28 GMT richgalloway 2024-06-26T15:14:28Z https://community.splunk.com/t5/Splunk-Search/How-to-use-aggregates-to-filter-data-and-join-the-result-to/m-p/691660#M235497 <P>Hello,</P><P>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).</P><P>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.</P><P>There might be events with other eventtypes as well which need to be retrieved.</P><P>This is what I tried:</P><P>&nbsp;</P><LI-CODE lang="markup">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 *</LI-CODE><P>&nbsp;</P><P>But this query returns only a fraction of the available matching transactions.</P><P>I read some other posts with all kind of approaches, is it really so hard in Splunk to get such tasks done?</P> Wed, 26 Jun 2024 13:19:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-use-aggregates-to-filter-data-and-join-the-result-to/m-p/691660#M235497 cjoelly 2024-06-26T13:19:08Z https://community.splunk.com/t5/Splunk-Search/How-to-use-aggregates-to-filter-data-and-join-the-result-to/m-p/691700#M235503 <P>I wonder if an outer join might have worked, but join is rarely the best answer because it performs poorly.</P><P>One other approach is to use a subsearch to find the interesting transaction IDs and then search for those IDs.</P><LI-CODE lang="markup">index="data" [search index="data" | stats values(eventtype) as eventtype by transaction_id | search eventtype="TYPE1" AND eventtype="TYPE2" | fields transaction_id | format ] | table *</LI-CODE><P>&nbsp;</P> Wed, 26 Jun 2024 15:14:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-use-aggregates-to-filter-data-and-join-the-result-to/m-p/691700#M235503 richgalloway 2024-06-26T15:14:28Z