Back to blog

Mail Protection Reports in Office 365

25 Jul 2013 by Emma Robinson

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. For more in-depth mail traffic and protection reports, learn more about Quadrotech’s Office 365 reporting software.

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

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

Service Selection

 

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

 

Prerequisites

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

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 Add-In

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

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

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

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

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 BreakDown

 

Spam Detail

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

DLP Breakdown

 

Rules 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

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

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.

Have you used the Excel Mail Protection Reports yourself?