Splunk Search

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

cjoelly
Loves-to-Learn

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?

Labels (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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 *

 

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...