Scenario: We needed to create a way to identify how long it takes for an attachment to be scanned due to recent complaints. We need to produce a report that shows Message Delivery and Attachment Reinserting post delivery.

We have two SafeAttachment policies; a Block Policy with specific users in the SentTo and a different policy that acts as our default policy configured with Dynamic Delivery for all other users.

Specifically, the two complaints we are receiving are:
– Block Mode – Users are taking a while to receive a message
-Dynamic Delivery Mode – The attachment takes a while to re-insert itself into the message post delivery.

Below is a script that you could build off of

Script: The script below will produce a report that will identify the email messages; When the message was sent, When it was delivered to the mailbox, and when the Attachment was re-inserted into the message. For folks in the Block Policy, they will not have data for post-delivery actions but should experience a delay with the actual message delivery. For folks in Dynamic Delivery, they should have faster delivery times of the email message but have post-delivery events for re-inserting the attachment and the time this takes.

In this script, you will need to edit it to get it to work with your environment. The script is not perfect, nor do I care being the lazy scripter I am. I just need it to work in the environment.

The PreReqs are:

Azure Token – You need a way of obtaining an access token so you make a connection to a Azure Registered App that has permissions to Exchange AND Advanced Hunting Kql. We have a function to pull a token that is referenced in the building of the RestSplat.

Network Message ID – You will need the NetworkMessageID of the message you are targeting.

The Kql commands if you wish to run manually from the Advanced Hunting module instead of the PS script below are as follows:

1. Find the Email Event. Make sure you swap out the Recipient variable. I tend to find the NetworkMessageID from here.

EmailEvents | where RecipientEmailAddress == '$Recipient' and Subject == '$subject' and Timestamp >= $Received_Increment

2. Find the attachment info if you want to see it:
EmailAttachmentInfo | where RecipientEmailAddress == '$recipient' and NetworkMessageId == '$NetworkMessageId' and Timestamp >= $Received_Increment

3. Find the Postdelivery event where it reinserted the attachment:
EmailPostDeliveryEvents | where RecipientEmailAddress == '$Recipient' and NetworkMessageId == '$NetworkMessageID' and ActionTrigger == 'SpecialAction' and ActionResult == 'Success'

PS Script which wraps the Kql commands:

#PreReq - Fill in the Variables
    $AttachmentReport = @()
    $BlockUsers = Get-SafeAttachmentPolicy SafeAttachments_Block  | Get-SafeAttachmentRule |Select -ExpandProperty SentTo
    $UTC_Offset = -5  #I dont feel like messing around with the conversion code for TimeZones.
    $Received_Increment = '7d'  #Values include: 4h, 3d, etc.
    $NetworkMessageIDs = "0577a857-fc84-4731-b2e9-08dd04acd415","39da4538-640a-4dde-9edb-08dd04ad994d","5202afcb-54a3-476e-57c6-08dd04adda06","836b94fa-0d50-452a-8f76-08dd04ae3336","71b68dcc-1e35-4cb4-7d6d-08dd04bb2ef2"



#Loop through each Network MessageID and build a report.

$NetworkMessageIDs | %{
    $NetworkMessageID = $_
    "
Searching Message with NetworkMessageID = $NetworkMessageID
    
    " 
    
#0. Determine RecipientList
    Write-host "Pulling RecipientList" -ForegroundColor Green
    
    #0a. Build the KQL Query Body Command
        
        $q_Anti = "EmailEvents | where NetworkMessageId == '$NetworkMessageID' and Timestamp >= $Received_Increment"
        $body = ConvertTo-Json -InputObject @{ 'Query' = $q_Anti }
    
    #0b. Build API with KQL Body
        $appuri = "https://api.security.microsoft.com/api/advancedhunting/run"  #For Threat Mgmt
        $RestSplat = @{ 
                    URI         = $appuri
                    Headers     = $(get-accesstoken_AdvancedHunting)
                    Method      = 'POST' 
                    ContentType = "application/json" 
                    Body = $body 
                    }
    
    #0c. Execute API
            $response = Invoke-RestMethod @RestSplat
            $response = $response.Results
            
    #0d. Variable for RecipientList
        $RecipientList = $Response.RecipientEmailAddress 

    
If($RecipientList -ne $null){

#1. Loop Recipients
    #Create Report Variable
    

$Recipientlist | %{
    #1a. Declare Recipient Variable for each loop instance
    $Recipient = $_
    "

    Searching $recipient"

    $DeliveryAction = If($Recipient -in $BlockUsers){"Block"}else{"Dynamic_Deliver"}

    #2. KQL - Find Email Delivery Events
            "...Finding Email Delivery Events"
    
        #2a. Build the KQL Query Body Command
        $q_Anti = "EmailEvents | where RecipientEmailAddress == '$Recipient' and NetworkMessageId == '$NetworkMessageID' and Timestamp >= $Received_Increment"
        
        $body = ConvertTo-Json -InputObject @{ 'Query' = $q_Anti }
    
        #2b. Build API with KQL Body
        $appuri = "https://api.security.microsoft.com/api/advancedhunting/run"  #For Threat Mgmt
        $RestSplat = @{ 
                    URI         = $appuri
                    Headers     = $(get-accesstoken_AdvancedHunting)
                    Method      = 'POST' 
                    ContentType = "application/json" 
                    Body = $body 
                    }
    
        #2c. Execute API
            $response = Invoke-RestMethod @RestSplat
            $response = $response.Results
            

        #2d. Create variables based on Message Delivery
            $DeliveryTimeStamp = [datetime]$response.TimeStamp
            $NetworkMessageID = $response.NetworkMessageID
            $InternetMessageID = $response.InternetMessageId
            $Sender = $Response.SenderFromAddress
            $Subject = $Response.Subject
    

    #3. Pull MessageTraceDetail

            "...Message Trace"
            $MessageTrace_Message = Get-MessageTrace -RecipientAddress $recipient  -messageid $InternetMessageID 
            $MessageTrace_Detail = $messagetrace_Message | get-messagetracedetail
            $MessageTrace_Detail_Start = get-date($MessageTrace_Detail | Select -first 1 -ExpandProperty Date)
            $MessageTrace_Detail_End = get-date($MessageTrace_Detail | Where Event -like "Deliver" | Select  -ExpandProperty Date)
            $MessageTrace_DelayInSeconds = $MessageTrace_Detail_End - $MessageTrace_Detail_Start | Select -ExpandProperty TotalSeconds
            

    #3. KQL - Find Attachment Name
            "...Finding Attachment Data"
            #3a. Build the KQL Query Body Command
                $q_Anti = "EmailAttachmentInfo | where RecipientEmailAddress == '$recipient' and NetworkMessageId == '$NetworkMessageId' and Timestamp >= $Received_Increment"
                $body = ConvertTo-Json -InputObject @{ 'Query' = $q_Anti }
            
            #3b. Build API with KQL Body
                $appuri = "https://api.security.microsoft.com/api/advancedhunting/run"  #For Threat Mgmt
                $RestSplat = @{ 
                    URI         = $appuri
                    Headers     = $(get-accesstoken_AdvancedHunting)
                    Method      = 'POST' 
                    ContentType = "application/json" 
                    Body = $body 
                    }
    
            #3c. Execute API
                $response = Invoke-RestMethod @RestSplat
                $response = $response.Results
            
            #3d. Create variables
                $AttachmentFileName = $Response.FileName
            


    #4. KQL - Find POST Email Delivery Events
            "...Finding Post Email Delivery Data"
        #4a. Build the KQL Query Body Command
            $q_Anti = "EmailPostDeliveryEvents | where RecipientEmailAddress == '$Recipient' and NetworkMessageId == '$NetworkMessageID' and ActionTrigger == 'SpecialAction' and ActionResult == 'Success'"
            $body = ConvertTo-Json -InputObject @{ 'Query' = $q_Anti }
 
        #4b. Build API with KQL Body
            $appuri = "https://api.security.microsoft.com/api/advancedhunting/run"  #For Threat Mgmt
            $RestSplat = @{ 
                    URI         = $appuri
                    Headers     = $(get-accesstoken_AdvancedHunting)
                    Method      = 'POST' 
                    ContentType = "application/json" 
                    Body = $body 
                    }
    
        #4c. Execute API
            $response = Invoke-RestMethod @RestSplat
            $response = $response.Results
            
              
        #4d. Create variables based on Message Delivery
            If($response -ne $null){
                $UpdateTimeStamp = [datetime]$response.Timestamp
                $Delay_InSeconds = $UpdateTimeStamp - $DeliveryTimeStamp | Select -ExpandProperty TotalSeconds
                $Delay_InMinutes = "$($UpdateTimeStamp - $DeliveryTimeStamp | Select -ExpandProperty Minutes) Minutes and $($UpdateTimeStamp - $DeliveryTimeStamp | Select -ExpandProperty Seconds) Seconds"
            }else{$UpdateTimeStamp = "N/A";$Delay_InSeconds = "N/A";$Delay_InMinutes="N/A"}

    #5. Create Report
        "...Adding to report"
            $obj = new-object psObject
                    $obj | Add-Member -membertype noteproperty -Name NetworkMessageID -Value $NetworkMessageID
                    $obj | Add-Member -membertype noteproperty -Name Sender -Value $Sender
                    $obj | Add-Member -membertype noteproperty -Name Recipient -Value $Recipient
                    $obj | Add-Member -membertype noteproperty -Name Subject -Value $Subject
                    $obj | Add-Member -membertype noteproperty -Name SafeAttachment_Mode -Value $DeliveryAction
                    $obj | Add-Member -membertype noteproperty -Name Message_Sent -Value $(get-date(get-date($MessageTrace_Detail_Start)).AddHours($UTC_Offset) -Format "MM/dd/yyyy h:mm:ss tt")
                    $obj | Add-Member -membertype noteproperty -Name Message_Delivered -Value $(get-date(get-date($MessageTrace_Detail_End)).AddHours($UTC_Offset) -Format "MM/dd/yyyy h:mm:ss tt")
                    $obj | Add-Member -membertype noteproperty -Name Message_Delivery_InSeconds -Value $MessageTrace_DelayInSeconds
                    #$obj | Add-Member -membertype noteproperty -Name Kql_Delivery -Value $DeliveryTimeStamp
                    $obj | Add-Member -membertype noteproperty -Name SafeAttachment_Attachment_Inserted -Value $UpdateTimeStamp
                    $obj | Add-Member -membertype noteproperty -Name SafeAttachment_Scanning_InSeconds -Value $Delay_InSeconds
                    #$obj | Add-Member -membertype noteproperty -Name Delay_Time $Delay_InMinutes
                    $obj | Add-Member -membertype noteproperty -Name Attachment -Value $AttachmentFileName
            $AttachmentReport += $obj


            $MessageTrace_Detail_Start = $null
            $MessageTrace_Detail_End =  $null
            $MessageTrace_DelayInSeconds = $null
            $Recipient=$Null
            $Sender = $null
            $DeliveryTimeStamp=$Null
            $UpdateTimeStamp=$Null
            $Delay_InMinutes=$Null
            $AttachmentFileName=$Null

}



}Else{Write-host "Data is not available yet" -ForegroundColor Cyan}
}


#View Report
$AttachmentReport | Sort SafeAttachment_Mode|  Out-gridview
$AttachmentReport | Export-csv C:\temp\AttachmentReport.csv