Mail Protection Reports in Office 365
Microsoft have made an Excel Add-In Available that connects to Exchange Online and allows you to generate reports and charts about your company’s mail traffic statistics.
The reports that are provided by the Excel Add-In are quite detailed although very cumbersome to use. The data and charts provided are similar to our own Office 365 Reports.
The reports also only work for customers using Exchange Online Protection or the new version of Office 365.
Installing the Excel Add-In
Before you can start using the reports, you will need to download the Excel Add-In from the Microsoft Downloads site:
Download Microsoft Office 365 Excel Plugin for Exchange Online Reporting
You will be asked which version to download – either the 32bit or 64bit version. Make sure you pick the right one for your current computer.
Once downloaded, fire up the installer.
Install Add-In 1
It’s your basic Windows Installer and involves clicking Next a few times.
You will need to answer one question about your type of Exchange Online Protection service. For Office 365 users you will need to choose the first option: Exchange Online Service
You will also need to install several prerequisites including Excel 2013 and the .Net Framework 4.5.
Once it’s installed, you will see an icon on your desktop called Mail Protection Reports for Office 365. Double click it to get started.
The first time you run the Add-In Excel will prompt you to install the customization. Obviously you need to click Install here.
Open Add In Excel First Time
Using the Mail Protection Reports
After you’ve opened up Excel using the Mail Protection Reports for Office 365 icon on your desktop you will need to connect to Exchange Online.
Clicking the grey Refresh button in the Traffic worksheet will pop up a username and password box. Type in the credentials for a user that has access to the Exchange Online Reports WebService (usually one of your administrator accounts).
Once it is successfully connected to the web service it will begin downloading the data it requires to generate reports for the past 14 days of mail flow.
If you want to generate reports over a longer or more specific time period, you can use the Query button on the main sheet. The maximum time period you can request data for is 60 days.
Query Date Range
Eventually, you’ll have a worksheet populated with graphs and data.
The main Traffic worksheet shows reports on how much mail has been sent and received as well as top senders and recipients.
You can also view a breakdown of the messages sent and received per hour at the bottom of the sheet.
Mail Traffic Breakdown by Hour
Filtered Messages Reports
The Spam Worksheet shows a similar view, focuses only on messages stopped by the Office 365 Content Filter, IP Connection Filter or Envelope headers.
The Malware tab is almost the same again, but relates specifically to mail filtered out by the Malware and Virus filters.
Another tab is available for messages filtered by either Transport Rules or Data Loss Protection rules (DLP).
Each of the reports offers the ability to only show specific types of information by filtering specific conditions. Simply click an option to either include or exclude that data set from the graphs.
Spam and Malware Detailed Information
The remaining tabs in the WorkSheet allow you to do a deep dive into each of the filtered messages to find out exactly why it was caught. It gives you information such as:
- Sender/Recipients Address
- Message Size
This is extremely cool and detailed – we will be looking at ways to incorporate something like this into our own Office 365 Reporting application in the future.
Although it is cumbersome and painful to use, this is a great little tool for doing basic analysis on your Office 365 mail traffic. It’s very similar to our own Mail Traffic and Malware reports.
Have you used the Excel Mail Protection Reports yourself? Let us know what you think in the comments below