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, Get-MailboxReport.ps1, to do all of the heavy lifting for me, and I’m sharing that script with you here.
Download the script file here: Get-MailboxReport.ps1 (downloaded 3567 times so far)
Let’s take a look at how the script works. Here if a video to demonstrate:
I’ve included help information within the script itself so you can use Get-Help to discover how to run the script.
[PS] C:\Scripts\demo>Get-Help .\Get-MailboxReport.ps1
NAME
C:\Scripts\demo\Get-MailboxReport.ps1
SYNOPSIS
Get-MailboxReport.ps1 - Mailbox report generation script.
SYNTAX
C:\Scripts\demo\Get-MailboxReport.ps1 [-database ] []
C:\Scripts\demo\Get-MailboxReport.ps1 [-file ] []
C:\Scripts\demo\Get-MailboxReport.ps1 [-server ] []
C:\Scripts\demo\Get-MailboxReport.ps1 [-mailbox ] []
C:\Scripts\demo\Get-MailboxReport.ps1 [-all] []
DESCRIPTION
Generates a report of useful information for
the specified server, database, mailbox or list of mailboxes.
Use only one parameter at a time depending on the scope of
your mailbox report.
RELATED LINKS
For more script details, a video demo, or to give feedback please go to:
http://exchangeserverpro.com/powershell-script-create-mailbox-size-report-exchange-server-2010
REMARKS
To see the examples, type: "get-help C:\Scripts\demo\Get-MailboxReport.ps1 -examples".
For more information, type: "get-help C:\Scripts\demo\Get-MailboxReport.ps1 -detailed".
For technical information, type: "get-help C:\Scripts\demo\Get-MailboxReport.ps1 -full".
Depending on which parameter you use the output will vary.
- If you use the -mailbox parameter to query a single mailbox, then the output will appear in the console window. I don’t really see the need to output a single mailbox’s details to a CSV file.
- If you use any of the other parameters, -server, -database, -file, or -all, the output will be written to a CSV file in the same folder you’re running the script from.
- You can use the optional -filename parameter to specify your own output file name
Once you’ve generated the CSV report you can open it with Excel and begin to analyze the data.
Download the script file here: Get-MailboxReport.ps1 (downloaded 3567 times so far)
Change Log:
- 6/02/2012 – V1.0 – Initial version
- 27/02/2012 – V1.1 - Improved recipient scope settings, exception handling, and custom file name parameter.
About Paul Cunningham
This is Awsome. I tried it and works smoothly. Before using this scripte i used to get the results manually by just entering the commands every time.
I have one query about the text file that we need to create for getting the results for particular users. which fileds should i enter in the text file.
Thanks
Devang
Display name, alias, or UPN should work. Take your pick. I usually just use display name.
Hi Paul,
Can u make this script work with server parameter? So it can list all the mailboxes from the server at once.
Thanks,
Sorin.
Sure thing. Actually I’m already working on that, will post an updated when I’ve got it finished.
I’ve managed to do it by myself
and now works with server parameter.
I modified your script and it works perfect.
Many thanks Paul,
Sorin.
I’ve included a -server option in the latest version.
Script has had a major overhaul and re-release, so much so that I’m just going to call this new one V1.0
question. is it possilbe to script a way to find all emails with attachments over 25meg.
thanks
Not with this script, but perhaps with a script that utilizes Exchange Web Services.
thank Paul
if you have time to point me in a direction of how to write that script…that would be great.
this one is really good too.
I haven’t done any EWS script development yet, so I don’t have anything to offer you right now. Although you’re not the first person to ask me for a script like that, so I think its time I started to get into it
But in the meantime, to learn about using EWS the first place I’d go to is Glen Scales’ blog here:
http://gsexdev.blogspot.com.au/
Hi Paul,
That script very useful, very well documented as well. I’ll try to improve it adding if the user has mailbox default storage limits.
Thanks for sharing!
hi
good work paul, thanks for sharing,
Paul
I have an issue where the powershel is preventing my from runnin gthe script. ” the file is not digitally signed” is the complaint from the PS!!
scratch my last post. Its early in the morning here………………
Hi Kevin, try changing your execution policy to unrestricted.
Set-ExecutionPolicy Unrestricted
Keep getting an error ….
“Missing opening “(” after keyword ‘for’.
At C:\Scripts\get-mailboxreport.ps1:48 char:5
+ For m <<<< ore script details, a video demo, or to give feedback please go to:
Am I missing something?
Exch 2007
Hi Josh, it is written for PowerShell v2.0. Try running:
$host
…to see which version you’ve got. If you’re on version1.0 you can try and edit the script to remove all that help info at the start, and see if that works, but I haven’t specifically tested it on v1.0 to check for any other issues.
Excellent script! Thanks for the work and the updates. I know that this will be used much by myself and the consultants I work with.
Thanks again
Getting this error message.
[PS] C:\temp\Exchange\PowerShell\other>.\Get-MailboxReport.ps1 -database MBX1VP\MbxDb-YNHH-002
Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your
computer. Do you want to run C:\temp\Exchange\PowerShell\other\Get-MailboxReport.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is “D”): R
Cannot process argument transformation on parameter ‘Database’. Cannot convert value “MBX1VP\MbxDb-YNHH-002″ to type “M
icrosoft.Exchange.Configuration.Tasks.DatabaseIdParameter”. Error: “‘MBX1VP\MbxDb-YNHH-002′ is not a valid value for th
e identity.
Parameter name: Identity”
+ CategoryInfo : InvalidData: (:) [Get-Mailbox], ParameterBindin…mationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-Mailbox
Which version of Exchange?
Try supplying the -database value as “server\storagegroup\database” if you’re running Ex2007.
Never mind. Got it now. Still getting this message:
Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your
computer. Do you want to run C:\temp\Exchange\PowerShell\other\Get-MailboxReport.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is “D”): R
I ran Set-ExecutionPolicy Unrestricted
May also need to right-click the downloaded file, and in properties unblock it.
Paul;
Great script! I removed a few of the output fields (we only have one mail server and one mail database, so those fields weren’t meaningful for us). At the moment I am trying to sort the resulting csv file via the script to put the mailboxes in order largest to smallest. Could use some help getting this to work.
Once I’ve got that figured out, this script is going to save me a ton of time. I’ve got it running as a schedlued task on my exchange server and emailing me the resulting csv on the first of every month.
In the last Else block try adding a Sort-Object to the $report | Export-CSV line like this:
$report | Sort-Object “Size (Mb)” -Desc | Export-Csv -Path $reportfile -NoTypeInformation
I am trying to run this script using the -database or -file parameter and it’s generating a blank csv file. The csv file generated is of 0 bytes.
That will happen if no mailbox info could be retrieved.
For -database make sure you’re supplying a value that would work if you ran “Get-Mailbox -database“.
For -file try removing the -ignoredefaultscope switch from the script line
“if($file) { $mailboxes = @(Get-Content $file | Get-Mailbox -resultsize unlimited -IgnoreDefaultScope) }”
I’ve added some script logic so that instead of creating a 0kb file it will instead show a warning that no mailboxes were found matching the criteria given.
Well, I tested the -database parameter works with .\GetMailboxReport.ps1 -database ‘ServerName\StorageGroupName\DataabaseName’
However, .\GetMailboxReport.ps1 -file does not work. I tried Display Name, Primary SMTP address, UPN but it doesn’t work. It creates a blank 9 byte csv file.
Ah yep, see comment above. Try removing -ignoredefaultscope from the line:
“if($file) { $mailboxes = @(Get-Content $file | Get-Mailbox -resultsize unlimited -IgnoreDefaultScope) }”
I’ll add it to my bug list.
Fantastic! It worked. Great script.
Thanks!
Hi Paul,
Great report for Migrations. As I need the Alias to perform the migration, I have added this in the first column:
$userObj | Add-Member NoteProperty -Name “Alias” -Value $mb.Alias
I also added sort “Size (Mb)” -Desc
I did run this on a combined DC and Exchange 2007 SP3 Server – Enabled and Expires is blank. OK on other Exchange 2007 SP3 Server, so seems issue having DC and Exchange 2007 together..
Could you try commenting out the error suppression lines at the start of the script and see if it produces any errors on the combined DC/Exchange server?
Thanks again paul for an amazing time saver(and also teaches me powershell)
i only had one issue when i ran it through 2010 ps(dont know if it matters) using -file
i got cannot move something about distinguage name cannot be used or must be used:) when using -ignorescope
so i removed the ignorescope for the file part of the script and it ran like a charmer:)
Thanks again
Much appreciated
Yep, little bug I need to work on there. If you have a multi-domain forest you’d just need to put your management shell session into viewentireforest $true mode to see all the mailboxes in the report.
Or if you’re a single domain forest it doesn’t matter, script will work as you’ve modified it
This bug should be fixed now in the latest version (1.1).
Hello Paul,
Thanks again för sharing your script. Works great, I just have a question… How can I do to find the right character in the csv file .. I m having in my exchange swedish names with those: å,ä,ö.
Is it a parameter to add ?
Sorry, I don’t understand the question.
When Im having a look at the csv file after I ran the script,
If for example the name of the user is Åsa Bergström, the result in the csv file is ?sa Bergstr?m
Its not the right character, I would like to get on my csv file the swedish characters like : the å the ä and the Ö instead of “?”
Hope I m more clear now?
Ah, I understand now. Not sure of the solution though, I’ll see if I can find something.
Nice report. I’d like to put it into a scheduled task for a weekly/monthly report that’s emailed to me. I’m wondering how to call upon the output file as it will change each time it’s ran.
Hi Christa, probably two ways you could do that.
1) Remove the file name randomization. So instead of
$reportfile = “MailboxReport-$timestamp-$random.csv”
have simply
$reportfile = “MailboxReport.csv”
2) The file name is stored as $reportfile, so if you’re adding email functionality to the script you can always reference the file name by that variable, no matter what the real name is.
I’ve added a -filename optional parameter to the latest version (1.1) for those who wish to specify the file name instead of the randomly generated ones (actually I use -filename almost every time I run it now
)
I guess I am a retard because I can’t get it to run. I keep getting the following error:
[PS] C:\Windows\system32>c:\scripts\get-Mailboxreport.ps1
The term ‘c:\scripts\get-Mailboxreport.ps1′ is not recognized as the name of a cmdlet, function, script file…..
What am I doing wrong? I am not an expert with PowerShell but this shouldn’t be this hard. thanks
Hi Tom,
Navigate to the folder where you’ve saved the script file, then run it with:
.\Get-Mailboxreport.ps1
You’ll need to supply one of the parameters for which type of report you want to run. You can see the help info by running:
Get-Help .\Get-MailboxReport.ps1
Or watch the video above for a demonstration.
Hope that helps.
I had already tried it that way but I ran it again. See below. I get the same error. It doesn’t seem to matter how I run it. Can you help further? This is driving me nuts. I will watch the video as well.
[PS] C:\scripts>.\Get-MailboxReport.ps1
The term ‘.\Get-MailboxReport.ps1′ is not recognized as the name of a cmdlet, function, script file,…….
thanks
You’ve downloaded the Zip file and extracted the PS1 file to C:\Scripts I take it?
Great script, but I am seeing something odd when I use the -server parameter. The number of mailbox objects listed in the EMC is 2498, but when I use the -server parameter it only processes about 1400 or so, not the full 2498. To get around that for now, I am using a generated user.txt file and using the -file parameter. Any ideas why the difference ?
Thanks
Are all the mailbox users in the same domain in AD, or different domains?
We only have one AD domain.
I recently tried the updated version and that seems to work just fine.
Thanks again
thz a lot fr the script, very useful
FYI I wrote a similar in concept script to generate Exchange 2010 mailbox billing reports in Excel directly. While it is a slightly different focus than what you are doing (in that it grabs all customer mailboxes or specific OUs, not databases or servers), perhaps you could recycle the Execl logic in my script to dump your data directly into Excel spreadsheet where you have already pre-formatted the attributes the way you want (which we also had to do) and save yourself a step or two:
http://gallery.technet.microsoft.com/Exchange-Mailbox-Billing-a33bcbec
I hope it helps.
Thanks for the great script Paul. I’ve removed a few of the objects I don’t need, and plan to schedule this to be exported to a SQL server so we can import the results into a database and report on mailbox growth per user.
I’ve changed your script to remove the random number being added to the file name, but if you can give me some advice on how to alter the export location that would be fantastic as i’d like the file exported to a folder that will have everything in it xcopied to another location at set times.
Eg: C:\ExchangeReportExport
Cheers from Aus, and thanks again Shane.
Oh worked it out…
$reportfile = “C:\PSScripts\Copy\MailboxReport-$timestamp.csv”
easy
Yep, also I’ve added a -filename parameter to the latest version (1.1) just uploaded in the last few minutes if you wanted to grab that. It has a few other minor fixes as well.
thanks so much
works great. What do i need to change the output file name to contain the server name it was run against?
You can use the -filename parameter to specify your own file names.
Thank you for providing us this script, I run it fine from PS command but when I try to run it on a batch file it doesn’t fetch anything. Can you please help with the line to run it on a batch file?
powershell.exe -noexit c:\Temp\Get-MailboxReport.ps1 -all
I get
Collecting mailbox list
Collecting report data
No mailboxes were found matching that criteria.
Getting output below when running the scripts
Collecting mailbox list
Collecting report data
No mailboxes were found matching that criteria.
It’s working now in the batch file, I just added the command to load Exchange Management Shell in the PS script.
Thanks
Hi Paul
How can we use this script and add the User Mailbox Quota in , so that it also shows in the csv file
I have added to following values to show the Storage quotas as well
$userObj | Add-Member NoteProperty -Name “IssueWarningQuota” -Value $mb.IssueWarningQuota.Value.ToMB()
$userObj | Add-Member NoteProperty -Name “ProhibitSendQuota” -Value $mb.ProhibitSendQuota.Value.ToMB()
$userObj | Add-Member NoteProperty -Name “ProhibitSendReceiveQuota” -Value $mb.ProhibitSendReceiveQuota.Value.ToMB()
I have added these as well:
$userObj | Add-Member NoteProperty -Name “Primary SMTP Address” -Value $mb.PrimarySMTPAddress
$userObj | Add-Member NoteProperty -Name “Forwarding Address” -Value $mb.ForwardingAddress
$userObj | Add-Member NoteProperty -Name “Hidden From Address Lists” -Value $mb.HiddenFromAddressListsEnabled
Make sure that in your “Select=-Object” line (154 in the latest version), that you add these attributes into your select statement. For example:
$stats = $mb | Get-MailboxStatistics | Select-Object TotalItemSize,TotalDeletedItemSize,ItemCount,LastLogonTime,LastLoggedOnUserAccount,HiddenFromAddressListsEnabled,ForwardingAddress,PrimarySMTPAddress
This could be helpful. Is it possible to have the script run against just “room mailboxes” ?
Tim,
Go to line 75, and add this line underneath:
[Parameter(ParameterSetName='room')] [switch]$room,
Go to line 139, and add this underneath:
if($room) { $mailboxes = @(Get-Mailbox $mailbox -RecipientTypeDetails RoomMailbox ) }
Save it and run it with the -room parameter. I tested this and it works.
Thanks BR – put doesnt the switch (-mailbox, -database -server, etc) specify where to pull the mailbox information from? Therefore using ‘-room’ switch it would have to be followed by the actual room ? I am trying to pull all rooms from a database or server.
Unless I am missing something. Thanks again !
The mailbox type us already included in the report, so you could just run it without modifications and use Excel to filter the data afterwards.
Another way would be to generate the list of room mailboxes into a text file and use the -file parameter when running the script.
Or, yet another way, make a copy of the script and call it say “Get-RoomMailboxReport.ps1″ and as BR suggests above change all the Get-Mailbox commands to add the “-RecipientTypeDetails RoomMailbox” parameter.
If you are getting the “No mailboxes were found matching that criteria.” message, make sure you are running the script in the Exchange Management Shell, this fixed it for me on Exchange 2007 and 2010.
Many thanks for this script, saved me many hours and lots of fingernails!
Hi Paul,
I have added one Ps1 file which is calling\having one function. I’m able to run the function( ex: get-Mail….). All is well.
Can you help me to know, how to remove the Ps1 file and the function Get-Mail… in my exchange server environment. I don’t want others to run this shell command, whenever i need the details, i ll run the script and get the data.
Help me to remove the function and PS1 file in safe manner
I don’t understand your question.
I followed this article to use get-databasestatistics function. I’m able to use this EMS shell command.
http://www.mikepfeiffer.net/2010/03/exchange-2010-database-statistics-with-powershell/
I want to remove the new Get-Databasestatistics command from my exchange environment. If I remove the ps1 file from the scripts folder, will this shell command get removed? or do i need to un register the script in someway…? Kindly advice
Thank you
Yes, if you delete a script file you won’t be able to run that script any more.
Why on earth would you want to remove that cmdlet? You know you can restrict who can run what cmdlet, right?
Kickass script Paul! Thanks for sharing.
Awesome script. Can you add functionality to show whether Exchange Archiving is enabled and the Total Size and Total Items in the archive? So far it seems the best way to check if archiving is enabled is to use Get-Mailbox | Select-Object ArchiveDatabase. Someone please correct me if I am wrong though. A value of null means archiving is disabled. I added some lines to the script and this worked.
When I tried to use Get-MailboxStatistics -Archive | Select-Object TotalItemSize,ItemCount this seems to work, but if the mailbox doesn’t have archiving enabled it throws out an error. I’m new to powershell scripting so I don’t know the best way to add this to the script so it’s handled gracefully.
Thanks!
I do not normally post comments on any website, but I just felt I had to this time!
Thank you – you have made my job about a milliontimes easier with this script…and such a simple thing it is too when you delve into it.
Why I had not thought of doing this sort of thing myself before I will never guess…but today, yes today Paul, you have made a new friend for life!
Once again, thank you mate!
I am not familiar with Exchange. When I run the script I get these error:
Can you help me find the missing links?
PS C:\Mailboxes> .\Get-MailboxReport.ps1 -database ‘PWRExchange\PWRE_MBX_2-2′
Get-PSSnapin : No Windows PowerShell snap-ins matching the pattern ‘Microsoft.E
xchange.Management.PowerShell.Admin’ were found. Check the pattern and then try
the command again.
At C:\Mailboxes\Get-MailboxReport.ps1:89 char:27
+ $2007snapin = Get-PSSnapin <<<< -Name Microsoft.Exchange.Management.PowerShe
ll.Admin
+ CategoryInfo : InvalidArgument: (Microsoft.Excha…owerShell.Ad
min:String) [Get-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : NoPSSnapInsFound,Microsoft.PowerShell.Commands.G
etPSSnapinCommand
Get-PSSnapin : No Windows PowerShell snap-ins matching the pattern 'Microsoft.E
xchange.Management.PowerShell.E2010' were found. Check the pattern and then try
the command again.
At C:\Mailboxes\Get-MailboxReport.ps1:96 char:28
+ $2010snapin = Get-PSSnapin <<<< -Name Microsoft.Exchange.Management.Powe
rShell.E2010
+ CategoryInfo : InvalidArgument: (Microsoft.Excha…owerShell.E2
010:String) [Get-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : NoPSSnapInsFound,Microsoft.PowerShell.Commands.G
etPSSnapinCommand
Import-Module : The specified module 'ActiveDirectory' was not loaded because n
o valid module file was found in any module directory.
At C:\Mailboxes\Get-MailboxReport.ps1:124 char:14
+ Import-Module <<<< ActiveDirectory
+ CategoryInfo : ResourceUnavailable: (ActiveDirectory:String) [I
Please Help!
@Kalvin – it looks like you are running that script from within a regular PowerShell console. You either need to load the Exchange snap-ins into that console, or instead run the Exchange Management Shell version of the PowerShell console (preferably IMHO). The Exchange Management Shell is installed on any computer you install the Exchange Administration tools on.
Happy hunting!
I am actually running it remotely and I am not in Exchange admin group. I have been given readonly access. Does that suffice or I need to do more?
Thanks!
Kalvin, as HotFix says, you will only be able to run this script either from with in the Exchange Management Shell version of the PowerShell console on an exchange server or from a computer that has the Exchange Administration tools installed onto it.
You can use the following command to load the Exchange Management tools in a script though, if that helps or you have problems fiding the Exchange console.
function Load_Exchange_Tools {
if (-not (Get-pssnapin | ? {$_.name -like ‘Microsoft.Exchange.Management.PowerShell.e2010′})) {
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.e2010
}
}
If you don’t have access to the tools, or your permissions don’t allow access to Exchange, you won’t be able to run scripts like this though.
Rgds, Shane.
This is brilliant, thanks
e.s. I changed the export command to $report | Export-Csv -encoding ‘unicode’ -Path $reportfile -NoTypeInformation – because here in Iceland we have alot of strange Icelandic characters like æ á þ ð ó and so on!
Nice work.
best regards, Bjarni
Excellent.
It seems that code
#This is an aged mailbox, so we want some extra details about the account
$user = Get-User $mb
isn’t exact
better is
$user = Get-User $mb.Alias
Seems to be working okay for me (although that comment about aged mailboxes doesn’t seem right, must have slipped in from another script).
Have you ever considered using a txt or csv file as input? I am currently evaluating Petri’s script for the same and can’t specify an input file. After analyzing your script, I find my problem as it is. Can you suggest me a way where I can specify a TXT or CSV file as input for users?
Thanks,
Animesh
The script has a -file parameter for using a txt file as the list of users to generate the report for. The txt file should contain the list of mailbox users in a format that would work with Get-Mailbox, so that could be alias, email address, display name, etc.
Hi thank u a lot for your wonderfull script.
I need to bublic it to may intranet site. How convert it in html formatted tablereport?
Could u modify the script to add html export?
I’m trying to modify it but I’m not able.
Thank you very much.
David.