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

Service Selection

You will also need to install several prerequisites including Excel 2013 and the .Net Framework 4.5.

Prerequisites

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

Excel App

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.

Refreshing Data

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.

Dashboard

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.

Spam BreakDown

Spam Detail

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

DLP Breakdown

Rules BreakDown

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.

Filters

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:

• Date/Time
• Sender/Recipients Address
• Domain
• Subject
• 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.

Conclusion

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.