In Exchange Online we have an upcoming message rate limit to be imposed in 2025: 2000 external recipients per 24-hour rolling window, max. The following query can be used to identify any instances where that limit was exceeded in the last 30 days.

let acceptedDomains = _getEXOAcceptedDomains();
EmailEvents
| where TimeGenerated >= ago(30d)
| where EmailDirection =~ 'Outbound' and SenderFromDomain in~ (acceptedDomains)
| where InternetMessageId endswith "PROD.OUTLOOK.COM>";
| summarize MsgsToExtRcptLast24H = count() by SenderFromAddress, bin(Timestamp, 24h) 
| where MsgsToExtRcptLast24H >= 2000

But to use this query in a Defender XDR Custom Detection Rule, we need to include a valid ReportId from one or more pieces of evidence (EmailEvents in this case). As you can see, the output from summarize only includes sender, date (start of the bin date range), and count (MsgsToExtRcptLast24h), but no ReportId. I’ve tried supplying in the summarize command a placeholder ReportId, literally like this:

| summarize MsgsToExtRcptLast24H = count() by SenderFromAddress, bin(Timestamp, 24h), ReportId = 'placeholder'

But this doesn’t work, instead we need a real EmailEvent’s ReportId. I would like to instead first capture the offenders like this:

let acceptedDomains = _getEXOAcceptedDomains();
let emails = EmailEvents
| where TimeGenerated >= ago(30d)
| where EmailDirection =~ 'Outbound' and SenderFromDomain in~ (acceptedDomains)
| where InternetMessageId endswith "PROD.OUTLOOK.COM>";
let offences = emails
| summarize MsgsToExtRcptLast24H = count() by SenderFromAddress, bin(Timestamp, 24h), 
| where MsgsToExtRcptLast24H >= 2000;

…and then somehow I would like to iterate through “offences” and then query EmailEvents to get just one EmailEvent that falls within the 24-hour window and from the offending sender, so we can use its ReportId, along with the sender address, the bin’d timestamp and count.

I think I need to capture offences as a dynamic array or table using mv-expand and then somehow perform the subquery. In PowerShell it would be like this:

foreach ($o in $offences) {
    $sampleReportId = ($emails =  | where { $_.SenderFromAddress -eq $o.SenderFromAddress -and $_.Timestamp >= $o.Timestamp -and $_.Timestamp <= (Get-Date $o.Timestamp).AddHours(24) } | sort Timestamp | select -First 1).ReportId
    #output final output for CDR:
    $o | select SenderFromAddress, Timestamp, MsgsToExtRcptLast24H, @{n='ReportId';e={$sampleReportId}}
} 

So you see, I just want to grab one sample EmailEvent’s ReportId, and then include that in the output from the summary command.

I have just scrambled and scrambled and cannot seem to make anything work. The other similar questions always get answered by somebody starting with making/building their own dynamic table from scratch. We need to use the findings from the first query to get our starting data.

From here: How to use result of first KQL query in the second query to filter results?

I think has_any() with tabular expression might be my best bet: https://learn.microsoft.com/en-us/kusto/query/has-any-operator?view=microsoft-fabric#tabular-expression, as I need to query based on matching both the sender AND the 24-hour timerange.