Chat with us, powered by LiveChat

Blog

Back

Office 365 Mail Traffic Statistics by User

13 Aug 2013 by Emma Robinson

Want to save this blog for later? Download it now.
One of the most requested features for our Office 365 reporting application 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 alan@cogmotive.com 2 0 675 0
13/08/2013 00:00 alexd@cogmotive.com 59 3 125,545 10,525
13/08/2013 00:00 allieb@cogmotive.com 35 43 1,445 1,558
13/08/2013 00:00 annew@cogmotive.com 34 0 12,450 0
13/08/2013 00:00 azizh@cogmotive.com 2 0 1004 0
13/08/2013 00:00 belindan@cogmotive.com 11 0 125,545 0
13/08/2013 00:00 bonniek@cogmotive.com 50 0 1221 0
13/08/2013 00:00 davidl@cogmotive.com 6 0 986 0
13/08/2013 00:00 denisd@cogmotive.com 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

How does it work?

The initial parts of the script deal with connecting to Office 365 in the usual manner.
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.

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.

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.

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 we find a match in the sender or recipient address, we increment the message count and total size of the messages.

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.
Instead of running all this PowerShell, why not sign up to a free trial of our Office 365 reporting application and we can do the hard work for you!
If you found this blog post useful, and want to refer to it again, why not download it as a PDF?

whois: Andy White Freelance WordPress Developer London