Obtaining a list of mailboxes together with their sizes must rank reasonably highly in the list of most asked questions by Exchange administrators. In Exchange 2000 and Exchange 2003 it was possible to see this information within the Exchange System Manager snap-in. It was also possible to export the information presented within Exchange System Manager into text format and subsequently import this information into applications such as Excel. Other members of the Exchange community published VBScripts that used Windows Management Instrumentation (WMI) or Messaging Application Programming Interface (MAPI) to do much the same thing. Of course, to some administrators, these scripts were sometimes difficult to understand or modify. As I’m sure you all know by now, Exchange 2007 offers the Exchange Management Shell to administer Exchange 2007 from the command line and one of the commands, or cmdlets as they are known, gives administrators a much simpler approach to the issue of obtaining mailbox statistics.
Get-MailboxStatistics Basics
The most basic Exchange Management Shell cmdlet to use is the Get-MailboxStatistics cmdlet on its own. Figure 1 shows a sample of the output of running this cmdlet with no additional parameters. This runs against the local mailbox server.
Figure 1: Default Results of Get-MailboxStatistics
As you can see, by default this gives us 4 pieces of information for each mailbox on the local server, namely the display name of the mailbox, the number of items in the mailbox, the mailbox storage limit status and the last logon time. The actual size of the mailbox is not shown by default so the first task is to determine the name of the attribute that stores this value. One way to determine the available attributes that can be retrieved is to pipe the results of the cmdlet into the Format-List cmdlet, or fl for short. For example, our cmdlet now becomes:
Get-MailboxStatistics | fl
Figure 2 shows the results of doing this, where the attributes of User2’s mailbox are shown.
Figure 2: Results of Get-MailboxStatistics | fl
Now you can see other important pieces of information, such as the TotalItemSize attribute that has a value of 1584504B, or approximately 1.5MB. Clearly User2 is not a big user of Exchange 2007. Now that we know the attribute that we are interested in is called TotalItemSize, we can modify our original cmdlet to extract this information along with the mailbox name and item count. The cmdlet to use is shown below. Note the fact that this time, we’ve used the Format-Table cmdlet, or ft for short, to produce the output in table format:
Get-MailboxStatistics | ft DisplayName,TotalItemSize,ItemCount
The result of this cmdlet is shown in Figure 3.
Figure 3: Get-MailboxStatistics With Mailbox Sizes
Now we are getting somewhere, as this is a fairly concise output telling us pretty much what we need to know. However, there are a couple of drawbacks to this output. Firstly, the output is not in ascending or descending order, so it is difficult to see quickly which mailboxes are the biggest. Also, the TotalItemSize column is shown in bytes by default which also does not make for easy reading.
Additional Get-MailboxStatistics Formatting
Let’s address the order of the output first. Sorting objects using PowerShell is really easy via the, you guessed it, Sort-Object cmdlet. All you really need to do for this exercise is to get the mailbox statistics and then pipe the results into the Sort-Object cmdlet before piping these results into the Format-Table cmdlet. For the Sort-Object cmdlet, all we really need to decide is which column you want to sort on and the direction you want to sort in. The first parameter we need to add to Sort-Object is the column name to sort on, which in our case is TotalItemSize. We then add either –Descending or –Ascending to give us the direction to sort in. Let’s show the largest mailboxes first, which is typically what administrators need to know. The cmdlet now becomes:
Get-MailboxStatistics | Sort-Object TotalItemSize –Descending | ft DisplayName,TotalItemSize,ItemCount
The result of this cmdlet is shown in Figure 4.
Figure 4: Get-MailboxStatistics With Mailbox Sizes in Descending Size Order
Next we need to convert the mailbox sizes from bytes into something more useful. Megabytes is the obvious answer although with the ever increasing mailbox sizes it will not be long before gigabytes will be used as the default. However, since on my test system I have only got relatively small mailbox sizes, I am going to display the mailbox sizes in kilobytes. To do this, we need to replace the TotalItemSize parameter in our cmdlet with something inherently more complicated:
@{ expression={$_.TotalItemSize.Value.ToKB()}}
So our cmdlet now looks like this:
Get-MailboxStatistics | Sort-Object TotalItemSize –Descending | ft DisplayName,@{ expression={$_.TotalItemSize.Value.ToKB()}},ItemCount
The result of this cmdlet is shown in Figure 5 below. If you want to display the mailbox sizes in MB, use TotalItemSize.Value.ToMB in the cmdlet above. Or you can use TotalItemSize.Value.ToGB of course.
Figure 5: Get-MailboxStatistics With Mailbox Sizes in KB
That’s looking much better. But wait! Look at the column names now. We can see that the column that was previously called TotalItemSize is now referenced in the rather cumbersome form of $_.TotalItemSize.Value.ToKB(). We can address that very easily by adding a new label to the cmdlet. In fact, all you need to do is to add a change to the cmdlet to re-label the column appropriately. The new cmdlet is shown below:
Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | ft DisplayName,@{label=”TotalItemSize(KB)”;expression={$_.TotalItemSize.Value.ToKB()}},ItemCount
The result of this is shown in Figure 6:
Figure 6: Get-MailboxStatistics With Labeled Column Names
At last we have a useful output that is nicely formatted and one that we can use to identify the largest mailboxes. What you don’t want to be doing is running this script manually every day, week or month. Obviously applications such as System Center Operations Manager (SCOM) 2007 bring this information back to you via the management console, so how can we do a similar thing with the Exchange Management Shell? The most obvious method is to send the information via email so let’s look at how this can be done.
Emailing The Results
Figure 7 below shows a PowerShell script called sendstats.ps1 that can be used to first generate the mailbox statistics via the cmdlet that has been built up in this article, then mail the results of this cmdlet to the administrator. The first thing to note with the script is that the results of the Get-MailboxStatistics cmdlet have been directed to a file called mailboxes.txt. This file is created in the folder where the script is run. The other lines of the script create and send the email, adding the mailboxes.txt file as an attachment. One important thing to note is that the line starting $SendingServer references the FQDN of the mail server responsible for sending the message. Obviously it will be a requirement to ensure that this server can actually relay the message.
###Send mailbox statistics script
###First, the administrator must change the mail message values in this section
$FromAddress = [email protected]
$ToAddress = [email protected]
$MessageSubject = “Mailbox Size Report”
$MessageBody = “Attached is the current list of mailbox sizes.”
$SendingServer = “e2k7.neilhobson.com”
###Now get the stats and store in a text file
Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | ft
DisplayName,@{label=”TotalItemSize(KB)”;expression={$_.TotalItemSize.Value.ToKB()}},
ItemCount > mailboxes.txt
###Create the mail message and add the statistics text file as an attachment
$SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress,
$MessageSubject, $MessageBody
$Attachment = New-Object Net.Mail.Attachment(“./mailboxes.txt”)
$SMTPMessage.Attachments.Add($Attachment)
###Send the message
$SMTPClient = New-Object System.Net.Mail.SMTPClient $SendingServer
$SMTPClient.Send($SMTPMessage)
Figure 7: SendStats.PS1 Script
Once executed, the script should send the email as you can see below in Figure 8. Opening the attachment reveals the output of the Get-MailboxStatistics script as shown in Figure 9.
Figure 8: Emailed Report
Figure 9: Attachment Contents
Although that gets us an email message with the relevant data contained within it, there is nothing contained within the script regarding it being run on a regular schedule. To do that, we can take advantage of the Windows scheduler service and execute the script on a regular basis. To do this, we need to run the Exchange Management Shell and also specify the script to run. This can be achieved by the following command:
PowerShell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\Bin\ExShell.psc1” -Command “./sendstats.ps1”
Here you can see that PowerShell is run and loads the Exchange console file, found on the C: drive in this example. Obviously you may need to change this depending on which drive Exchange 2007 has been installed onto. The –Command parameter is used to identify the script that we want to run, namely sendstats.ps1.
Summary
In this article we’ve looked at a very common issue faced by an Exchange administrator, namely the ability to produce a list of mailboxes and their sizes. Although tools such as SCOM 2007 can do this for you, it can be seen that it’s simple to achieve these results using the Exchange Management Shell cmdlet Get-MailboxStatistics. PowerShell can initially be a steep learning curve, but it’s something well worth getting to grips with.
