If we look back a few years ago, we would have our servers/services running Exchange Server 2010, BPOS, FOPE (Forefront Online Protection for Exchange) and each product would have their own interface. They did not interact well among each other but FOPE had a killer feature, which was the report capabilities that were built into the product.
Nowadays, it is a different story where all current players: Office365, Exchange Online Protection (EOP) and Exchange Server 2013 share the same user experience and sometimes, if you don’t look closely, you may not know what you are managing. We do have some report support from the main page of Office365/EOP (Figure 01) but we can get more details and better management using the Mail Protection Reports which is available for free.
Microsoft released a workbook based in Excel 2013 and this workbook will provided summary and detailed information about several key areas of the message and protection for either EOP or Office 365. All information is retrieved from web services in the cloud in the first moment, after which we can apply filters to narrow down the results.
Forefront Online Protection for Exchange (FOPE) is being phased out and if you still have this solution, then you may be interested in this MSExchange.org article about the transition process.
Installing the Mail Protection Reports…
The installation process is a breeze and we must have installed Excel 2013, Microsoft .Net Framework 4.5 on the laptop where we are going to run the reports.
The tool is called Mail Protection Reports for Office 365 and it should be installed on a workstation, after all, we will run it for a few days in a month and because of the requirements, the tool is not suitable for servers. These are the steps required to install the tool:
- Download the tool from the Microsoft Download Site.
- In the initial page (Figure 02), like any other welcome installation page, just click Next.
- In the End-User License Agreement. Read the license agreement and if you agree with it, then select I accept the terms in the License Agreement and click Next.
- In the Service Selection page (Figure 03). We can install the tool for Microsoft Exchange Online that is for customers using Office365 (hybrid or even if all mailboxes are in the cloud) or Microsoft Exchange Online Protection for customers that use only the EOP service to protect their mail flow.
- In the Prerequisites Required page (Figure 04). A list of all prerequisites required will be listed, and if there is anything missing, we can click on the link and install the missing requirement. Click Next.
- In the page Ready to install Mail protection Reports for Office 365, Click on Install.
- In the final page of the wizard, we should have a message informing that the installation was completed successfully. Click on Finish.
The result of the process is a new icon on the desktop (Figure 05), and by clicking on it we will access the workbook from where we will retrieve all the information and have our reporting capabilities.
By default, the workbook and its required auxiliary files are installed on C:\ProgramData\Microsoft\MailProtectionReports folder.
Using the Mail Protection Reporting workbook
When we open the workbook for the first time the view will be similar to Figure 06, where we have several tabs: Traffic, Spam, Malware, Rules, Data Lost Prevention, Spam Mail Detail, Malware Mail Detail, Rule Mail Detail and DLP Mail Detail. As soon as we open it, the workbook is blank, there is no data there. In order to start getting the reports we need to click on Query.
You need to hit the Query button at least once per session, and that will fill out all the data required for the first 5 (five) tabs of the spreadsheet. All tabs that have detail in their name will require an extra step to retrieve the information.
After hitting Query, a pop-up asking for the credentials will be displayed, we need to fill out using the user id and password (the user id field must be using the nam[email protected] format). A second dialog box will appear (Figure 07). After authenticating, we can define the interval and that is a new feature where we can retrieve data up to 60 days. Click on OK.
The process will take some time depending on the amount of information that you have. A summary of the entire operation will be displayed in the dialog box (Figure 08), click OK.
Now we are talking (figure 09), the default charts are being displayed. In the first tab we highlighted two items: the item number 1 in red in the picture is where the administrator can see the current time interval of the data being displayed; the second item shows the filters where we can click on the required information and the charts will be changing dynamically.
Bear in mind to use Ctrl key on your keyboard to select more than one item from the filter area. The selection is not just for Traffic Type, we can do the same for Date and that gives us great flexibility to come up with a nice report.
In the same page if we go a little bit down (Figure 10) we can see the Top Recipients and Top Senders and at the bottom we have all days which we can expand and get the numbers based on the hour of the day. For the image below, we removed some dates from the report using the filter on the right side to get only data from April and by doing that all the information being displayed is using only the selected dates.
All other tabs will have similar layouts with charts on the left, filters on the right side and at the bottom a table. The following table has a summary of what is going to be displayed on each tab of the workbook.
|Spam||Top Spam Recipients
Top Malware Recipients
|Data Loss Prevention||DLP Policy Matches
DLP Rule Matches
DLP Action Applied
|DLP Match Type
DLP Policy Matches
Using the Detail tabs
All tabs containing details on their name can be accessed directly by defining the start date and end date and then we can use refresh.
There is another way, when looking at the numbers on the tables of the tabs: Spam, Rules, Malware and Data Loss Protection, some numbers will have links (Figure 11) and by clicking on them, we will be redirected automatically to the given tab.
In the spam mail detail tab for example (Figure 12), we can see a list of all entries for the period defined which is pretty cool. However, you may have noticed that each column has a filter, and we can use that to filter any column, for example we can easily track all Inbound messages for a specific user with a couple of clicks.
In this Tutorial, we went through the installation process of the Mail Protection Reports. We also checked how to retrieve information and filter to create personalized reports in the workbook.