Reporting Office 365

Microsoft has never equipped on-premises Exchange with good reporting facilities and administrators have had to create their own reports using whatever facilities were available to them or turn to third-party software products such as the long-lived Promodag reports.

The lack of a consistent API to access information about mailboxes and their contents was an issue for administrators and developers alike. That problem was finally addressed from Exchange 2007 onwards when Microsoft introduced PowerShell and Exchange Web Services (EWS). PowerShell is well suited for the quick retrieval of information about Exchange configuration and settings while EWS is better when more than fifty or so objects need to be processed. Many examples of how to use these PowerShell or EWS to extract and report Exchange data can be found on the web, including how to combine the two to good effect.

Other sources of data like messaging tracking logs have been mined to provide insight into how Exchange is used, but the reports found in the on-premises product have never been particularly useful and sometimes have been just plain weird, as in the insistence in using XML as the preferred output format. It still bemuses me that a product that’s been around for nearly 20 years doesn’t offer out-of-the-box basic reporting like mailbox sizes, mailboxes per database/server, used and unused distribution groups, public folder analysis (size, ownership, access), and so on. If Microsoft had added just a single useful report per version, Exchange 2013 would provide eight to administrators. Yet all it can offer are some ho-hum reports that don’t really help anyone. Crazy!

Office 365 is better in two very important respects. First, the Office 365 Administration Center includes a Reports section that offers a selection of reports covering different aspects of the service. However, the detail exposed in the reports is basic and seldom provides a real insight into how Exchange Online and the other Office 365 applications are used. Microsoft must have realized that the built-in reports are basic and therefore provide the Office 365 reporting web service to provide an interface to the Office 365 Reporting DataMart, a repository that consolidates data feeds from Exchange Online, Exchange Online Protection, Lync Online, Azure Active Directory, and other Office 365 sources.

A range of information about different aspects of Office 365 is extracted from the data sources on a regular basis. You can see the kind of information available in the DataMart by running the Office 365 PowerShell reporting cmdlets. For example, the Get-GroupActivityReport cmdlet lists the numbers of distribution groups created and deleted within a tenant on a day-by-day basis. One thing that’s obvious from an examination of the data returned from Office 365 is that the reporting service focuses on high-level aggregate data and doesn’t dive down into the detail that is often necessary to make good management decisions. In addition, the reporting service returns data with a limited horizon of between 14 and 90 days, so if you want to use the information retrieved from Office 365 for long-term analysis some arrangement usually has to be made to store the data in another repository.

PowerShell is a fantastic way to automate common administrative activities or to process a group of objects at one time, but no one would ever claim that PowerShell is the most efficient or effective way to generate reports. In addition, PowerShell is slow when it comes to processing large collections of objects, so using a script to create a report about mailbox sizes is acceptable when you have 100 mailboxes to process, but not such a great idea when 10,000 mailboxes are involved. Not only would the script be slow, its execution would create a strain on Office 365 resources that Microsoft is likely keen to avoid.

To complement the capabilities of PowerShell, the Office 365 reporting service supports full REST programmatic access to the contents of the Reporting DataMart. The intention is that third-party software developers can incorporate Office 365 reporting data into their products to provide Office 365 customers with a much better view of what’s actually happening inside a tenant than is found in the basic reports. Apart from the obvious goodness inherent in having a common repository to consult to retrieve operating data about Office 365, the combination of Reporting DataMart and APIs avoids people coming up with their own methods to extract information from Office 365.

The Office 365 reporting service provides snapshots of tenant activity. Slow as it might be, sometimes PowerShell is the only way to drill down and retrieve granular information about mailboxes and other objects. Figuring out the gaps in the data available from the Office 365 reporting service, filtering out any inconsistencies that are detected, and analyzing information to generate meaningful reports are how reporting products from independent software vendors distinguish themselves. For instance, the reporting service might tell you that your tenant has so many inactive mailboxes, but what you really need to know is who owns those mailboxes, when they were last accessed, and if they are holding valuable Office 365 licenses.

Examples of Office 365 reporting products include Cogmotive, 365 Command, 4Ward365, and the Governance Toolkit for Office 365. A slightly different take is provided by products that have evolved from the on-premises space, like ENow Software’s MailScape 365, as these implementations cover components such as hybrid connectivity, including Active Directory Federation Services, Azure Active Directory Sync Services, and DirSync, to make sure to provide a complete picture of what can be a pretty complex infrastructure.

Each product has its own strengths and specific focus, but all that use the reporting service follow the same basic principle of using a service account, a non-licensed user belonging to the tenant, to log on and periodically extract data from Office 365 for a tenant and then use that data for analysis and reports. The service account is authorized with the appropriate permissions (such as membership of the Exchange View-Only Organization Management role group) to retrieve information from Office 365 on behalf of a tenant. The data is downloaded from the Office 365 reporting service and stored for ongoing analysis. Although most products will create a service account for you, it’s good to know what happens behind the scenes. This Cogmotive description provides good information for how a service account is created.

The service account is used to periodically download information from the Office 365 reporting service along with whatever other additional data is needed from Office 365 or Azure Active Directory. The retrieved data is usually stored by the software vendor so that it can be used to generate trend reports such as the growth in mailboxes or messaging activity over time. Each product has its own set of reports and its own unique way to interpret and present information.

Naturally, because reporting products are built by independent software vendors, you have to pay extra for them. Pricing details are available from the vendors and range from a couple of hundred dollars per month upward, depending on the number of seats in the tenant. Cogmotive has a very nice offer for small tenants as it doesn’t charge anything for tenants with fewer than 50 seats. According to a company spokesperson, Cogmotive currently supports about 2,000 tenants worldwide amounting to some 1.7 million seats.

Given the attractiveness of the growing number of Office 365 tenants and the availability of the Office 365 reporting web service, it’s likely that more software vendors will enter the market over time. Most vendors allow time-limited trials so it is possible to run products against real tenant data to test just how suitable they are for your needs. The kind of issues to consider when assessing reporting products for Office 365 are:

No single product is the best choice for every situation. The best approach is to spend the time to test the software in your own environment and make the decision based on what you discover there. Take your time!

Follow Tony @12Knocksinna