Working with Exchange Online Reports
Summary: Use Windows PowerShell to Manage Office 365 using Windows PowerShell cmdlets, scripts, and batch processes.
With nearly 500 cmdlets at your disposal this article can barely even scratched the surface of what you can do with Windows PowerShell and Exchange Online. But here are a couple commands you might find useful. Often-times administrators like to keep track of whether people are actually using the system; for example, maybe you’d like to know if there are users who haven’t opened their Outlook mailbox in the past week or the past month or the past whenever. Windows PowerShell is perfect for doing that type of reporting. For example, here’s a command that returns all the mailboxes that no one has logged onto since November 1, 2013:
Get-Mailbox | Get-MailboxStatistics | Where-Object {$_.LastLogonTime -lt "11/1/2013"}
And here’s a command that returns all the mailboxes that no one has ever logged onto:
Get-Mailbox | Get-MailboxStatistics | Where-Object {$_.LastLogonTime -eq $Null}
Pretty cool, isn’t it? Here’s how you can get a list of users who have more than 10,000 items in their mailbox:
Get-Mailbox | Get-MailboxStatistics | Where-Object {$_.ItemCount -gt 10000}
As you might expect, we could do this day. But we won’t.
Without going into any detail, here are just a few of the many cmdlets that can be used to return reporting information and statistics:
That’s what we said: wow.
Note
If you look closely at the list, you might notice the Get-CsConferenceReport cmdlet. The Get-Cs portion of that cmdlet name suggests that this is a Lync Online cmdlet, not an Exchange Online cmdlet. Is that true?
That depends on how you want to look at it. This cmdlet (as well as other Get-Cs reporting cmdlets) does return information about Lync Online, and it was created by the Lync Online team. However, the Get-Cs reporting cmdlets require the Exchange Online reporting service in order to generate and return data. That means you must have a subscription to Exchange Online in order to use these cmdlets; if you don’t have a subscription to Exchange Online then you won’t have access to the reporting service. Because of that, these cmdlets are included in the Exchange module rather than the Lync Online module. If you start Windows PowerShell and load only the Lync Online module, you will not see any of the Get-Cs reporting cmdlets. To make a long story short, you need to load the Exchange module in order to generate Lync Online reports.
So what kind of reports can you generate using the reporting cmdlets? As you can tell by the cmdlet names, plus the examples we’ve already shown you, the answer is this: pretty much any kind of report you can think of. But let’s take a closer at one useful cmdlet we’ve already been introduced to: Get-MailboxStatistics. Get-MailboxStatistics is designed to return detailed information about user mailboxes. By default, Get-MailboxStatistics returns information similar to this for a single mailbox:
DisplayName ItemCount StorageLimitStatus LastLogonTime
----------- --------- ------------------ -------------
Alex Darrow 29 10/8/2013 3:18:05 PM
As you can see, even this simple report has some very useful information, including the total number of items in Alex’s mailbox (ItemCount) and the last time Alex logged on (LastLogonTime).
Note
Good question: how did we get back this information? As it turns out, we ran the following Windows PowerShell command:
Get-MailboxStatistics –Identity "alexd"
But there’s definitely a lot more information where that came from. For example, maybe we’d like to know the total size of the items in Alex’s mailbox. In that case, we could run this command:
Get-MailboxStatistics -Identity "alexd" | Select-Object DisplayName, TotalItemSize
Or maybe we’d like to know the number of deleted items in Alex’s mailbox. No problem:
Get-MailboxStatistics -Identity "alexd" | Select-Object DisplayName, DeletedItemCount
Or maybe we’d like to know both:
Get-MailboxStatistics -Identity "alexd" | Select-Object DisplayName, TotalItemSize, DeletedItemCount
You get the idea. To see all the property values that can be returned using the Get-MailboxStatistics cmdlet just run a command similar to this:
Get-MailboxStatistics -Identity "alexd" | Select-Object *
And of course, you’re not limited to having to look at mailbox statistics one mailbox at a time. Want a report listing information about all your mailboxes? No sooner said than done:
Get-Mailbox | Get-MailboxStatistics
That command will return information similar to this:
DisplayName ItemCount StorageLimitStatus LastLogonTime
----------- --------- ------------------ -------------
Alex Darrow 29 10/8/2013 1:18:05 PM
Allie Bellew 3 11/8/2013 9:18:21 PM
Anne Wallace 3 10/21/2013 3:18:45 PM
Aziz Hassouneh 2 10/18/2013 2:19:05 PM
Belinda Newman 21 10/4/2013 3:19:26 PM
Bonnie Kearn 13 10/8/2013 8:19:50 PM
Is that cool or what?
You also aren’t limited to two options here: getting back information for one mailbox, or getting back information for all your mailboxes. Instead, you can write all sorts of clever little Windows PowerShell commands that return information for a subset of mailboxes. Need information just for three of your mailboxes? Then simply pipe the identifying information to Get-MailboxStatistics. For example:
"Alex Darrow", "Allie Bellew", "Anne Wallace" | Get-MailboxStatistics
That returns data just for the three specified mailboxes:
DisplayName ItemCount StorageLimitStatus LastLogonTime
----------- --------- ------------------ -------------
Alex Darrow 29 10/8/2013 1:18:05 PM
Allie Bellew 3 11/8/2013 9:18:21 PM
Anne Wallace 3 10/21/2013 3:18:45 PM
Or maybe you’d like to generate a report for all the mailboxes belonging to the Finance department. That can be done with a command similar to this:
Get-User -Filter '{Department -eq "Finance"}' | Get-MailboxStatistics
Or how about this command, which returns a list of all the users who haven’t logged since October 31, 2013 (that is, their last logon time is less than [-lt] 10/31/2013):
Get-Mailbox | Get-MailboxStatistics | Where-Object {$_.LastLogonTime -lt "11/1/2013"}
Nice.
Next: Determining Which Cmdlets are Available to Exchange Online Administrators