How to Analyze User Email Traffic by Internal or External Destination

Use PowerShell to Analyze Message Trace Data to Find Out Who’s Sending External Email

Updated 8 November 2023

In an August 2023 article, I explain how to use PowerShell to analyze message trace data to report the volume of traffic going to external domains. A follow-up question about that article asked if it was possible to create a report showing the volume of external and internal email sent by each user. It seemed like this should be a straightforward thing to do, and that’s what I explain how to do here.

Message trace data captures the sender and recipient for each message. If the recipient address doesn’t belong to one of the domains registered for the tenant, we can conclude that it’s external email. The previous article explains how to fetch message data for the last ten days from Exchange Online and construct a list of messages suitable for analysis. This script uses exactly the same code to fetch message trace data. The difference is what we do with that data.

Finding Messages for Users

The first thing is to find the set of mailboxes we’re interested in reporting. The example script processed both user and shared mailboxes.

[array]$Mbx = Get-ExoMailbox -RecipientTypeDetails UserMailbox, SharedMailbox -ResultSize Unlimited

Now the script loops through each mailbox and finds if any message trace transactions are available for the mailbox. If true, the script counts internal and external messages and calculates the percentage of the overall total for each category. The script also records to which external domains a mailbox sends messages before capturing the data in a PowerShell list:

ForEach ($User in $Mbx) {
  Write-Host ("Processing email for {0}" -f $User.DisplayName)
  # Get messages sent by the user
  [array]$UserMessages = $Messages| Where-Object {$_.Sender -eq $User.PrimarySmtpAddress}
  If ($UserMessages) {
  # We’ve found some messages to process, so let’s do that
  [int]$ExternalEmail = 0; [int]$InternalEmail = 0; [array]$ExternalDomains = $Null
  ForEach ($M in $UserMessages) {
    $MsgRecipientDomain = $M.RecipientAddress.Split('@')[1]    
        If ($MsgRecipientDomain -in $Domains) {
            $InternalEmail++ 
        } Else {
            $ExternalEmail++
            $ExternalDomains += $MsgRecipientDomain
        }
  }
  $ExternalDomains = $ExternalDomains | Sort-Object -Unique
  $PercentInternal = "N/A"; $PercentExternal = "N/A"
  If ($InternalEmail -gt 0) {
     $PercentInternal = ($InternalEmail/($UserMessages.count)).toString("P") }
  If ($ExternalEmail -gt 0) {
     $PercentExternal = ($ExternalEmail/($UserMessages.count)).toString("P") }

  $ReportLine = [PSCustomObject]@{
    User          = $User.UserPrincipalName
    Name          = $User.DisplayName
    Internal      = $InternalEmail
    "% Internal"  = $PercentInternal
    External      = $ExternalEmail 
    "% External"  = $PercentExternal
    "Ext.Domains" = $ExternalDomains -Join ", "
    “Mbx Type”    = $User.RecipientTypeDetails }
  $MessageReport.Add($ReportLine)
 } # End if user
} # End ForEach mailboxes

An example of the information reported for a mailbox is shown below:

User        : Tony.Redmond@office365itpros.com
Name        : Tony Redmond (User)
Internal    : 115
% Internal  : 64.97%
External    : 62
% External  : 35.03%
Ext.Domains : bermingham.com, codetwo.com, eastman.com, eightwone.com, microsoft.com, nordan.ie, o365maestro.onmicrosoft.com, office365.microsoft.com, ravenswoodtechnology.com, sharepointeurope.com, thecluelessguy.de

Generating an Analysis Report

After generating the report file, the script creates two output files: a CSV file that might be used for further analysis with Excel or another tool (perhaps it might be interesting to visualize the data in Power BI) and a HTML report (Figure 1).

An “N/A” value in either of the field reporting the percentage of email sent internally or externally means that the user sent no messages of this type during the reporting period (last 10 days). If a mailbox doesn’t send any email during that time, the script doesn’t include it in the report.

You can download the full script from GitHub.

It’s PowerShell

The point is that none of what the script does is magic. The message trace data is easily accessible and available for analysis. All you need to do is slice and dice the data as you wish, using PowerShell to sort, refine, or otherwise process the information. Learning how to use PowerShell is such a fundamental part of working with tenant data that it always surprises me when I meet tenant administrators who seem unwilling to master the shell. Oh well, at least it gives me topics to write about!

---

Learn about maximizing the use of Exchange Online data by subscribing to the Office 365 for IT Pros eBook. Use our experience to understand what’s important and how best to protect your tenant.