Analyzes Azure Communication Services email delivery by joining send and status logs to measure delivery latency and surface clear explanations for SMTP and enhanced SMTP failure codes.

ACS-Email-Delivery-Duration-and-SMTP-Analysis.kql

// -----------------------------------------------
// Description: Analyzes Azure Communication Services email delivery by
//              joining send and status logs to measure delivery latency 
//              and surface clear explanations for SMTP and enhanced SMTP
//              failure codes.
//
// Created by: Blake Drumm (blakedrumm@microsoft.com)
// Date Created: January 21st, 2026
// Enhanced: February 10th, 2026
// Enhancements:
//   - Added sender information (SenderUsername, SenderDomain)
//   - Added message characteristics (Size in MB, AttachmentsCount, recipient counts)
//   - Added detailed failure information (FailureMessage, FailureReason)
//   - Added bounce classification (IsHardBounce, BounceType)
//   - Added recipient domain extraction and mail server tracking
//   - Added InternetMessageId for cross-system correlation
//   - Corrected Size field interpretation (megabytes, not bytes)
// -----------------------------------------------
(ACSEmailSendMailOperational
| project 
    SendTime = TimeGenerated, 
    MessageId = CorrelationId, 
    MessageSize = Size,  // Size is in megabytes (MB)
    AttachmentsCount,
    ToRecipientsCount,
    CcRecipientsCount,
    BccRecipientsCount,
    UniqueRecipientsCount,
    _ResourceId)
| join kind=inner (
    ACSEmailStatusUpdateOperational
    | where isnotempty(RecipientId)
    | where DeliveryStatus in ("Delivered","Failed","Bounced","Suppressed","Quarantined","FilteredSpam")
    | summarize arg_max(TimeGenerated, DeliveryStatus, SmtpStatusCode, EnhancedSmtpStatusCode, 
                        SenderDomain, SenderUsername, FailureMessage, FailureReason, 
                        IsHardBounce, RecipientMailServerHostName, InternetMessageId)
        by MessageId = CorrelationId, RecipientId, _ResourceId
    | project
        MessageId,
        RecipientId,
        _ResourceId,
        FinalStatusTime = TimeGenerated,
        FinalDeliveryStatus = DeliveryStatus,
        SmtpStatusCode = tostring(SmtpStatusCode),
        EnhancedSmtpStatusCode = tostring(EnhancedSmtpStatusCode),
        SenderDomain,
        SenderUsername,
        FailureMessage,
        FailureReason,
        IsHardBounce,
        RecipientMailServerHostName,
        InternetMessageId
) on MessageId, _ResourceId
| where FinalStatusTime >= SendTime
| extend DurationMilliseconds = datetime_diff("millisecond", FinalStatusTime, SendTime)
| extend DurationSeconds = round(todouble(DurationMilliseconds) / 1000.0, 3)
// Extract recipient domain for analysis
| extend RecipientDomain = tolower(extract(@"@(.+)$", 1, RecipientId))
// Categorize message size (Size field is in megabytes)
| extend MessageSizeCategory = case(
    MessageSize < 0.1, "Tiny (<100KB)",
    MessageSize < 1.0, "Small (100KB-1MB)",
    MessageSize < 5.0, "Medium (1-5MB)",
    MessageSize < 10.0, "Large (5-10MB)",
    "Very Large (>10MB)"
)
// Categorize bounce type (handle both "True"/"False" and "true"/"false")
| extend BounceType = case(
    FinalDeliveryStatus == "Delivered", "Not Applicable",
    tolower(IsHardBounce) == "true", "Hard Bounce",
    tolower(IsHardBounce) == "false", "Soft Bounce",
    FinalDeliveryStatus in ("Bounced", "Failed") and isempty(IsHardBounce), "Unknown (not populated)",
    "Unknown"
)
| extend SmtpStatusCode =
    iff(isempty(SmtpStatusCode) and FinalDeliveryStatus == "Delivered", "<not applicable>", SmtpStatusCode)
| extend EnhancedSmtpStatusCode =
    iff(isempty(EnhancedSmtpStatusCode) and FinalDeliveryStatus == "Delivered", "<not applicable>", EnhancedSmtpStatusCode)
| extend SmtpStatusCodeDetails = case(
    SmtpStatusCode == "<not applicable>", "Not applicable (Delivered)",
    SmtpStatusCode == "421", "Service not available; try again later (temporary failure)",
    SmtpStatusCode == "450", "Mailbox unavailable (temporary); retry expected",
    SmtpStatusCode == "451", "Local error in processing (temporary); retry expected",
    SmtpStatusCode == "452", "Insufficient system storage (temporary); retry expected",
    SmtpStatusCode == "500", "Syntax error; command unrecognized",
    SmtpStatusCode == "501", "Syntax error in parameters or arguments",
    SmtpStatusCode == "502", "Command not implemented",
    SmtpStatusCode == "503", "Bad sequence of commands",
    SmtpStatusCode == "504", "Command parameter not implemented",
    SmtpStatusCode == "550", "Mailbox unavailable / policy / access denied (permanent failure)",
    SmtpStatusCode == "551", "User not local; please try forwarding address",
    SmtpStatusCode == "552", "Storage allocation exceeded (mailbox full or message too large)",
    SmtpStatusCode == "553", "Mailbox name not allowed / invalid recipient",
    SmtpStatusCode == "554", "Transaction failed (often policy/content rejection)",
    isempty(SmtpStatusCode), "Unknown / not provided",
    strcat("Unmapped SMTP code: ", SmtpStatusCode)
)
| extend EnhancedSmtpStatusCodeDetails = case(
    EnhancedSmtpStatusCode == "<not applicable>", "Not applicable (Delivered)",
    EnhancedSmtpStatusCode == "5.1.1", "Bad destination mailbox address (recipient doesn't exist)",
    EnhancedSmtpStatusCode == "5.1.0", "Bad destination mailbox address",
    EnhancedSmtpStatusCode == "5.2.2", "Mailbox full",
    EnhancedSmtpStatusCode == "5.2.1", "Mailbox disabled / not accepting mail",
    EnhancedSmtpStatusCode == "5.4.1", "Cannot route / no answer from host / destination routing issue",
    EnhancedSmtpStatusCode == "5.7.1", "Rejected by policy (SPF/DKIM/DMARC/reputation/policy)",
    EnhancedSmtpStatusCode == "4.7.1", "Temporary policy/reputation issue; retry expected",
    EnhancedSmtpStatusCode == "4.4.1", "Connection timed out / temporary network issue; retry expected",
    isempty(EnhancedSmtpStatusCode), "Unknown / not provided",
    strcat("Unmapped enhanced code: ", EnhancedSmtpStatusCode)
)
| project
    MessageId,
    InternetMessageId,
    RecipientId,
    RecipientDomain,
    SenderUsername,
    SenderDomain,
    FinalDeliveryStatus,
    BounceType,
    SendTime,
    FinalStatusTime,
    DurationSeconds,
    DurationMilliseconds,
    MessageSize,
    MessageSizeCategory,
    AttachmentsCount,
    ToRecipientsCount,
    CcRecipientsCount,
    BccRecipientsCount,
    UniqueRecipientsCount,
    SmtpStatusCode,
    SmtpStatusCodeDetails,
    EnhancedSmtpStatusCode,
    EnhancedSmtpStatusCodeDetails,
    FailureReason,
    FailureMessage,
    RecipientMailServerHostName,
    _ResourceId
| order by SendTime desc