Help with "Where" command in a transaction

[msarro]

Hey everyone. Right now I am working with a transaction. I currently have two sources which I am trying to correlate based on a single field. The issue is that the single field's value occasionally repeats in one of the sources. So, after the transaction takes place, there are a number of transactions only showing events from one of the sources. We honestly don't care about those transactions - we're only looking for transactions where both source types are present. So is there a way to say something like this:

sourcetype=AS_CDR OR sourcetype=MSP-PROD|dedup _raw|eval CID1=coalesce(AS_Call_ID,MSP_Call_ID)|transaction fields=CID1 maxspan=1m keepevicted=true|where eventcount>1 AND contains(AS_CDR) AND contains(MSP-PROD)

We could do this with a join, but when we're correlating 4 different sources for millions of records, its simply too expensive. Any help would be greatly appreciated.

[Ayn]

I can think of two ways to achieve this.

1. Single out the recurring values and dedup them out. For instance if the duplicate events happen within the same minute and your search is covering the last hours, you could use dedup date_hour date_minute date_second. This will obviously not be failsafe though, so might not be the best option.

2. Instead of where, use search to find transactions that have both values as sourcetypes.

   sourcetype=AS_CDR OR sourcetype=MSP-PROD
   |dedup _raw
   |eval CID1=coalesce(AS_Call_ID,MSP_Call_ID)
   |transaction fields=CID1 maxspan=1m keepevicted=true
   |search eventcount>1 AND sourcetype=AS_CDR AND sourcetype=MSP-PROD