Thursday, August 14, 2008

PowerShell - Mailbox Size to CSV

Unfortunately, we don't have the luxury of using a software package that provides reporting on our mailbox sizes and statistics like Quest's MessageStats (http://www.quest.com/messagestats/). So, I had to devise a way to get the requested (from my boss) statistics for all user's mailbox sizes. The information that was requested included:
  • Display Name
  • Department
  • Total Items
  • Mailbox Size (MB)
All of this information needed to be put into a CSV file that could later be used for analysis using Excel.

There are two cmdlets needed to access this data:
  • get-mailbox
  • get-mailboxstatistics
It is possible to get information from one command to the other by piping (|) it, but I couldn't make this work because I needed to aggregate the data for each user. To address this, I created a collection of the mailboxes, then as I looped through the collection, I create an object that contained the information from the collection $colMailboxes (information from the get-mailbox cmdlet) and from the $mailboxStats object (information from the get-mailboxstatistics cmdlet). As I looped through the mailboxes, the script creates a string that is then appended to a file.

#create the output file

#create object of mailboxes
$colMailboxes = get-mailbox -resultsize unlimited

$strHeaders = "Display Name, Department, Total Items, Mailbox Size (MB)"
write-output $strHeaders

foreach ($objMailbox in $colMailboxes) {
     $mailboxStats = get-mailboxstatistics -identity $objMailbox.alias
     #if there is an error
     if(!$?){
          $foreach.movenext()}
     else{
          $strOutput = $objMailbox.DisplayName + "," + $objMailbox.office + "," + $mailboxStats.ItemCount + "," + $mailboxStats.TotalItemSize.value.toMB()
     write-output $strOutput
     }
}


I saved this as mailbox_size_toCSV.ps1. When running the script the results must be put out to a file.

./mailbox_size_toCSV.ps1 | out-file mbstats.csv

There are probably several things that I could do to improve this script, but it works for me and was pretty easy to put together.

No comments: