Back to blog

Office 365 Mail Traffic Statistics by User

Aug 13, 2013 by Emma Robinson

One of the most requested features for our Office 365 reporting software is for a report showing how many emails each user has sent and received over a given period.

We can use the Get-MessageTrace cmdlet in Exchange Online to search the SMTP message tracking logs in Office 365 and build this report.

The script creates a CSV file in the following format which can be opened in Microsoft Excel.

Date Recipient Inbound Outbound InboundSize OutboundSize
13/08/2013 00:00 2 0 675 0
13/08/2013 00:00 59 3 125,545 10,525
13/08/2013 00:00 35 43 1,445 1,558
13/08/2013 00:00 34 0 12,450 0
13/08/2013 00:00 2 0 1004 0
13/08/2013 00:00 11 0 125,545 0
13/08/2013 00:00 50 0 1221 0
13/08/2013 00:00 6 0 986 0
13/08/2013 00:00 5 2 600 1,525

You could then use Microsoft Office PowerBI to manipulate this data to create all sorts of cool charts and graphs.

Download the Script

To get started, download the PowerShell script from the Microsoft TechNet Scripting Center.

This script only works on the new Wave 15 version of Microsoft Office 365 and accepts two variables on the Command Line:

Office365UserName – Your Office 365 Administrator Username
Office365Password – Your Office 365 Administrator Password
.\Get-DetailedMessageStats.ps1 -Office365Username -Office365Password Password123

How does it work?

The initial parts of the script deal with connecting to Office 365 in the usual manner.

Read moreĀ The Microsoft Office 365 Reporting Handbook

Once we’re connected, we collect all the Primary SMTP Addresses for the tenant using the Get-Recipient cmdlet and build a hash table with each SMTP address as the key.

$Recipients = Get-Recipient -ResultSize Unlimited | select PrimarySMTPAddress
$MailTraffic = @{}
foreach($Recipient in $Recipients)
$MailTraffic[$Recipient.PrimarySMTPAddress.ToLower()] = @{}

It’s now time to parse the message tracking logs to collect all the message tracking data for each message sent and received from the organisation.

To reduce load on the Office 365 servers they break up the results into multiple pages of results, which we deal with using a do..until loop.

$CurrMessages = Get-MessageTrace -PageSize 5000 -Page $Page | Select Received,SenderAddress,RecipientAddress,Size
$Messages += $CurrMessages
until ($CurrMessages -eq $null)

Now that we’ve collected all the recipient and message tracking data we need from Office 365, it’s time for the script to do the magic of collating the information into the hash table.

To achieve this we iterate through each message tracking entry individually.

foreach($Message in $Messages)

We check each message tracking entry to determine if the Sender or Recipient email address is one that matches the Primary SMTP Addresses we collected earlier.

if ($MailTraffic.ContainsKey($Message.SenderAddress))
if ($MailTraffic.ContainsKey($Message.RecipientAddress))

If we find a match in the sender or recipient address, we increment the message count and total size of the messages.

$MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd
if ($MailTraffic[$Message.SenderAddress].ContainsKey($MessageDate))

The remainder of the script formats the inbound and outbound mail traffic data for each user into a usable format and outputs it to CSV.