This is to analyse the report in Exchange Online known as the "Mail flow map report" this will collate all the inbound and outbound mail by domains into a large CSV file to get to that report from Exchange Admin Centre choose Reports>Mail flow as below:
Then from there we need the "Mail flow map report" option as below:
When you click this option it will take a moment to load and you will be presented with a chart you cannot read, ignore that and click on the "Request Report" option as below:
Then from the new menu give it a Name, choose the date range required and select a recipient then click Request as below:
Now you need to wait for an e-mail to that inbox that will provide a link to download a CSV file, when downloaded you can see that file below, its highlighted in blue:
Convert CSV from Unicode to UTF8
The files you get from Microsoft reporting are Unicode and for proper Powershell processing we need to make this file a UTF8 file, to complete this conversion open the file in Notepad then click File>Save as... and in the "Encoding" choose "UTF8" then click save,
Script to extract individual domains and count messages
The file will have the header values set like this:
"date","tenant_id","category","Connector_Or_ThirdPartyServiceProvider","Sender_Or_Recipient_Domain","message_count"
This means we can use the ones in bold to produce a report that will be human readable, not a CSV file, lets look at the fields now:
Connector_Or_ThirdPartyServiceProvider
Connector_Or_ThirdPartyServiceProvider
This will have the statuses as below (there are more depending on your setup but this script will focus on these two):
"Coming To O365 From Internet" which will be Inbound emails
"Leaving O365 To Internet" which will be Outbound emails
Sender_Or_Recipient_Domain
This will be the domain that is sending or receiving the emails, therefore this needs to be unique and normalised to a single domain per entry with the Inbound or Outbound status from the previous attribute.
"Coming To O365 From Internet" which will be Inbound emails
"Leaving O365 To Internet" which will be Outbound emails
Sender_Or_Recipient_Domain
This will be the domain that is sending or receiving the emails, therefore this needs to be unique and normalised to a single domain per entry with the Inbound or Outbound status from the previous attribute.
message_count
This will be how many messages per day are sent/received from this domain so we need to add these update based on inbound/outbound statuses
This will be how many messages per day are sent/received from this domain so we need to add these update based on inbound/outbound statuses
Script : EXORoutingSummary.ps1
# Set up log file with timestamp
$timestamp = Get-Date -Format "yyyyMMdd-HHmmss"
$logFile = "RoutingMap_Analysis_$timestamp.log"
# Function to write to both console and log file
function Write-Log {
param (
[string]$Message,
[System.ConsoleColor]$ForegroundColor = [System.ConsoleColor]::White
)
# Write to console
Write-Host $Message -ForegroundColor $ForegroundColor
# Write to log file (without color)
Add-Content -Path $logFile -Value $Message
}
# Initialize log file
"RoutingMap Analysis - Generated on $(Get-Date)" | Out-File -FilePath $logFile
# Find all CSV files that start with "RoutingMap_"
$csvFiles = Get-ChildItem -Filter "RoutingMap_*.csv"
Write-Log "Found $($csvFiles.Count) RoutingMap CSV files to process." -ForegroundColor Cyan
# Initialize domain counts by direction
$inboundDomains = @{} # Coming To O365 From Internet
$outboundDomains = @{} # Leaving O365 To Internet
$connectorTypes = @{} # Track unique connector types
# Process each file
foreach ($file in $csvFiles) {
Write-Log "Processing file: $($file.Name)" -ForegroundColor Cyan
# Read all lines from the file with UTF8 encoding
$lines = Get-Content -Path $file.FullName -Encoding UTF8
# Skip the header
for ($i = 1; $i -lt $lines.Count; $i++) {
$line = $lines[$i]
# Skip empty lines
if ([string]::IsNullOrWhiteSpace($line)) { continue }
# Extract direction, connector, domain and count
# Pattern for CSV line: "date","tenant_id","category","connector","domain","count"
if ($line -match '"[^"]*","[^"]*","([^"]*)","([^"]*)","([^"]*)","(\d+)"') {
$direction = $matches[1]
$connector = $matches[2]
$domain = $matches[3]
$count = [int]$matches[4]
# Track connector types
if (-not $connectorTypes.ContainsKey($connector)) {
$connectorTypes[$connector] = 0
}
$connectorTypes[$connector] += $count
# Add to the appropriate domain count based on direction
if ($direction -match "Coming To O365") {
# Inbound traffic
if ($inboundDomains.ContainsKey($domain)) {
$inboundDomains[$domain] += $count
} else {
$inboundDomains[$domain] = $count
}
}
elseif ($direction -match "Leaving O365") {
# Outbound traffic
if ($outboundDomains.ContainsKey($domain)) {
$outboundDomains[$domain] += $count
} else {
$outboundDomains[$domain] = $count
}
}
}
}
}
# Calculate totals
$totalInbound = ($inboundDomains.Values | Measure-Object -Sum).Sum
$totalOutbound = ($outboundDomains.Values | Measure-Object -Sum).Sum
$totalMessages = $totalInbound + $totalOutbound
# Output Connector Types
Write-Log "`nConnector or Third Party Service Provider Summary:" -ForegroundColor Green
Write-Log "------------------------------------------------" -ForegroundColor Green
$sortedConnectors = $connectorTypes.GetEnumerator() | Sort-Object -Property Value -Descending
foreach ($entry in $sortedConnectors) {
Write-Log "$($entry.Key) : $($entry.Value) messages"
}
# Output Inbound Domains
Write-Log "`nINBOUND Domain Summary (Coming To O365 From Internet):" -ForegroundColor Green
Write-Log "-----------------------------------------------------" -ForegroundColor Green
$sortedInbound = $inboundDomains.GetEnumerator() | Sort-Object -Property Value -Descending
$topInbound = $sortedInbound | Select-Object -First 20 # Show top 20 domains
foreach ($entry in $topInbound) {
Write-Log "$($entry.Key) : $($entry.Value)"
}
if ($inboundDomains.Count -gt 20) {
$remainingInbound = $inboundDomains.Count - 20
Write-Log "... and $remainingInbound more domains" -ForegroundColor Yellow
}
# Output Outbound Domains
Write-Log "`nOUTBOUND Domain Summary (Leaving O365 To Internet):" -ForegroundColor Green
Write-Log "---------------------------------------------------" -ForegroundColor Green
$sortedOutbound = $outboundDomains.GetEnumerator() | Sort-Object -Property Value -Descending
$topOutbound = $sortedOutbound | Select-Object -First 20 # Show top 20 domains
foreach ($entry in $topOutbound) {
Write-Log "$($entry.Key) : $($entry.Value)"
}
if ($outboundDomains.Count -gt 20) {
$remainingOutbound = $outboundDomains.Count - 20
Write-Log "... and $remainingOutbound more domains" -ForegroundColor Yellow
}
# Output overall statistics
Write-Log "`nOVERALL STATISTICS:" -ForegroundColor Cyan
Write-Log "Total inbound domains: $($inboundDomains.Count)" -ForegroundColor Yellow
Write-Log "Total outbound domains: $($outboundDomains.Count)" -ForegroundColor Yellow
Write-Log "Total inbound messages: $totalInbound" -ForegroundColor Yellow
Write-Log "Total outbound messages: $totalOutbound" -ForegroundColor Yellow
Write-Log "Total messages: $totalMessages" -ForegroundColor Yellow
# Ask if user wants full report
Write-Host "`nSummary log has been written to: $logFile" -ForegroundColor Green
$answer = Read-Host "Would you like to generate a full report with ALL domains? (Y/N)"
if ($answer -eq "Y" -or $answer -eq "y") {
$fullReportFile = "RoutingMap_FullReport_$timestamp.log"
# Create full report
"RoutingMap Full Domain Report - Generated on $(Get-Date)" | Out-File -FilePath $fullReportFile
# Add Connector Types
"Connector or Third Party Service Provider Summary:" | Add-Content -Path $fullReportFile
"------------------------------------------------" | Add-Content -Path $fullReportFile
foreach ($entry in $sortedConnectors) {
"$($entry.Key) : $($entry.Value) messages" | Add-Content -Path $fullReportFile
}
# Add ALL Inbound Domains
"`nINBOUND Domain Summary (Coming To O365 From Internet):" | Add-Content -Path $fullReportFile
"-----------------------------------------------------" | Add-Content -Path $fullReportFile
foreach ($entry in $sortedInbound) {
"$($entry.Key) : $($entry.Value)" | Add-Content -Path $fullReportFile
}
# Add ALL Outbound Domains
"`nOUTBOUND Domain Summary (Leaving O365 To Internet):" | Add-Content -Path $fullReportFile
"---------------------------------------------------" | Add-Content -Path $fullReportFile
foreach ($entry in $sortedOutbound) {
"$($entry.Key) : $($entry.Value)" | Add-Content -Path $fullReportFile
}
# Add overall statistics
"`nOVERALL STATISTICS:" | Add-Content -Path $fullReportFile
"Total inbound domains: $($inboundDomains.Count)" | Add-Content -Path $fullReportFile
"Total outbound domains: $($outboundDomains.Count)" | Add-Content -Path $fullReportFile
"Total inbound messages: $totalInbound" | Add-Content -Path $fullReportFile
"Total outbound messages: $totalOutbound" | Add-Content -Path $fullReportFile
"Total messages: $totalMessages" | Add-Content -Path $fullReportFile
Write-Host "Full report with ALL domains has been written to: $fullReportFile" -ForegroundColor Green
}
Write-Host "Analysis complete!" -ForegroundColor Green
You will also notice that the list of domains end with "... and xxx more domains" if you would like the full report say "Yes" to the prompt in the script for the full report, when completed the path will be shown in the console window.
