Calling Office 365 Reporting Web Service from custom solutions

Office 365 provides a set of reports for administrators to get details of mailbox usage, SharePoint site storage, active Lync users etc. Administrators can access these report from the reports section admin portal or by directly visiting https://portal.microsoftonline.com/Reports/AllReports.aspx . Enterprises would like to integrate these reports into their existing reporting solutions and business dashboards. For this purpose Office 365 provides a REST based Reporting Web Service. This blog explains the process of building a sample solution using the Office 365 Reporting Web Service.

Accessing the Office 365 Reporting Web Service

Office 365 reporting web service can be accessed from https://reports.office365.com/ecp/reportingwebservice/reporting.svc.  The request will prompt for administrator credentials and once you provide them you will see the service description of the reporting web service as shown below

Each collection element in the service description XML represents a report that can be called to get details. To view a particular report we just have to add the report name to the reporting webservice URL. For example to access the MailBoxUsage report we will have to visit the URL https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailboxUsageDetail . The full set of reports available via the Reporting Web Service can be viewed from this link

ODATA query options can be used to select columns, filter and sort results in the report.  To select only the User Name and Current Mail Box size columns in the MailBoxUsage report we can add the ODATA query $select=UserName,CurrentMailboxSize to the reporting web service URL. Our URL would then look like https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailboxUsageDetail?$select=UserName,CurrentMailboxSize

Let us create a sample windows form application in Visual studio that will call the MailboxUSage report in Office 365 and display the top 10 mailbox user names based on the current mailbox size.

1.       Open visual studio and create a windows form project

2.       In form1 add a button and a multi-line text box

3.       Add the below code to the click event of the button

string userName = "admin user name ";

           string passWord = "admin user password";

            UriBuilder ub = new UriBuilder("https", "reports.office365.com");

            ub.Path = "ecp/reportingwebservice/reporting.svc/MailboxUsageDetail/";

            ub.Query = "$select=UserName,CurrentMailboxSize&$top=10&$orderby=CurrentMailboxSize";

            string fullRestURL = Uri.EscapeUriString(ub.Uri.ToString());

            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(fullRestURL);

            request.Credentials = new NetworkCredential(userName, passWord);

            //

            try

            {

                HttpWebResponse response = (HttpWebResponse)request.GetResponse();

               

                StreamReader readStream = new StreamReader(response.GetResponseStream(), System.Text.Encoding.GetEncoding("utf-8"));

                StringBuilder sb = new StringBuilder();

 

                XmlDocument xdoc = new XmlDocument();

                xdoc.LoadXml(readStream.ReadToEnd());

                XmlNodeList nodes = xdoc.GetElementsByTagName("m:properties");

                for (int i = 0; i < nodes.Count; i++)

                {

                    sb.Append("User Name :" + nodes[i].ChildNodes[0].InnerText + "\t" + "Mailbox Size :" + nodes[i].ChildNodes[1].InnerText + Environment.NewLine);

                }

                txtResult.Text = sb.ToString();

            }

            catch (Exception exp)

            {

                txtResult.Text = exp.Message;

            }

The code calls the Office 365 Reporting Web Service with the report name and query and also passes the appropriate admin credentials. The resulting XML is iterated and user name and mailbox size is displayed in the text box. The results will be displayed as below

A client library that simplifies the access to Office 365 reporting service is available from nugget.org. This client library reduces the code complexity in calling reporting web service. More details on installing and using this library is available in this MSDN article.