Reader Faisal asks about retrieving a list of the top 30 mailboxes in order of size.
Finding the top (or largest) mailboxes in the Exchange Server organization is a fairly common requirement. I’m often asked to produce such lists by managers when issues of server capacity get raised.
In fact, it is why I wrote the Get-MailboxReport.ps1 script, which I recommend you download.
But, in the interests of learning about PowerShell, let’s also take a look at doing it manually. Fortunately this is quite a simple report to generate using PowerShell.
You might already be familiar with the Get-MailboxStatistics cmdlet, for example:
[PS] C:\>Get-Mailbox Alan.Reid | Get-MailboxStatistics DisplayName ItemCount StorageLimitStatus LastLogonTime ----------- --------- ------------------ ------------- Alan Reid 608 BelowLimit 6/27/2012 5:11:15 AM
Get-MailboxStatistics returns an object made up of various properties that describe the mailbox statistics for the mailbox. We can take a closer look at these properties using Get-Member.
[PS] C:\>Get-Mailbox Alan.Reid | Get-MailboxStatistics | Get-Member TypeName: Microsoft.Exchange.Data.Mapi.MailboxStatistics Name MemberType Definition ---- ---------- ---------- Clone Method System.Object Clone() Dispose Method System.Void Dispose() Equals Method bool Equals(System.Object obj) GetDisposeTracker Method Microsoft.Exchange.Diagnostics.DisposeTracker GetDisposeTracker() GetHashCode Method int GetHashCode() GetProperties Method System.Object[] GetProperties(System.Collections.Generic.ICollection[Microsoft.... GetType Method type GetType() SuppressDisposeTracker Method System.Void SuppressDisposeTracker() ToString Method string ToString() Validate Method Microsoft.Exchange.Data.ValidationError[] Validate() PSComputerName NoteProperty System.String PSComputerName=ho-ex2010-mb1.exchangeserverpro.net RunspaceId NoteProperty System.Guid RunspaceId=8da06b60-9483-4d24-9930-13567c0fd94e AssociatedItemCount Property System.Nullable`1[[System.UInt32, mscorlib, Version=2.0.0.0, Culture=neutral, P... Database Property Microsoft.Exchange.Data.ObjectId Database {get;} DatabaseName Property System.String DatabaseName {get;} DeletedItemCount Property System.Nullable`1[[System.UInt32, mscorlib, Version=2.0.0.0, Culture=neutral, P... DisconnectDate Property System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral,... DisconnectReason Property System.Nullable`1[[Microsoft.Exchange.Data.Mapi.MailboxState, Microsoft.Exchang... DisplayName Property System.String DisplayName {get;} Identity Property Microsoft.Exchange.Data.Mapi.MailboxId Identity {get;} IsArchiveMailbox Property System.Nullable`1[[System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, ... IsQuarantined Property System.Boolean IsQuarantined {get;} IsValid Property System.Boolean IsValid {get;} ItemCount Property System.Nullable`1[[System.UInt32, mscorlib, Version=2.0.0.0, Culture=neutral, P... LastLoggedOnUserAccount Property System.String LastLoggedOnUserAccount {get;} LastLogoffTime Property System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral,... LastLogonTime Property System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral,... LegacyDN Property System.String LegacyDN {get;} MailboxGuid Property System.Guid MailboxGuid {get;} MailboxTableIdentifier Property System.String MailboxTableIdentifier {get;} MapiIdentity Property Microsoft.Exchange.Data.Mapi.MapiObjectId MapiIdentity {get;} MoveHistory Property System.Object MoveHistory {get;} ObjectClass Property Microsoft.Exchange.Data.Mapi.ObjectClass ObjectClass {get;} OriginatingServer Property Microsoft.Exchange.Data.Fqdn OriginatingServer {get;} ServerName Property System.String ServerName {get;} StorageLimitStatus Property System.Nullable`1[[Microsoft.Exchange.Data.Mapi.StorageLimitStatus, Microsoft.E... TotalDeletedItemSize Property Microsoft.Exchange.Data.Unlimited`1[[Microsoft.Exchange.Data.ByteQuantifiedSize... TotalItemSize Property Microsoft.Exchange.Data.Unlimited`1[[Microsoft.Exchange.Data.ByteQuantifiedSize...
Of particular interest if we want to find the largest mailboxes is the TotalItemSize property. We can sort on that property using Sort-Object, and then use Select-Object to only return the top X number of results.
As a one-liner this breaks down as follows:
- A Get-Mailbox or Get-MailboxDatabase depending on the scope of our investigation
- The output from either of the above cmdlets piped into Get-MailboxStatistics
- The output from Get-MailboxStatistics piped into Sort-Object
- Select-Object to return only the desired number of results, and optionally to return only the specific properties we want to see in our final output
- Optionally again, an export to file
Example #1 – Select top 30 mailboxes by totalitemsize
[PS] C:\>Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize -First 30 DisplayName TotalItemSize ----------- ------------- Administrator 3.495 MB (3,665,004 bytes) Alan Reid 2.893 MB (3,033,472 bytes) TestMB HO 1.818 MB (1,906,318 bytes) Help Desk 1.795 MB (1,882,379 bytes) Test Mailboxey 1.659 MB (1,739,954 bytes) TestMB BR 1.657 MB (1,737,479 bytes) Alannah Shaw 1.291 MB (1,353,920 bytes) Diane Hall 1.278 MB (1,339,655 bytes) Melanie Thomas 1.235 MB (1,294,958 bytes) Charlotte Bonsey 1.233 MB (1,293,409 bytes) Chris Majumdar 1.218 MB (1,277,482 bytes) Caroline Ball 1.213 MB (1,271,477 bytes) Jane Martin 1.206 MB (1,264,527 bytes) Ferzana King 1.206 MB (1,264,310 bytes) Hilary Greenall 1.192 MB (1,250,258 bytes) Alex Heyne 1.185 MB (1,242,700 bytes) Sue Chandarana 1.177 MB (1,234,595 bytes) Ana Williams 1.174 MB (1,231,015 bytes) Hasu Janjuh 1.173 MB (1,229,980 bytes) Davina Nsiah 1.169 MB (1,225,418 bytes) Valerie Andrews 1.168 MB (1,224,599 bytes) Michael Phillips 1.167 MB (1,223,914 bytes) Bob Winder 1.166 MB (1,222,496 bytes) Sandra Watson 1.164 MB (1,220,810 bytes) Michelle Stevenson 1.164 MB (1,220,166 bytes) Andrea Sharma 1.163 MB (1,219,437 bytes) Hilary Seaman 1.161 MB (1,217,606 bytes) Joy Singh 1.16 MB (1,216,872 bytes) Yvonne Bardall 1.154 MB (1,210,163 bytes) Sue Andress 1.153 MB (1,209,401 bytes)
Example #2 – Select top 100 mailboxes by totalitemsize and export to CSV file
[PS] C:\>Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize -First 100 | Export-CSV top100mailboxes.csv
The resulting CSV file:
Hopefully that answers your question Faisal, but once again you’re welcome to download and use the Get-MailboxReport.ps1 script for this type of mailbox size reporting.
Hi, i’m trying to generate a list of mailboxes by their size from a SPECIFIC DAG or from a list of specific databases.
any ideas?
How to get the mailbox databases with a default quota usage as 2GB sorted based on the mailbox count of the database. Any Idea…
how can i Get a List of the Top Exchange Server Mailboxes by Size + alias
Muito obrigado! Mestre Jedi!
What about getting top 25-50 and for each creating a new-move request?
Thank you Paul for sharing your knowledge.
Do you know why , if I run the script on my server, the TotalItemSize is displayed in Byte and not in MegaByte ??
Great article!
Quick question: how should I script out top 10 mailboxes that do not have archive enabled?
Hey Paul…love the site and all your hard work! Keep it up.
Just wanted to say that I looked everywhere to try and figure out how to do this by reading in a list of users from a text file. I couldn’t find anything, so I finally sat down to figure it out myself.
The first part is easy. Just do this:
$UserList = Get-Content ‘C:\Temp\Users.txt’
It took me a while but I finally figured out the second half of it:
$Values = ForEach ($User in $UserList) {(Get-Mailbox -Identity $User | Get-MailboxStatistics) | Select DisplayName, TotalItemSize, ItemCount}
Now I can sort it based on any one of the values by doing this:
$Values | Sort TotalItemSize -Descending | FT -AutoSize
Hi Paul,
Is it possible to have a PowerShell script to capture the top 10% of all user mailbox and their associated database to a table?
Currently I have to manually find their associated database after get the top 10 result.
Thanks,
The Real Person!
The Real Person!
I’d just use this script and then sort/filter in Excel.
https://www.practical365.com/powershell-script-create-mailbox-size-report-exchange-server-2010
Hi Paul,
How to get space usage statistics per year by each mailbox database?
thanks
The Real Person!
The Real Person!
Can’t do it with any of the builtin PowerShell cmdlets, but you can use EWS to pull the age of items and summarise them into per-year stats. Look at Glen Scales blog for lots of EWS code samples.
Hi Paul,
when connecting to Office 365 and checking user mailbox size the script does not return the sort order as it does with OnPremise Exchange.
Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize
Do you have a solution for that?
The Real Person!
The Real Person!
As I understand it, it’s because in a remoting session the results come back with that attribute as a string, which sorts differently. I have a script that isn’t ready to share yet that converts the strings to KB/MB/GB values for sorting. I don’t know if it’s the best solution.
Just spent a while trying to get this to run remotely and then finally scrolled down this far to find the answer! If anyone else gets this far follow Paul’s advise above and run this ON THE SERVER no via a remote session!
Hi, my Exchange 2007 server is old and all we have. Is it ok to run the command for the get top 30 mailboxs without impact on the server? Please advise.
Thank you
The Real Person!
The Real Person!
Install the Exchange management tools on your workstation or an admin server, and run it from there. Shouldn’t impact server at all that way.
Nice beginning, but the script doesn’t really do what’s promised.
90mb mailboxes are listed before 3gb mailboxes.
I realise this isn’t your fault, but it is problematic…
The Real Person!
The Real Person!
Are you using PS Remoting or are you using the Exchange Management Shell?
When I run this command I receive the error “The total data received from the remote client is exceeded allowed maximum. Allowed maximum is 524288000” This seems to be a hard coded limit. I would appreciate any suggestions that you have for splitting this up so I can successfully retrieve the top 100 largest mailboxes from a 20,000 user environment.
Many Thanks Chris
Hi
I get the following on EAC when trying to run the following
WARNING: The object blueturtle.local/Users/CAS_{3128192dfd6a4100} has been corrupted, and it’s in an inconsistent
state. The following validation errors happened:
WARNING: Database is mandatory on UserMailbox.
WARNING: Database is mandatory on UserMailbox.
Is this an issue i should be concerned about?
The Real Person!
The Real Person!
Yes.
Pingback: Exchange Server: Thống kê mail box size và gá»i email cho admin | Me, IT and Tech
Hi Paul,
I have a group in Active directory and each member having mailbox, I would like to get the mailboxsize for that group. Is there any command I can get the the mailbox size for the members added to that group.
Regards,
Adeel
hi paul,
I am running MSX 2007 SP1 on a windows 2008 enterprise box.
I need help in getting an excel report that will display
Display Name Primary SMTP Item Count Total Item Size
in descending order of “total item size”
Additionally I want to add a filter to get only of a specific accepted domain mailboxes to show in the output.
Kindly help me generate this report.
Thank You
Using Power Shell is like moving from windows back to cmd line….any idea why MS wants to regress? cmd line does not do anything except slow people down…useless
dear power shell….thank you for encouraging me to learn something that will perish in the future….here goes more useless info to forget later….
knowledge from msdos, win95-98-2k-xp-server 2k-2k3…RIP…
Heh, a late reply ,, but I couldn’t help myself…
Enjoy creating 100 users and mailbox with the GUI.
I’ll do it in 20 seconds through PowerShell, and take the rest of the day off…
PowerShell is moving forwards into the future. Some of us never stopped using the power of the command line…
Hello, thanks for your support. I have a question: in my Exchange organization, i have more domains and the query it extract it give me only one domain, the principal. How i can extract all mailbox for all domains?
I solve it!!! if you add to Get-Mailbox the option -DomainController DC01, where DC01 is the domain controller for the domain that we want extract mailbox size, we obtain the list only for that domain.
[PS] C:\>Get-Mailbox -DomainController DC01 -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize -First 100 | Export-CSV top100mailboxes.csv
Hi Paul,
How to get data of all mailboxes sizes which are having more than 900 MB in size.
Thanks in advance.
Problem is, Get-MailboxStatistics output just a display name – not unique and cant really be used as such. The other half of the information you need is in Get-Mailbox :- found answer in following link. once you have the data on all your mailboxes, and the output can be IMPORTED into excel you can do all your sorting etc and delete whatever you dont want
***if this is what you were looking for, please click this link and give the guy some credit.. i didnt come up with this, i just found it
#REM http://www.experts-exchange.com/Software/Server_Software/Email_Servers/Exchange/Q_27828458.html
$Mailboxes = Get-Mailbox -ResultSize Unlimited
foreach ($Mailbox in $Mailboxes)
{
$Mailbox | Add-Member -MemberType “NoteProperty” -Name “MailboxSizeMB” -Value ((Get-MailboxStatistics $Mailbox).TotalItemSize.Value.ToMb())
}
$Mailboxes | Sort-Object MailboxSizeMB -Desc | Select PrimarySMTPAddress, MailboxSizeMB
#REM – to export this out — do the following 😉 enjoy (see the part where it says “Select” you can add additional fields like ALIAS etc to this)
$Mailboxes = Get-Mailbox -ResultSize Unlimited
foreach ($Mailbox in $Mailboxes)
{
$Mailbox | Add-Member -MemberType “NoteProperty” -Name “MailboxSizeMB” -Value ((Get-MailboxStatistics $Mailbox).TotalItemSize.Value.ToMb())
}
$Mailboxes | Sort-Object MailboxSizeMB -Desc | Select PrimarySMTPAddress, MailboxSizeMB | Export-Csv -NoType “C:tempMailboxessize.csv”
Paul,
This is great information. I found what I needed by concatenating information from another resource and your examples here. What I needed was the top mailboxes sizes in a specific database. Sometimes a database gets much larger than the others. I look for the largest mailboxes and then move them to a younger database. This is what I came up with:
Get-MailboxDatabase “yourDBnameHere” | Get-MailboxStatistics | Sort totalitemsize -desc | Select-Object DisplayName,TotalItemSize -First 30
Hi,
Is there a way to combine Get-Mailbox & Get-MailboxStatistic?
We are after:
The DisplayName, LastLogonTime from Get-MailboxStatistic. &
ForwardingAddress, ProhibitSendReceiveQuota from Get-Mailbox.
Instead of generating two different report, is there a way to combine these parameters from two different cmdlets into one report?
Thanks
The Real Person!
The Real Person!
Yes, you can do this using custom objects. I use that technique in the Get-MailboxReport.ps1 script here:
https://www.practical365.com/powershell-script-create-mailbox-size-report-exchange-server-2010/
(it doesn’t grab exactly those attributes you’ve listed but if you look at the code you should see how you can easily adjust it to get whatever info you need)
Thanks Paul. I will give it a try
I tried .get-mailboxreport.ps1 –database foxboro1 to export all mailbox name from database, but i got an error
get-mailboxreport.ps1 is not digitally assigned! can you please help
I just need to export mailbox names within certain database with mailbox size
Thanks
The Real Person!
The Real Person!
You need to change your execution policy to allow unsigned scripts.
http://technet.microsoft.com/en-us/library/ee176949.aspx
Can I query for these values OldestItemReceivedDate & NewestItemReceivedDate in Get-MailboxStatistics?
The Real Person!
The Real Person!
Get-MailboxFolderStatistics has some parameters for collecting that info.
Big fan,another great work Paul!
Thanks for the great info, Paul. I always look to you first when I need to know/learn how to do something in Exchange and PowerShell
Hi Paul,
I have an exchange 2003 with all the boxes and a new Exchange 2010 and i did the command on the 2010 and the result was great but do not include the 2003 servermail boxes infos…
Is there a way to add them simply on the same script ?
Regards
The Real Person!
The Real Person!
You may want to look into a script such as this one:
http://blogs.technet.com/b/gary/archive/2009/09/16/list-mailbox-sizes-for-both-exchange-2003-and-exchange-2007.aspx
Hi Paul,
Where does the “csv” file goes to after running this command……….
Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize -First 100 | Export-CSV top100mailboxes.csv
Thanks,
Wasim
The Real Person!
The Real Person!
It will be written to the same folder you’re in when you run the command.
Pingback: 6 tips for keeping users’ Exchange Mailbox sizes down | BackupAssist: The Windows Backup Software
Hi,
I am trying to find a command that will return the user and total item count for the inbox only.
Can this be done?
Thanks
Becky
The Real Person!
The Real Person!
Hi Rebecca, try this:
https://www.practical365.com/reporting-mailbox-folder-sizes-with-powershell/
Close, but no cigar. The info for “Microsoft.Exchange.Data.Mapi.MailboxStatistics.TotalItemSize” indicates that it is of type “Microsoft.Exchange.Data.Unlimited”.
This is a problem, because sometimes the value is the string “unlimited”, sometimes GB, sometimes MB, and usually displayed in a way that does not lend itself to importing into Excel.
(Having both the string, MB and unit count in the same column is very poor.)
So what .Net methods can we invoke to return just a plain byte count?
The Real Person!
The Real Person!
Not sure which commands you’re running to get that error, but if you’re looking for more useful formatting of the mailbox stats/data I suggest using Get-MailboxReport.ps1 script here:
https://www.practical365.com/powershell-script-create-mailbox-size-report-exchange-server-2010
hi Paul
i have use these PS command and got the desire result, but in addition to DISPLAY NAME i want add another Attribute like UserlogonName when exacting the result, i did that with UserLogonName but i unable to fetch the UserLogonName attribute.
The Real Person!
The Real Person!
I recommend you grab the Get-MailboxReport.ps1 script here:
https://www.practical365.com/powershell-script-create-mailbox-size-report-exchange-server-2010
Thanks Paul 🙂