Get mailbox and AD info and export it to a CSV file

When doing mailbox migrations it essential to know some information about the mailboxes for migration planning. When working with clients on migrations I provide a spread sheet that lets them assign users to “Move Groups” and set the target database. But the client needs to know where the mailboxes are, how large they are, and possibly what department and/or title the users have for planning who to move when. Once we have this info the client can group users together and see in a table how large the “move group” is and how large the target database will be after the moves (I may post this XLSX in the future).

I had a VBScript written for Exchange 2003 that got the required information and needed the same thing for Exchange 2007 & 2010. The big issue I ran into was that I needed results from Get-Mailbox, Get-User, and Get-MailboxStatistics all in the same line so I could save the output to a CSV, to be later copied into my migration planning XLSX.

So I did some searching and found this post on how to combine the results of two PowerShell cmdlets into on output.

The script below will save the following attribute to a CSV file:

ServerName  DatabaseName, Name, FirstName, LastName, DisplayName, Alias, PrimarySmtpAddress, samAccountName, UserAccountControl, TotalItemSizeinKB, ItemCount, Company, Description, Department, Title, City, StateOrProvince, CountryOrRegion, DistinguishedName
Always check here for the latest version:
http://izzy.org/scripts/Exchange/Admin/Get-MailboxInfo.ps1

Exchange 2000/2003 VBScript version:
http://izzy.org/scripts/Exchange/Pre-2007/200x/GetMailboxInfo.vbs

# Modified to work with Exchange 2010 by Jason Sherry http://info.izzy.org
# Created 11/10/2010, Last Updated 4/24/2012
# Gets the size of mailboxes and certain attributes from the AD that can be used to plan for mailbox moves
$MB = Get-Mailbox -resultSize unlimited
$MB | foreach{
$AccountEnabled = "Enabled"
$user = Get-User $_ | select DisplayName, FirstName, LastName, company, department, title, samAccountName, UserAccountControl, City, StateOrProvince, CountryOrRegion
$mbx = $_ | select ServerName, samAccountName, Name, Alias, PrimarySmtpAddress, DistinguishedName
write-host "Processing: " $user.DisplayName "("$user.samAccountName")"
$ADSPath = "LDAP://" + $mbx.DistinguishedName
$ADUser = [ADSI]$ADSPath
$Description = [String]$ADUser.Description # Required to convert the returned value to a string
$mbx | add-member -type noteProperty -name DisplayName -value $User.DisplayName # Not part of the mailbox properties, so using Get-User property and adding it to $mbx variable
$mbx | add-member -type noteProperty -name FirstName -value $User.FirstName
$mbx | add-member -type noteProperty -name LastName -value $User.LastName
$mbx | add-member -type noteProperty -name Company -value $User.company
$mbx | add-member -type noteProperty -name Department -value $User.department
$mbx | add-member -type noteProperty -name Title -value $User.title
$mbx | add-member -type noteProperty -name City -value $User.City
$mbx | add-member -type noteProperty -name StateOrProvince -value $User.StateOrProvince
$mbx | add-member -type noteProperty -name CountryOrRegion -value $User.CountryOrRegion
$mbx | add-member -type noteProperty -name Description -value $Description # Not avaliable from Exchange cmdlets, so using ADSI
If ($User.UserAccountControl -contains "AccountDisabled"){
$AccountEnabled = "Disabled"
}
$mbx | add-member -type noteProperty -name UserAccountControl -value $AccountEnabled
Get-MailboxStatistics $_ | ForEach{
$MBSize = $_.TotalItemSize.Value.ToKB()
$MBItemCount = $_.ItemCount
$MBDB = $_.DatabaseName
}
$mbx | add-member -type noteProperty -name TotalItemSizeinKB -value $MBSize # Get attributes from Get-MailboxStatistics and add them to $mbx variable
$mbx | add-member -type noteProperty -name ItemCount -value $MBItemCount
$mbx | add-member -type noteProperty -name DatabaseName -value $MBDB
write-host "DisplayName: "$mbx.DisplayName "`tMailbox Size: "$mbx.TotalItemSizeinKB "`tMailbox Size: "$mbx.ItemCount
write-host
# Write-host $mbx.ServerName,"N/A", $mbx.DatabaseName, $mbx.Name, $mbx.FirstName, $mbx.LastName, $mbx.DisplayName, $mbx.Alias, $mbx.PrimarySmtpAddress, $mbx.samAccountName, $mbx.UserAccountControl, $mbx.TotalItemSizeinKB, $mbx.Description, $mbx.Department, $mbx.Title, $mbx.City, $mbx.StateOrProvince, $mbx.CountryOrRegion, $mbx.DistinguishedName
$mbx | Select ServerName,"N/A", DatabaseName, Name, FirstName, LastName, DisplayName, Alias, PrimarySmtpAddress, samAccountName, UserAccountControl, TotalItemSizeinKB, ItemCount, Company, Description, Department, Title, City, StateOrProvince, CountryOrRegion, DistinguishedName
} | export-csv -NoTypeInformation .\MailboxData.csv -Encoding unicode
$MB = $Null
$user = $Null
$ADUser = $Null
$MBX = $Null
CategoriesIT