Accessing the Office 365 Reporting Service using C#

Introduction

As with other cloud offerings from Microsoft, there is so much data and meta data being collected there is often a need for reporting on that information.  Within Office 365 administration portal you can see the various reports that are offered, however it may be necessary to pull that data into your own application or reporting tool.  Thankfully you can access that underlying data through Powershell commandlets or by using the Office 365 Reporting service.  This post will focus on a few simple example methods I used to explore the Office 365 Reporting service using C#.

Prerequisites

• Visual Studio. Visual Studio Community 2015
• Office 365 Administrator Account

Visual Studio

I have uploaded the full source to github so I will not be posting the full example methods in this post, if you would like to download the full source you can get it here.  Each method has local variables for your Office 365 administrator account username and password.  One of the example methods I would like to highlight is the run_all_reports method.  This was probably the most helpful method when exploring the different reports.  This allowed me to quickly loop through each report available and dump the first set of results to an xml file.  I could then inspect each report result to see what data was available and which report data I needed to pull into my own application.

public void run_all_reports()
{
    var username = "";
    var password = "";

    foreach (var report in Reports.ReportList)
    {
        var ub = new UriBuilder("https", "reports.office365.com");
        ub.Path = string.Format("ecp/reportingwebservice/reporting.svc/{0}", report);
        var fullRestURL = Uri.EscapeUriString(ub.Uri.ToString());
        var request = (HttpWebRequest)WebRequest.Create(fullRestURL);
        request.Credentials = new NetworkCredential(username, password);

        try
        {
            var response = (HttpWebResponse)request.GetResponse();
            var encode = System.Text.Encoding.GetEncoding("utf-8");
            var readStream = new StreamReader(response.GetResponseStream(), encode);
            var doc = new XmlDocument();
            doc.LoadXml(readStream.ReadToEnd());

            doc.Save($@"C:\Office365\Reports\{DateTime.Now:yyyyMMdd}_{report}.xml");

            Console.WriteLine("Saved: {0}", report);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The get_report_list method simple hits the service endpoint and grabs the definition of all the available reports in the api.  Note that depending on your permission level you may not see all the reports available.  Finally, the run_report_messagetrace method is an example of pulling one report and mapping it back to an object using the HttpClient and Newtonsoft Json libraries.

Conclusion

With any cloud offering today there will generally be some reporting capabilities built in, however these reports are usually not enough.  Luckily more and more services are exposing their source data through api’s.  The Office 365 reporting service is just one example of that and from the sample methods above you can see how quickly it is to get access to this data.