PowerShell Script to Create a Mailbox Size Report for Exchange Server 2010

One of the Exchange Server administration tasks I perform almost every day is creating mailbox size reports. There are a few different reasons that I create these reports, such as planning a mailbox migration project, responding to a storage capacity alert for a particular database, or providing a specific team of people with a report of their mailbox sizes.

Now it is pretty easy to get the sizes for Exchange mailboxes and to handle the formatting of the Exchange 2010 mailbox statistics so that they are easier to perform calculations on, but it gets a bit boring running those commands day after day.

Even worse, after running the commands to create a CSV report I still had to open that in Excel, remove the unwanted details, use Excel formulas to convert the values from bytes to megabytes, sort them into order, and so on. Again not difficult, just boring after doing it hundreds of times.

So I created a PowerShell script to do all of the heavy lifting for me, and I'm sharing that script with you here. Let's take a look at how the script works.

Firstly the script accepts two optional parameters; -database or -file. These tell the script whether to run a report on a given mailbox database, or to run a report on a list of mailbox names in a text file.

param( 	[string]$database = "", 	[string]$file = "" )

You can specify either parameter, but not both at once, nor can you run the script without any parameters.

#Determine whether correct parameters have been used  if( $database -eq "" -and $file -eq "" ) { 	Write-Host -ForegroundColor Red "You must specify either -database or -file" }  if( $database -ne "" -and $file -ne "" ) { 	Write-Host -ForegroundColor Red "You can't use both -database and -file" }

With the correct parameter used the script executes one of two functions to generate the report.

#Get the report  if( $database -ne "" ) { 	Get-ReportDBMode $database }  if( $file -ne "" ) { 	Get-ReportFileMode $file }

Those functions simply retrieve the mailbox statistics, sort them in order of largest to smallest, perform the conversions of the values from bytes to megabytes, and then clean up the output to just the relevant information.

#................................... # Functions #...................................  function Get-ReportDBMode {  	param ($database) 	$report = Get-MailboxStatistics -database $database | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Label="Size(Mb)"; Expression={$_.TotalItemSize.Value.ToMb()}},LastLogonTime 	return ($report) }  function Get-ReportFileMode {  	param ($file) 	$report = Get-Content $file | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Label="Size(Mb)"; Expression={$_.TotalItemSize.Value.ToMb()}},LastLogonTime 	return ($report) }

The output will be similar to this:

Here are a few examples of how to run the script:

.\Get-MailboxReport.ps1 -database MB-HO-01  .\Get-MailboxReport.ps1 -file .\users.txt  .\Get-MailboxReport.ps1 -file .\users.txt | Export-CSV -NoTypeInformation -Path report.csv

Here is the complete script:

#Get-MailboxReport.ps1 #Written By: Paul Cunningham #URL: http://exchangeserverpro.com # #Change Log #V1.0, 15/10/2011 - Initial version # #.SYNOPSIS #Lists the mailboxes and mailbox sizes for #the specified database or list of users. Output #is sorted by TotalItemSize in descending order. # #.EXAMPLE #.\Get-MailboxReport.ps1 -database MB-HO-01 #Returns a report with the mailbox statistics for all mailbox users in #database MB-HO-01 # #.EXAMPLE #.\Get-MailboxReport.ps1 -file .\users.txt #Returns a report with the mailbox statistics for all mailbox users in #the file users.txt # #.EXAMPLE #.\Get-MailboxReport.ps1 -file .\users.txt | Export-CSV -NoTypeInformation -Path report.csv #Exports the report into a CSV file so that it can be opened in Microsoft Excel for #further analysis #  param( 	[string]$database = "", 	[string]$file = "" )  #................................... # Static Variables #...................................  #................................... # Functions #...................................  function Get-ReportDBMode {  	param ($database) 	$report = Get-MailboxStatistics -database $database | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Label="Size(Mb)"; Expression={$_.TotalItemSize.Value.ToMb()}},LastLogonTime 	return ($report) }  function Get-ReportFileMode {  	param ($file) 	$report = Get-Content $file | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,@{Label="Size(Mb)"; Expression={$_.TotalItemSize.Value.ToMb()}},LastLogonTime 	return ($report) }  #................................... # Script #...................................  #Determine whether correct parameters have been used  if( $database -eq "" -and $file -eq "" ) { 	Write-Host -ForegroundColor Red "You must specify either -database or -file" }  if( $database -ne "" -and $file -ne "" ) { 	Write-Host -ForegroundColor Red "You can't use both -database and -file" }  #Get the report  if( $database -ne "" ) { 	Get-ReportDBMode $database }  if( $file -ne "" ) { 	Get-ReportFileMode $file }