Summary: Use Windows PowerShell to produce a mailbox activity report for Office 365.

Microsoft Scripting Guy, Ed Wilson, is here. This morning I am sipping a cup of pomegranate black tea, and nibbling on a fresh blueberry scone. I am using my Windows Surface Pro 2 to check my email, and to make sure the Hey, Scripting Guy! Blog posted properly overnight. Everything is groovy.

I am looking for the Scripting Wife’s phone. No, she hasn’t lost it. She ordered a new one. To be fair, it will be her birthday present, although she may not know that yet. She is turning out to be a huge gadget geek, and she announced that she simply must have a new phone. She ended up ordering the Nokia Lumia 1020. She came close to ordering the Nokia 1520 due to the 41-megapixel camera and because of the larger screen. In the end, it was the camera that tipped the scale. So, I am looking for her phone to show up in the morning deliveries. Not here yet, but then it isn’t even 8:00 AM yet either.

Mailbox usage report

The MailboxUsage report REST URI provides summary stats about the Office 365 organization's mailbox usage. The report makes the following fields available:

• Date
• MailboxesOverWarningSize
• MailboxesUsedLessThan25Percent
• TenantGuid
• TenantName
• TotalInactiveMailboxCount
• TotalMailboxCount

Information about the report is available on the MSDN page that details this report: MailboxUsage report.

Note  Todays script is based on the one I created yesterday, and you should read that post for more details about the script and how it works: Use PowerShell for Office 365 Reporting.

The first portion of the script is the same as the one used yesterday. This portion of the code reads the credentials from the XML file and specifies the URL root, the format, and the report to run:

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

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

$Format = "`$format=JSON"

$WebService = "MailboxUsage" 

I decided to create a separate variable to hold the property names because the property names are all pretty long, and my script was getting bulky. In the end, I decided that I did not need all of the properties. I then used an expanding string to create the Select portion of the query string. This is shown here:

$aryP = "Date,TenantName,MailboxesOverWarningSize,TotalInactiveMailboxCount,TotalMailboxCount"

$Select = "`$select=$aryP"  

The next portion uses a string to create the report URL:

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

The Invoke-RestMethod cmdlet accepts the credentials from the XML file and the URI created in the previous string. Grouping returns the results and sends them down the pipeline. This is shown here:

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

Now I need to create a custom object for my output. I decided that, unlike yesterday, I do not want a string for a date. I want the complete DateTime object because it has a number of methods that make it easier to work with. This script uses Windows PowerShell 3.0 syntax to create a custom object that contains the properties I specified earlier:

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

    Foreach-Object {

      [PSCustomObject] @{

        Date = $_.date

        TenantName = $_.TenantName

        MailboxesOverWarningSize = $_.MailboxesOverWarningSize

        TotalMailboxCount = $_.TotalMailboxCount

        TotalInactiveMailboxCount = $_.TotalInactiveMailboxCount } } 

It is possible to use a filter, an orderby in the query string, or the Select option. But to be honest, it is easier for me to use Windows PowerShell to do this. For example, if I only want to see a mailbox report for a date that is later than 6/30/14, I can add the following to my script:

| where date -gt "6/30/14" 

Windows PowerShell automatically casts the string to a date for me, and I am at little risk of messing up my script. This is shown in the following image:

Maybe I only want to find dates that have inactive mailboxes. I can also do this fairly easily. Here is the script,, but the results are not too exciting:

| where TotalInactiveMailboxCount

Maybe I want to track the mailbox count over time. I can use my same script, and develop a slightly different query. Here it is:

| Select Date, TotalMailboxCount | sort Date -Descending

When I run the script, I see the mailbox count for each date, and the report begins with the most recent date. This is shown in the following image:

That is all there is to using Windows PowerShell to produce a mailbox activity report. 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