Export (0) Print
Expand All

Getting started with the Office 365 Reporting web service

The Office 365 Reporting web service enables developers to integrate information about email and spam, antivirus activity, and compliance-related events into their custom service reporting applications and web portals. All the reports available in the admin center, inside the downloadable Microsoft Excel spreadsheets, and those accessed through Windows PowerShell cmdlets, are accessible using the Reporting web service. Users accessing the Reporting web service must have administrative rights in the organization. This article introduces the Reporting web service, provides an overview of its features and functionality, and includes a code sample to get you started using the service.

You’ve probably heard that Microsoft Office 365 provides all the tools and capabilities that come with access to a massive server farm for SharePoint Server, Lync, Exchange Server, and always up-to-date clients. The new apps for Office and SharePoint are generating a lot of buzz as well. So if you're a developer new to Office 365, welcome to the proverbial cloud!

What you might not know is that inside the admin center is a set of mail flow, spam, and virus protection reports that show you, among other things, where all that spam is coming from, and who sends the most email in your organization.

If you are an enterprise developer or work in the IT department, you’re going to want these reports in your dashboard. So, let’s get started!

The first step is to be sure your Office 365 account permissions are set correctly, and to test them by using your browser to access the Reporting web service. Next, you’ll build a simple Windows Presentation Foundation (WPF) application in Visual Studio 2012 to issue queries and examine the results. Having a working knowledge of Visual Studio is helpful, but don’t worry if you’re new to it, the code is already written for you.

Once you have your first Reporting web service client up and running, we'll touch on the reports that are available, and tell you where to find information about ODATA2 query options. By the time you’ve read this article, you'll have a basic understanding of the Reporting web service and know enough to start creating your own reports.

To be able to see the reports, you need the right permissions in Office 365. Ask your organization administrator to add you to one of the administrator roles. For now, start at the lowest-level administrator, "Service Admin," because you’re going to store that account's password in a file temporarily. You might also ask your administrator to create a separate administrator account that you can use just for exploring the reporting system. Safer, cleaner, better all around.

In this article, when you see the account name userone@example.onmicrosoft.com, substitute your administrator account.

Once you're up and running with your new administrator account, view the reports from: https://portal.microsoftonline.com/Reports/AllReports.aspx. You'll see more data if your organization is active and has lots of users, but even small organizations can get a surprising amount of spam and malware in their email. The following screen capture shows an example "received email" graph for a small organization.

Figure 1. Sample received mail graph

Sample received mail graph from O365 admin portal

The first thing to know about the Reporting web service is that it's a REST web service. That stands for "Representational State Transfer." If you're not familiar with REST, think of it as using browser-style URLs and HTTP GET requests to retrieve data. When you call a REST web service, typically it returns a bunch of data rather than a Web page or a downloadable file.

To retrieve the "service description document," go to https://reports.office365.com/ecp/reportingwebservice/reporting.svc.

The web site prompts you for your administrator credentials. If you're using Internet Explorer and it displays only a line of text at the top, it's probably asking if you want to display all the content. Choose the Show all content button, and you should get something that looks like this.

Figure 2. Service description document

Browser with O365 reporting.svc in Atom XML

Congratulations, you've made your first properly-authenticated Reporting web service request!

Each of the collection elements in the XML service document indicates an Office 365 report that your administrator account can access. Which ones show up depends on your permissions. At this time (July, 2013), there are only a couple of reports like that, so as you code your dashboard, only a small fraction of the reports will be hidden from your Service Administrator-privileged account.

As you build your reporting system, the best way to know whether the user can access a particular report is to first check the service document returned for that user’s credentials to be sure the report is listed.

Another important part of the service is the MailFilterList "report." You can think of this as returning the pre-defined enum string constants used in many of the report queries. For example, if you're trying to get a report of all spam emails that were sent to the quarantine mailbox, your report query would include an ODATA query option such as $filter=EventType eq 'Malware'. That text string, Malware, is one of many in the list of named EventTypes, and those are returned by the MailFilterList report. We won't go into any more detail about the MailFilterList report, but as you dig deeper, you'll find yourself needing that information frequently. For more information, see MailFilterList report.

At this point, let’s take a step back and see what’s going on behind the scenes. The following high-level conceptual diagram gives you an idea of how the reporting system functions.

Figure 3. Office 365 reporting-related cloud services

Office 365 Reporting web service architecture

The primary sources of reporting information are the Exchange Online service, Microsoft Forefront Protection services, Lync Online service, Exchange Server mailbox servers, and Active Directory Domain Services (AD DS). The various sources deposit their log information to the data mart. On the scale at which Office 365 is operating, thousands of servers can be involved feeding data to the reporting system.

Because of the huge amount of data and replication delays, it can take a while for the data to become available for reports. Typically it’s just a couple hours, but new accounts definitely lag. What that means is that the Reporting web service really isn’t intended for up-to-the-minute system monitoring. It’s more for analysis of historical resource usage.

Once the reporting data appears in the data mart, it can be returned in your requests to the Reporting web service. The Office 365 admin center also gets data from the Reporting web service.

Which brings up an interesting question: just how many ways are there to get the reporting data? There are four distinct ways you can retrieve reports. You already know about the web service and the admin center but you can also download a customizable spreadsheet that gets its data from the web service. Also, if your situation requires the data in Windows PowerShell scripts, there are reporting cmdlets you can call by way of remote-Windows PowerShell. The four ways of retrieving reports are compared in the following table.

Multiple ways to get reports

Ways to get the reports

Ease of use

Customizable

Getting there (using example.com domain)

Office 365 admin center predefined charts and lists

Simple, interactive status-and health-checks.

Low. Interactive filtering by date, triggered transport policy rules, and so on.

https://portal.microsoftonline.com/admin/AllReports.aspx

Downloadable spreadsheet

Detailed, flexible analysis of historical and live service data, for example in Excel-based score-cards.

Medium. Updates may require reapplying customizations, and internal source code not exposed.

http://www.microsoft.com/en-us/download/details.aspx?id=30716

Reporting Windows PowerShell cmdlets

Scripting necessary. Precise data for periodically generated reports in script-based IT maintenance tools.

High. Perfect for script-based analytical tools.

Exchange Online PowerShell

REST Reporting web service

Programming required service-monitoring portals, or for scorecards requiring integration with custom and non-Office 365 services.

Very high. REST Web service provides ODATA2 query filtering and a full programming IDE in Microsoft Visual Studio.

https://reports.office365.com/ ecp/reportingwebservice/ reporting.svc

With four different ways to get the data, how can you be sure that all reports return the same data? The spreadsheet and the admin center both call the Reporting web service, which in turns calls the Windows PowerShell cmdlets. You can also call those cmdlets directly. The only things that access the datamart are the Windows PowerShell cmdlets, which ensures that every report includes the same data, regardless of how you obtain it.

The Reporting web service is handled by the Exchange Server front-end servers in the datacenter, which has a few important effects: one is that when email access is down, the reports are often down. Also, Exchange Server has network-bandwidth protection in the form of response "throttling" that can sometimes affect the Reporting web service. However, you’re unlikely to be affected by that unless you’re really overloading the reporting system.

Now that you know the basics of requesting reports, the next step is to create a Windows Presentation Foundation (WPF) application in Microsoft Visual Studio 2012. The XAML and C# code for this application are provided in the XAML code for O365RWS_Simple MainWindow.xaml and C# code for O365RWS_Simple MainWindows.xaml.cs sections at the end of the article.

Create a simple report request

  1. Open Visual Studio 2012.

  2. Create a new C# WPF Application project named O365RWS_Simple. Be careful to choose the Visual C# project template, because that’s what the code is written in. Also, you need to use that project name; it’s referenced in the code you’ll copy, and things will break if you use a different project name.

    Figure 4. Visual Studio create new WPF project dialog

    Visual Studio 2012 Create O365RWS_Simple project

  3. When the Visual Studio Form Designer opens, go to the MainWindow.xaml file, and replace all the content with the XAML code in the XAML code for O365RWS_Simple MainWindow.xaml section at the end of this article. The form designer should look like the following example. Notice that the report name and options are set for you.

    Figure 5. Visual Studio form designer

    Form design after copying XAML code
  4. Open the MainWindow.xaml.cs file from Solution Explorer and replace all that content with the C# code from the C# code for O365RWS_Simple MainWindows.xaml.cs section at the end of this article.

  5. Locate the following lines of code, and change the values of userName and passWord to that for the admin account you set up earlier.

    
        // IMPORTANT: Change these to your admin user setting
        // Set this to your admin reporting account user name 
        string userName = "userone@example.onmicrosoft.com";
        // Set this to your admin reporting account password
        string passWord = "myPa$$wordI$$ecure!";
    

    Remove the password from the sample when you can. It’s never a good idea to save any password as text in a file.

  6. Save the project (Ctrl+Shift+S).

  7. Go back and verify that your user name and password are correct. This sample contains no error checking or exception handling, so it’s not going to give you useful information when something goes wrong.

  8. Start the application in the debugger (F5). If all goes well, you will see the following.

    Figure 6. Your first application running

    Sample application running with no results
  9. As you can see, the report name is pre-populated with the MailTrafficTop report. This report returns information on the top recipients and senders of email in the organization. The options in this case are asking for the name and number of messages received by the users who’ve received the most email on a daily basis for the previous two weeks.

  10. If you're sure the username and password are correct, click Go. Depending on how large your organization is, and how much email it generates, this can take anywhere from a couple of seconds to a minute or so. Ten seconds is not uncommon, so hang in there. Each report will return a maximum of 2000 entries. Your application will need to determine whether to make additional queries to get the complete report data. When it returns, you should get something that looks like the following.

    Figure 7. First results in the sample

    Sample running with first results

    The document is just raw XML in Atom (RFC 4287) format. Take a look through it. It might look complicated, but it’s all pretty straightforward. This is an introductory article, so we’re not going to dive deeply into that XML. Feel free to copy it to your favorite editor. Visual Studio handles XML files nicely.

  11. After you’ve reviewed the XML, try some of the other reports, or close the application. If you do try the other reports in the following sections, be sure to clear out the options, because they’re not the same for each report. Remember, this sample has no error reporting.

Let’s take a quick look at the code you copied earlier. First, it creates a .NET Framework Uniform Resource Identifier (URI) builder class UriBuilder. That class provides an easy way to construct the sometimes-complicated web address that’s sent to the Reporting web service.


    //
    // Construct the full REST request
    UriBuilder ub = new UriBuilder("https", "reports.office365.com");
    ub.Path = "ecp/reportingwebservice/reporting.svc/" + RwsReportName.Text;
    ub.Query = RwsQuery.Text;
    string fullRestURL = Uri.EscapeUriString(ub.Uri.ToString());
    //

Some important points to remember:

  • Always use HTTPS. The Reporting web service will only accept connections by way of HTTPS.

  • The service endpoint is reports.office365.com. There are some rare scenarios when it might be different, but that’s beyond the scope of this article.

  • The path portion always starts with ecp/reportingwebservice/reporting.svc/, followed by the report name.

  • The query contents, taken from our RwsQuery text box, consist of parameters separated by &. In the final results, there will be a ? between the end of the path and the start of the query, but for us the URIBuilder adds that.

  • Before you pass the string to an HttpRequest object, the query part has to be escaped, where spaces and special characters are turned into the familiar %20; format. That’s what the EscapeUriString method does.

After the URI is constructed, the code create a new HttpWebRequest object, and adds a Credentials object using the userName and passWord. Finally, it makes the call to the Reporting web service.


    //
    // Create the request object and add the credentials from above
    HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(fullRestURL);
    request.Credentials = new NetworkCredential(userName, passWord);
    //
    // [lines removed for clarity]
    //
    // Make the HTTPS request to the reporting server
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();

The rest of the code formats the resulting XML so it will be readable when displayed in the RwsResult textbox. You might not need to do that in your applications.

The Reporting web service uses the organization subscription options, configuration settings, and the user's permissions to control access to the reports, and the options available in them.

Applications that access the Reporting web service need to make two initial requests from the web service. The first is the service description document, reporting.svc. That XML document tells the application which reports the authenticated user is allowed to access. If a report is present in the reporting.svc document, then the user can access it. For more details, see Office 365 Reporting web service reporting.svc document.

The second request the application usually makes retrieves the MailFilterList report, which is crucial to the functioning of custom applications. The MailFilterList report provides several categories of string values that are to be used with the other reports. For example, several of the reports return information about Exchange and data loss prevention (DLP) policies and rules. The mail administrator can customize and create new DLP policies and rules, and provide names appropriate for the organization. In addition to other pre-defined strings, the MailFilterList report provides a list of those policy and rule names. By using the names from the MailFilterList report, the application ensures that the user can select from the current set of configured polices and rules, and that the string comparisons in their queries will also use the right names. For more details, see MailFilterList report.

While the application you created certainly gets the job done, you might want to use Visual Studio to create a service reference that uses the Windows Communication Foundation (WCF). It’s not difficult, but it’s beyond the scope of this article.

So far we’ve talked about the reporting.svc document, mentioned the MailFilterList report, and even gotten data back from the MailTrafficTop report. That barely scratches the surface. The following table lists the available reports.

Office 365 Reports

Report

Description

ConnectionbyClientType* reports
ConnectionbyClientTypeDetail* reports

The number and types of email client-access methods used by the organization's users during the reporting period. For example, Outlook Web Access, Exchange Web services, and so on.

CsActiveUser* reports

The number of active, logged-in Lync Online users during the reporting period.

CsAVConferenceTime* reports
CsP2PAVTime* reports

The amount of time logged-in organization users participated in Lync Online conferences during the reporting period.

CsConference* reports
CsP2PSession* reports

The count of Lync Online conferences and peer-to-peer sessions during the reporting period.

GroupActivity* reports

Office 365 user groups created and deleted, summarized over the indicated time periods.

MailboxActivity* reports
GroupActivity* reports

Office 365 users created and deleted, summarized over the indicated time periods. Active Directory Domain Services (AD DS) replication can sometimes delay this information up to a day.

MailboxUsage report
MailboxUsageDetail report

Summary and detailed statistics about organization user mailboxes.

MailDetail report

Although this is present in the service document, it won’t return any data, so don’t call it.

MailDetailDlpPolicy report

Details about messages that have triggered Data Loss Prevention (DLP) policy rules.

MailDetailMalware report

Malware detected in incoming and outgoing email messages.

MailDetailSpam report

Spam detected in incoming and outgoing email messages.

MailDetailTransportRule report

Exchange Server transport rules that were used in processing individual email messages.

MailFilterList report

The defined string constants used as options when requesting the other reports.

MailTrafficPolicy report

Messages that have triggered DLP policy rules.

MailTraffic report

How much mail is sent to, and received from, domains outside the organization.

MailTrafficSummary reports

A wide selection of reports listing the top users, events, malware detected, and so on.

MailTrafficTop report

Which users have sent and received the most messages.

MessageTrace report
MessageTraceDetail report

A detailed history of how a specific email was transferred through the Office 365 systems, to help diagnose delivery problems.

MxRecordReport report

Returns current settings and status for the mailer-exchange (MX) DNS records.

OutboundConnectorReport report
ServiceDeliveryReport report

Information about the current settings and status of outbound mail (send) connectors defined for the organization.

StaleMailbox report
StaleMailboxDetail report

The details and summary counts of mailboxes that have not been accessed within the indicated time period.

Each of these reports returns numerous fields of data and can be filtered, selected, and ordered as needed. For more information about report details, HTTP header, errors, and code samples, see Office 365 Reporting web service reference.

Let’s return for a moment to the sample and discuss ODATA query options. ODATA2 is an industry standard, with information available at www.odata.org. The sample contains a separate options box pre-populated with this string:

$select=Name,MessageCount&$filter=AggregateBy eq 'Day' and Direction eq 'Inbound'

That string contains two ODATA2 System Query Options, separated by an &: a select and a filter query option. The option name starts with a dollar sign "$", and must be separated from the value by an equals sign "=". These statements are functionally similar to SQL statements, and are oriented toward standard HTTP GET parameter syntax. The Reporting web service supports the following ODATA2 options:

  • $select= a list of comma-separated report columns to include in the report output.

  • $filter= an expression where a true evaluation will include the row in the output. This is a powerful option, but the syntax can be confusing. Read the ODATA2 specifications for this area, as complex reporting will require you to use this option frequently.

  • $top= a positive integer of the maximum number of rows to include. The maximum number of rows the web service will return in one request is 2000.

  • $orderby= specifies one or more columns to sort the results by. The desc keyword specifies descending order.

  • $format= accepts either Atom or JSON, and determines the report output syntax.

The ODATA2 filter options $expand=, $inlinecount=, and $skip= are not supported.

Many reports allow you to precisely define the start and end of the reporting period and the report data granularity. Do this by using the StartDate, EndDate, and AggregateBy fields in the $filter= option. See How to: Specify reporting time spans for details.

In addition to the above system query options, other report settings are handled through HTTP headers:

  • Accept-Language takes a standard culture identifier and, if available, will return localized column names in the report output.

  • X-RWS-Version takes an Office 365 service version identifier. Currently the most-recent version is Office 365 service version 2013-V1. This can allow your applications to specify an older version to maintain compatibility as the service moves forward.

For more information, see How to: Use ODATA2 query options.

Now that you know how to get permissions to the Office 365 reporting system, the four ways the system can be accessed (admin center, spreadsheet, Windows PowerShell cmdlets, and the Reporting web service), have seen how to use simple C# code to access the web service, have had a glimpse at the breadth of available reports, and learned some of the ODATA query options for optimizing your system, check out some of the Office 365 Reporting web service SDK content and code samples for more in-depth information.

The user interface definition XAML code.

<Window x:Class="O365RWS_Simple.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="778">
    <Grid>
        <TextBox x:Name="RwsReportName" HorizontalAlignment="Left" Height="23" 
            Margin="31,36,0,0" TextWrapping="Wrap" VerticalAlignment="Top" 
            Width="112" Text="MailTrafficTop"/>
        <Button Content="Go" HorizontalAlignment="Left" Margin="623,37,0,0" 
            VerticalAlignment="Top" Width="75" Click="Button_Click_1"/>
        <TextBox x:Name="RwsQuery" HorizontalAlignment="Left" Height="23" 
            Margin="162,37,0,0" TextWrapping="Wrap" 
            Text="$select=Name,MessageCount&amp;$filter=AggregateBy eq 'Day' and Direction eq 'Inbound'" 
            VerticalAlignment="Top" Width="432"/>
        <Label Content="Report Name" HorizontalAlignment="Left" Margin="31,10,0,0" 
            VerticalAlignment="Top" Width="112"/>
        <Label Content="options, separated with '&amp;'" HorizontalAlignment="Left" 
            Margin="162,10,0,0" VerticalAlignment="Top" Width="432"/>
        <TextBox x:Name="RwsResults" HorizontalAlignment="Left" Height="208" 
            Margin="42,88,0,0" TextWrapping="Wrap" 
            Text="Results are displayed here...." VerticalAlignment="Top" 
            Width="552" VerticalScrollBarVisibility="Visible"/>
    </Grid>
</Window>

The request-handling code for the application.

/*
 * Office 365 Reporting Web simple example
 * Copyright Microsoft, All rights reserved.
 * 
 * This sample is for demonstration purposes only
 * Not for use in a production environment.
 */
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Xml;

namespace O365RWS_Simple
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            // IMPORTANT: Change these to your admin user setting
            // set this to your admin reporting account user name 
            string userName = "userone@example.onmicrosoft.com";
            // set this to your admin reporting account password
            string passWord = "myPa$$wordI$$ecure!";
            //
            // Construct the full REST request
            UriBuilder ub = new UriBuilder("https", "reports.office365.com");
            ub.Path = "ecp/reportingwebservice/reporting.svc/" + RwsReportName.Text;
            ub.Query = RwsQuery.Text;
            string fullRestURL = Uri.EscapeUriString(ub.Uri.ToString());
            //
            // Create the request object and add the credentials from above
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(fullRestURL);
            request.Credentials = new NetworkCredential(userName, passWord);
            //
            try
            {
                // Make the HTTPS request to the reporting server
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                //
                // Atom data is returned as XML, so let's make it human-readable
                Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
                StreamReader readStream = new StreamReader(response.GetResponseStream(), encode);
                StringBuilder sb = new StringBuilder();
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(readStream.ReadToEnd());
                TextWriter tr = new StringWriter(sb);
                XmlTextWriter wr = new XmlTextWriter(tr);
                wr.Formatting = Formatting.Indented;
                doc.Save(wr);
                string requestDataReturned = sb.ToString();
                wr.Close();
                //
                // and then display the XML results
                RwsResults.Text = requestDataReturned;
            }
            catch {
                RwsResults.Text = "Something went wrong!";
            }
        }
    }
}
Show:
© 2014 Microsoft