Index ¦ Archives ¦ Atom

Generating weekly O365-hosted mailbox statistics

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

        $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


(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...

© Jamie Finnigan; opinions my own and not my employers. Built using Pelican. Modified from theme by Giulio Fidente on github.