Script: 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

# Based on script from: http://www.powergui.org/thread.jspa?threadID=7514
# 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
# For more info see: http://info.izzy.org/Wiki/GetMailBoxInfo.aspx
# For Exchange 2003 support see: http://info.izzy.org/Technical/Scripting/Documents/Forms/DispForm.aspx?ID=60

$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

About jasonsherry

I am a Microsoft Infrastructure Architect/Professional Services Consultant (FOR HIRE!), where I focus on the design, migration, and implementation of Microsoft infrastructure and collaboration software; such as Microsoft Exchange, SharePoint, SCOM, AD, and general Windows infrastructure. For more info see my bioresume at: http://jasonsherry.org
This entry was posted in Exchange, Microsoft and tagged , . Bookmark the permalink.

4 Responses to Script: Get mailbox and AD info and export it to a CSV file

  1. Jason B. Williams says:

    Great Script here. I had to add Office and the LastLogonTime information to mine but everything worked like a champ.

    Like

  2. Roger Haines says:

    Wow!! Bravo! Thanks for saving me tons of time. Works very well. Combining Get-User and Get-Mailbox shouldn’t be that hard!! Thanks again.

    Like

  3. vasan says:

    Hi Jason, am trying to get “reporting manager” field to be added into my script, but I can’t get the right attribute here. “Reporting manager” information available from “Organization” tab of each of user mailbox. Appreciate your help ya….

    Like

    • jasonsherry says:

      The field for a user’s manager is “manager”. It’s a single value field that stores the DN of the user’s manager. So to get a better value you would need to bind to this DN and then get DisplayName or other attributes to include in your output CSV probably.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s