Summary: Use Windows PowerShell to query the Office 365 Reporting web service.

 Hey, Scripting Guy! There are a lot of reports available via various buttons on the Office 365 Admin page, but only a few seem to have a cmdlet to back them up. Is there a way that I can use Windows PowerShell to automate creation, and maybe even to customize the results from those reports?

—GB

 Hello GB,

Microsoft Scripting Guy, Ed Wilson, is here. This morning I thought I would play some Alice Cooper on my Zune. I decided to have a bagel and a cup of coffee for breakfast. Teresa scored some organic Peruvian beans, and I grew to appreciate Peruvian coffee when I was teaching Windows PowerShell classes in Lima. Actually, Lima is a way cool town, and the people I worked with down there were top notch. Here is a pretty cool picture I took one afternoon.

So, today is sort of an old-school type of day—old school music, old school music device, old school breakfast.

Speaking of old school…

            Using the Office 365 Reporting web service

A great source of Office 365 information is available via the Office 365 Reporting web service. A good description of the service and some good documentation is available on MSDN: Office 365 Reporting web service. The primary focus of this service is for developers and others who create custom reports. All of the reports are available in the Admin portal. The reports that are accessible via Windows PowerShell cmdlets are accessible via the reporting web service. Using this web service is pretty straightforward, especially to regular readers of the Hey, Scripting Guy! Blog.

The hard part of working with the Office 365 Reporting web service is creating the query string that is passed as part of the call to Invoke-RestMethod. I found an easy way around that when I ran across a script written by Alan Byrne in the Script Center Repository: Accessing the Office 365 Reporting Web Service from PowerShell. Alan has written several guest posts for the Hey, Scripting Guy! Blog, so I felt confident in borrowing some of his code.

But of course, I needed to modify the code. Here is the code I borrowed from Alan:

$Root = "https://reports.office365.com/ecp/reportingwebservice/reporting.svc/" 

$Format = "`$format=JSON"

$WebService = "ConnectionbyClientTypeYearly"

$Select = "`$select=Date,ClientType,TenantName,Count" 

# Build report URL

$url = ($Root + $WebService + "/?" + $Select + "&" + $Format)  

I need to modify two pieces of this, depending on which report I want to run. The reason is that $WebService variable holds the name that I retrieve from the Office 365 Reporting web service article. For example, in this script, I query the ConnectionbyClientTypeYearly report. MSDN tells me that this report contains the following fields:

• ClientType
• Count
• Date
• TenantGuid
• TenantName

From this list of available fields, I decide to modify my $Select variable as follows:

$Select = "`$select=Date,ClientType,TenantName,Count"

I also learn that there are four ConnectionbyClientType* reports. These reports are:

• ConnectionbyClientTypeDaily
• ConnectionbyClientTypeWeekly
• ConnectionbyClientTypeMonthly
• ConnectionbyClientTypeYearly

Note  There is also a ConnectionbyClientTypeDetail* series of reports. Don’t get confused like I did. They offer different fields.

I decide to use my same stored credentials (like I have done all week); and therefore, I read the stored credentials from my XML file. This is shown here:

$cred = Import-Clixml C:\fso\ScriptingGuyCredential.xml

Hey dude, PowerShell is PowerShell

One of the cool things, for people who know Windows PowerShell, is that PowerShell is PowerShell is PowerShell. So I pipe the results from Invoke-RestMethod to the Foreach-Object cmdlet, and I build a custom object. I use the Windows PowerShell 3.0 syntax for building a custom object. (I talked about this technique and why to use it in the following Hey, Scripting Guy! Blog post: A PowerShell Object Lesson: Part 3.)

The advantage of creating a custom Windows PowerShell object is that I can do anything I want to do with the object. Here is the script that accomplishes this task:

(Invoke-RestMethod -Credential $cred -uri $url).d.results   |

    Foreach-Object {

      [PSCustomObject] @{

        Date = $_.date.tostring("yyyy-MM-dd")

        WindowsLiveID = $_.WindowsLiveID

        UserName = $_.UserName

        ClientType = $_.ClientType

        Count = $_.Count } }  

Now, the question is what do I want to do in my script. For example, I can pipe the resulting objects to Out-Gridview. The line to do so goes right after the Count=$_.Count line, and outside of the two closing braces as shown here:

Count = $_.Count } }  | Out-GridView

If I want to write to a text file, I might want to format the output as a nice table. To do this, I might add the path to the text file at the top of my script where I created all of my variable assignments. Then I would add the Format-Table cmdlet after the Count = Count script. This is shown here:

$OutputFile = "c:\fso\connecttype.txt"    

 <# … #>

Count = $_.Count } }  |

Format-Table |  Out-File -FilePath $OutputFile -Encoding ascii

To write to a CSV file, I would use Export-CSV. This modification is shown here:

Count = $_.Count } }  | Export-Csv -Path $OutputFile

So, the lesson is that after I create objects from my report, I can use them in any way that I desire. The complete script (without modifications) is shown here:

$cred = Import-Clixml C:\fso\ScriptingGuyCredential.xml

$Root = "https://reports.office365.com/ecp/reportingwebservice/reporting.svc/" 

$Format = "`$format=JSON"

$WebService = "ConnectionbyClientTypeYearly"

$Select = "`$select=Date,ClientType,TenantName,Count" 

# Build report URL

$url = ($Root + $WebService + "/?" + $Select + "&" + $Format)  

(Invoke-RestMethod -Credential $cred -uri $url).d.results   |

    Foreach-Object {

      [PSCustomObject] @{

        Date = $_.date.tostring("yyyy-MM-dd")

        WindowsLiveID = $_.WindowsLiveID

        UserName = $_.UserName

        ClientType = $_.ClientType

        Count = $_.Count } }  

GB, that is all there is to using Windows PowerShell to query the Office 365 Reporting web service. Office 365 Week will continue tomorrow when I will talk about more cool stuff.

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