Create Exchange 2010 Mailbox Size Reports with PowerShell

When I train or speak about PowerShell, I always talk about it in terms of a management engine. We can access this engine through a number of interfaces. PowerShell isn’t just a command line tool. If you need a GUI you can have one, but it will sit on top of Powershell.
The Exchange management tools are a great example of this paradigm. Sure, there is a graphical Exchange management console, but it is limited to what it was designed to handle. If you want to do something else, you need to drop down to the PowerShell console. For example, there is no provision in the GUI to create mailbox storage reports. But it is relatively simple to do so from a PowerShell prompt.
I’m going to demonstrate how to do this from a Windows 8 desktop with Exchange 2010 management tools installed. All of my commands in this article will be run from the Exchange PowerShell console running on the client. In follow-up articles, I’ll show you how to create Exchange 2010 individual inbox reports and Exchange 2010 multiple inbox reports using PowerShell.

Exchange Mailbox Size Reporting: Starting Small

Let’s start small and look at reporting for a single mailbox. Exchange has a cmdlet for this task, Get-MailboxStatistics, which returns some basic information. First, I’ll get the mailbox statistics for Jack Frost.

[PS] C:\>$jf = get-mailboxstatistics jfrost Creating a new session for implicit remoting of “Get-MailboxStatistics” command… [PS] C:\>$JF   DisplayName               ItemCount    StorageLimitStatus      LastLogonTime ———–               ———    ——————      ————- Jack Frost                52                   BelowLimit

My test mail server doesn’t have a lot of data nor actual users, but the techniques I’m using should work for you just as well. The mailbox itself has some nice information we can use.

[PS] C:\>$jf | Select Displayname,ItemCount,TotalItemSize
DisplayName                     ItemCount TotalItemSize
-----------                     --------- -------------
Jack Frost                             52 442.2 KB (452,782 bytes)

That TotalItemSize property is useful.

[PS] C:\>$jf.TotalItemSize
IsUnlimited Value
----------- -----
False 442.2 KB (452,782 bytes)

I can tell from the way this is displayed that the property is actually another type of object. Piping the object to Get-Member confirms it as you can see in below in Figure 1.
Exchange 2010 reporting
 
It looks like the Value property is “cooked”. Based on this output, I suspect Value is yet another object. As you see in Figure 2, it is.
Exchange 2010 reporting
 
This means I can format the value any number of units.

[PS] C:\>$jf.TotalItemSize.value.tokb()
442
[PS] C:\>$jf.TotalItemSize.value.tobytes()
452782

All that remains is to use this to define a custom property.

PS C:\> $jf | select DisplayName,ItemCount,@{Name="SizeKB";Expression={$_.totalItemSize.Value.ToKB()}}

 
Exchange 2010 reporting
The reason why will become apparent in a few minutes.

Exchange Mailbox Size Reporting: Scaling Up

In PowerShell, if I can do something for one object I can probably do it for 10, 100, or 1,000 objects. My Exchange 2010 server has two mail databases, so I’ll just focus on one of them for now and retrieve all of the mailboxes. You could easily combine everything I’m going to demonstrate into a single one-line command, but I’m going to separate the steps for the sake of clarity.

PS C:\> $mb = get-mailboxdatabase chi-maildb | get-mailbox

Since I already know how to get information for a single mailbox it isn’t much more work to do it for all the mailboxes. I’ll even sort by size.

$mb | Get-MailboxStatistics |
Select Displayname,ItemCount,
@{Name="SizeKB";Expression={$_.totalItemSize.Value.toKB()}} |
Sort SizeKB -Descending | Format-Table –auto

You can see the results below in Figure 4.
Exchange 2010 reporting
This report was for mailboxes in a given database. But what about finding, say, the top ten mailboxes across all databases?

get-mailbox | Get-MailboxStatistics |
Select Displayname,ItemCount,
@{Name="SizeKB";Expression={$_.totalItemSize.Value.toKB()}} |
Sort SizeKB -Descending | Select -first 10 |
Format-Table –auto

As you can see below, I’m formatting the results as a nice table.
Exchange 2010 reporting
I could pipe this to Out-File and save it in a text file. Or I could remove the format command and pipe it to other cmdlets like Export-CSV, Send-Mailmessage or ConvertTo-HTML. Just because this is Exchange doesn’t mean I’ve given up my core PowerShell cmdlets.
Let’s take one more pass here and report on usage by database, since I have two.

get-mailboxdatabase | sort name | foreach {
$stats = $_ | get-mailboxstatistics
$_ | Add-Member -membertype ScriptProperty -name TotalCount -value {
($stats | Measure ItemCount -sum ).sum }
$_ | Add-Member -MemberType ScriptProperty -Name SizeMB -value {
($stats | foreach {$_.totalItemSize.Value.ToBytes()}| Measure -sum).sum/1MB }
$_ | Select Servername,Name,TotalCount,SizeMB
} | format-table –AutoSize

I’ll admit that this is a little more complicated. But all I’m doing is getting the mailbox databases, then I get mailbox statistics for each one. Next I add custom properties to the database object to show the total item count and total size formatted as bytes. I could have use the ToMB() method on TotalItemSize.Value, but I thought if I did the sum on bytes and then converted I’d have a more accurate value. You can see the result below in Figure 6.
Exchange 2010 reporting
 
I’m having so much fun with this, so let’s slice and dice the data one more way. Here’s a report for each database showing the individual mailboxes as well as a value that indicates their percentage use of the total database.

get-mailboxdatabase | sort name | foreach {
$stats = $_ | get-mailboxstatistics
$dbTotalSize = ($stats |  foreach {$_.totalItemSize.Value.ToBytes()}| Measure -sum).sum
$stats | Select Database,Displayname,ItemCount,
@{Name="SizeKB";Expression={$_.totalItemSize.Value.toKB()}},
@{Name="PerDB";Expression={
[math]::Round(($_.totalItemSize.Value.tobytes()/$dbTotalSize)*100,4)
}}
} | Sort Database,SizeKB -descending |
format-table -Group Database -property Displayname,ItemCount,SizeKB,PerDB

As before, each database is processed, thereby getting mailbox statistics. I create a variable, $dbTotalSize, which should be the total size of all mailboxes for the database. Later I can create a custom property called PerDB that takes the users converted total item size and divides it by this value to get a percentage. I went ahead and used the Round() method from the .NET [math] class to format the value to 4 decimal places. As you can see below, my mail server has small mailboxes.
Exchange 2010 reporting
 
Even though the GUI lacks what I need, with a little digging around in the PowerShell console, and by using cmdlets I already knew how to use like Select-Object and Add-Member, I can put together some pretty useful Exchange mailbox usage reports. While you could run all of my code samples interactively at the console, I expect you’ll want to turn them into scripts or functions to save some typing.