Getting incoming message counts out of Exchange Online
A business partner was wanting to get basic weekly statistics for several O365-hosted email addresses.
She was mostly interested in the number of incoming messages each day, and had a team member manually generating that report each Monday. Built-in O365 reporting didn't (still doesn't?) go to this level of detail.
This thing has been running quietly each week for more than a year now, saving some poor person from burning time at the start of each week, manually counting emails & updating a spreadsheet. Automation is good.
The core piece uses Get-MessageTrace calls against Exchange Online to get message counts for each day. The statistics are generated for the 7 days prior to the day it is run, and are emailed to the same address as statistics are being generated for. I set it up as a scheduled PowerShell runbook on Azure Automation, but it could just as well run as a regular PowerShell scheduled job on some other Windows system.
There's probably a tidily-packaged SaaS product in here, somewhere:
$scriptName = "Exchange-DistributionList-Statistics"
$slackChannel = "#[channel]"
$from = "[company] Email Statistics Bot <no-reply@[company].com>"
$lists = @("questions@[company].com", "team@[company].com")
####
function Main {
Initialize-Helpers -scriptName $scriptName
try {
$session = Initialize-ExchangeOnlineSession
} catch {
Write-Output $_.Exception.Message
Send-SlackNotification $slackChannel $_.Exception.Message
Break
}
$slackMessage = "Azure Automation script '$scriptName' executed without generating results or errors."
try {
foreach ($address in $lists) {
$subject = "Weekly incoming email counts for {0}" -f $address
$body = "<table><tr><th align='left'>Date</th><th align='left'>Count</th></tr>"
Write-Output "Getting incoming email count for: $address"
foreach ($i in 1..7) {
$row = new-object PSObject
$start = (Get-Date).AddDays(-$i) | Get-Date -Hour 0 -Minute 0 -Second 0
$finish = $start.AddDays(1)
Write-Output " Getting message trace count for range: $start - $finish"
$traces = Get-MessageTrace -RecipientAddress $address -StartDate $start -EndDate $finish
$line = "<tr><td>{0}</td><td align='right'>{1}</td></tr>" -f $start.ToString('MM/dd/yyyy'), $traces.Count
$body += $line
}
Send-Email -From $from -To $address -Subject $subject -Body $body -Priority Normal
} # end per-DL loop
} catch {
$slackMessage = "Azure Automation script '$scriptName' failed. Error/s: [{0}]" -f $_.Exception.Message
}
Remove-PSSession $session
Write-Output $slackMessage
Send-SlackNotification $slackChannel $slackMessage
}
####
Main
####
(Note that it uses a few helpers I haven't included for connecting to Exchange Online and sending email/Slack notifications, but those are relatively easily reproduced.)
I still don't really like writing PowerShell (see Madness), but sometimes it is the path of least resistance...