Weekend Scripter: Use PowerShell to Create Office 365 Reports

Doctor Scripto

Summary: Guest blogger, Alan Byrne, talks about using Windows PowerShell to create Office 365 reports.

Microsoft Scripting Guy, Ed Wilson, is here. Today I would like to welcome guest blogger Alan Byrne.

Photo of Alan Byrne

Alan is the cofounder of Cogmotive Reports, an Office 365 Reporting and Analytics web application that offers a range of automated Office 365 reports for mailbox sizes, user log-on times, mobile devices, licence usage, and several other Office 365 metrics. He has an extensive background in Microsoft Windows and in Exchange design and administration, and he tries to script every administration task he comes across. More recently, Alan has been applying these skills to Microsoft Office 365.

Here is Alan’s contact information:
Twitter: @cogmotive
Website: Cogmotive Reports

Here’s Alan…

Microsoft has recently unveiled the Office 365 Reporting Web Service, which enables us to get usage information about our Office 365 environment. This web service can be used to get all sorts of useful statistics and reporting data, including reports about mailbox activity, mail traffic, Lync Online, and much more.

In this blog post, we are hoping to give you a high-level overview of these reporting web services and provide some code samples to help you use Windows PowerShell to get the information you need.

Our example uses the ConnectionbyClientTypeDetailDaily web service, which shows you how each of your employees have connected to their Office 365 mailbox in the past few days. For example, are they reading their email by using Outlook (MAPI), a mobile phone (Exchange ActiveSync – EAS), or web mail (OWA)?

The MSDN website provides a lot of information about the web services, and it includes some code samples. Unfortunately, the information is very much geared towards software developers rather than us system administrator types.

The Office 365 reporting services are available to all customers in the new version of Office 365 (Wave 15), and they are accessible over HTTPS.

To quickly see what reports are available to you, simply fire up your browser and navigate to the base reporting URL:

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

Enter your Administrator credentials when prompted, and you will be presented with an XML output of all the web services available to you.

Image of command output

Our Windows PowerShell script also needs to handle credentials, which we do like this:

$OutputFile = “ConnectionbyClientTypeDetailDaily.csv”

$username = “admin@domain.onmicrosoft.com” # Administrator Username

$password = “Password” | ConvertTo-SecureString -asPlainText -Force # Encrypted Password

$cred = New-Object System.Management.Automation.PSCredential($username,$password)

Each of the previous web services can be manipulated via the URL query string, and they return the data in either XML or a JSON value.

Let’s start by building the request URL for this Client Access report. We use the following URL:

https://reports.office365.com/ecp/reportingwebservice/reporting.svc/ConnectionbyClientTypeDetailDaily/?$select=Date,WindowsLiveID,UserName,ClientType,Count&$format=JSON

If we paste this URL into our browser, the web service will spit a chunk of JSON back at us:

Image of command output

ProTip: You can paste the JSON into the JSON Parser Online to make it readable by actual humans.

Let’s break down the URL to understand what all the components do:

  • Here is the base URL:

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

  • Here is the web service we are calling:

ConnectionbyClientTypeDetailDaily

  • Here is the select variable, which works similar to SQL Server and tells the web service which fields you want returned:

$select=Date,WindowsLiveID,UserName,ClientType,Count

Note  I suggest opening the Report Service URL in your browser without any query string to see all the values that are returned. This enables you to learn what is available before filtering.

  • Here is the format of the data returned—we are using JSON:

 $format=JSON

Our Windows PowerShell script has variables in it for each of these values, which allows us to quickly modify the script if we want to use a different web service in future:

$Root = “https://reports.office365.com/ecp/reportingwebservice/reporting.svc/” # Report Root URL

$WebService = “ConnectionbyClientTypeDetailDaily”

$Select = “`$select=Date,WindowsLiveID,UserName,ClientType,Count”

$Format = “`$format=JSON”

$url = ($Root + $WebService + “/?” + $Select + “&” + $Format) # Build report URL

We can now pass this URL and our credentials to the Invoke-RestMethod cmdlet, which turns the JSON output into a useable Windows PowerShell object. (This cmdlet is available only in Windows PowerShell 3.0.)

$rawReportData = (Invoke-RestMethod -Credential $cred -uri $url).d.results # Get all properties in an object

Finally, we iterate through this object to write all the data to a CSV file so we can open it in Excel.

foreach ($entry in $rawReportData)

{

            $Date = $entry.Date.ToString(“yyyy-MM-dd”)

            $WindowsLiveID = $entry.WindowsLiveID

            $UserName = $entry.UserName

            $ClientType = $entry.ClientType

            $Count = $entry.Count

           

            Out-File -FilePath $OutputFile -InputObject (“$Date,$WindowsLiveID,$UserName,$ClientType,$Count”) -Encoding UTF8 -append

            write-host (“$Date,$WindowsLiveID,$UserName,$ClientType,$Count”)

}

Hopefully, this provides a simple starting point for you to impress your boss with your new insight into how your Office 365 cloud is being used by your employees.

~Alan

You can find Alan’s script in the Script Center Repository: Accessing the Office 365 Reporting Web Service from PowerShell.

Thank you, Alan, for sharing your time and knowledge. Join me tomorrow as we begin a guest series about PowerSploit. It is way cool stuff that you will not want to miss.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

0 comments

Discussion is closed.

Feedback usabilla icon