Mail protection reports using the Excel reporting workbook


Applies to: Exchange Online, Exchange Online Protection

You can use the Microsoft Excel 2013 reporting workbook to view both summary and detailed protection reports from Microsoft Office 365. This topic explains how to initially populate the workbook with data, how to use summary and detail worksheets, and how to refresh data in the workbook.

What do you need to know before you begin?

  • You need to be assigned permissions before you can perform this procedure or procedures. To see what permissions you need, see the "Reporting" entry in the Feature permissions in Exchange Online topic or the "Reporting" entry in the Feature permissions in EOP topic.

  • This topic assumes that you have already downloaded and installed the Excel reporting workbook using the instructions on this download page.

  • If your service subscription changes after installing the workbook, you should uninstall and then reinstall the workbook with the proper parameters.

  • For information about when data is available in the workbook and for how long, see the “Reporting and message trace data availability and latency” section in Reporting and message trace in Exchange Online Protection.

What do you want to do?

Populate the workbook with data

When you open the workbook for the first time, it contains no data. Use the following procedure to populate the workbook with data.

  1. Open the workbook and click the Query button.

  2. In the resulting Sign in to Office 365 window, enter valid credentials for your cloud-based organization, and click Login.

  3. In the resulting Query window, select the time interval and then click OK in order to start the process of connecting to your organization and retrieving your messaging data.

  4. A Progress dialog box opens that contains a progress indicator. When the task is complete, click OK.

Use the workbook

There are two types of worksheets in the workbook: summary worksheets and detail worksheets.

The summary worksheets contain charts, tables, and Excel data elements called slicers. You use the slicers to filter the data shown in the charts and tables. You can click on the individual slicers or use standard selection controls like <CTRL>+click and <SHIFT>+click to select multiple slicers. For example, on the Traffic worksheet, the available slicers are the dates and the traffic type values that are described in the Legend at the top of the worksheet. By default, all traffic type values are selected in the Traffic Type table at the top of the worksheet. If you want to include only malware and spam data in the charts and the table, hold down the <CTRL> key and click Malware and Spam. You can also select specific dates or a date range by clicking the date values in the Date table.

The detail worksheets contain query controls and tables. You can go directly to a detail worksheet, fill out the query information and click Refresh to see the results. If you start on a summary worksheet, and you expand a row in the table and double-click an entry, you may be taken to the corresponding detail worksheet. Details are only available on certain data elements. If details are available, the value in the cell will be underlined.

Update the data in the workbook

To change the query that's used to populate the data in the workbook, click the Query button on any summary worksheet. If you change the query parameters it affects all of the worksheets in the workbook.

You can also click the Refresh button that's available on any worksheet to update the data in the workbook without having to modify the query. For example, suppose on May 1 you ran a query by selecting the last 30 days as the time interval. On June 1, to update the workbook with the May data, simply click the Refresh button on any worksheet.