One-liner to export mailbox size, quotas and more to a CSV file

2011-10-19 34 Comments

I got a question form a friend if I could help and sort out a command that exported mailbox size and quotas to a CSV file for him. This should work for both Exchange Server 2007 and 2010, here is how we did it:

First run a get mailbox command:

Get-Mailbox -ResultSize Unlimited

Then we add a pipe and a number of attributes we want to get:

Get-Mailbox -ResultSize Unlimited | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota

We continue with adding two that performs Get-MailboxStatistics to receive attributes from the mailbox that the Get-Mailbox Cmdlet does not give us:

@{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}} and @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}

Then we add another attribute that Get-Mailbox gives us:

Database

And to finish it off we export the results to a CSV file after another pipe:

| Export-Csv “UserMailboxSizes.csv” –NoTypeInformation

And the complete command again with all parts combined together:

Get-Mailbox -ResultSize Unlimited | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label="TotalItemSize(MB)";expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv "C:\Scripts\UserMailboxSizes.csv" -NoTypeInformation

This command can of course be modified and you can add other attributes or functions. In the following example I use where to get only the mailboxes that does not use the database default quota.

Where {$_.UseDatabaseQuotaDefaults -eq $false

And the complete command:

Get-Mailbox -ResultSize Unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label="TotalItemSize(MB)";expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv "C:\Scripts\UserMailboxSizes.csv" -NoTypeInformation

There you go, enjoy and do not hesitate to let me know if you have any questions!

Filed under Exchange 2007, Exchange 2010 Tagged with 2007, 2010, csv, exchange, export, mailbox, one-liner, powershell, quota, size

34 Responses to One-liner to export mailbox size, quotas and more to a CSV file

G.B says:

2012-01-21 at 00:02

after exporting where is the file stored?

Reply
- Martin Sundström says:
  
  2012-01-23 at 10:00
  
  Per default when you don’t specify a path the files is stored in %SYSTEMROOT%\System32. I suggest that you specify a path, for example:
  
  Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
  
  Reply
  - Derrick says:
    
    2016-04-06 at 17:14
    
    Hello, I am running your full command Get-Mailbox -ResultSize Unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
    
    But the TotalItemSize(MB) ItemCount are empty, how was this fixed?
Paul says:

2012-02-22 at 17:04

good oneliner

Reply
- Martin Sundström says:
  
  2012-02-23 at 10:26
  
  Thanks for your feedback, and thanks for reading!
  
  Reply
Gus Arman says:

2012-06-18 at 13:58

I have question guys, first thanks for the great command.
but it is reqiured from me to filter these outputs based on users department can we do that

Reply
- Martin Sundström says:
  
  2012-06-25 at 10:16
  
  When you say filter, does that mean that you only want to run the command for one department? If so, run the following command and add your department name:
  
  Get-Mailbox -ResultSize Unlimited | Where {$_.Department -eq “” -and $_.UseDatabaseQuotaDefaults -eq $false | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
  
  You can also view the department information in the output by running the following command:
  
  Get-Mailbox -ResultSize Unlimited | Where {$_.Department -eq “” -and $_.UseDatabaseQuotaDefaults -eq $false | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
  
  Hope that helps, let me know if it doesn’t. Thanks for reading!
  
  Reply
  - andy says:
    
    2014-04-11 at 15:53
    
    I don’t think department is an obj member of get-mailbox. you have to pipe it from get-user
  - andy says:
    
    2014-04-11 at 16:00
    
    btw, I have the same problem as robb, only last row show the fields from get-statistics.
  - Shiva Sharma says:
    
    2014-07-29 at 19:14
    
    oh – thank you for this… also found similar in
    #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
    
    $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:\temp\Mailboxessize.csv”
Brendan says:

2012-07-31 at 02:57

Fantastic breakdown & showed me a problem with the export I was writing. Your final Where { seems to be missing the } and causes your last command to not execute.

Reply
- Erik says:
  
  2012-08-14 at 15:13
  
  Any day where the closing } has to go?
  
  Reply
  - Martin Sundström says:
    
    2012-08-14 at 15:39
    
    Sorry for the delay, I have now updated the final command and it should work. Thanks for Reading and letting me know!
Jason says:

2012-08-14 at 20:24

The totalitemsize part gives me an item size if I run it for a single user, but as soon as I don’t specify a mailbox and do this for everyone at once it doesn’t fill out this field.

Reply
- Steve Koivisto says:
  
  2012-09-24 at 16:37
  
  I’m seeing the same behavior. Exchange 2010 SP2
  
  Reply
- john says:
  
  2012-10-03 at 10:12
  
  Same here on 2010 SP2.
  All the Get-MailboxStatistics commands return data, but when exporting it to csv it does not fill the data, except for the last mailbox it is run on.
  
  Reply
- Robb says:
  
  2013-02-13 at 20:43
  
  Same here… all columns called by the @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}} are empty except the last row.
  
  I’ve spent at least a couple hours trying to determine what is causing this with nothing to show for it. I’m on Exchange 2010 SP2 UR4v2.
  
  Reply
Matt Levy (@skrods) says:

2012-09-26 at 10:26

Hi There, I can run the command and view the information on screen, but when I export-csv, I get:

“ClassId2e4f51ef21dd47e99d3c952918aff9cd”,”pageHeaderEntry”,”pageFooterEntry”,”autosizeInfo”,”shapeInfo”,”groupingEntry”
“033ecb2bc07a4d43b5ef94ed5a35d280″,,,,”Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo”,
“9e210fe47d09416682b841769c78b8a3”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,
“27c87ef9bbda4f709f6b4002fa4af63c”,,,,,

I have tried -encoding “unicode”/”UTF7″/”UTF8” and they all result in unreadable text.

My command is:

Get-Mailbox -ResultSize Unlimited |get-mailboxstatistics | Sort-Object -Property TotalItemSize | Format-Table DisplayName,TotalItemSize| Export-Csv “C:\Support\UserMailboxSizes.csv” -encoding “Unicode” -notype

What am I doing wrong?

I also noticed same behavior as Jason above.

Reply
- john says:
  
  2012-10-03 at 10:25
  
  you can try using “select-object” instead of “Format-Table”
  
  Reply
  - Jason says:
    
    2012-10-03 at 15:10
    
    Here is what we did to fix it. I found this script elsewhere and modified it to suit our purposes.
    
    # Initalize a dynamic array (table).
    
    # This table will hold these records (retrieved with these PowerShell commands)
    
    # DisplayName (Get-MailboxStatistics), OrganizationalUnit (Get-Mailbox), StorageGroupName
    
    # (Get-MailboxDatabase), ItemCount and TotalSize (Get-MailboxStatistics)
    
    $mbxStats = @()
    
    # Retrieve all mailboxes (you might have more than 1000, which is the default)
    
    $mailboxes=Get-Mailbox -resultsize unlimited| Where {$_.UseDatabaseQuotaDefaults -eq $false}
    
    # Loop through each mailbox
    
    $mailboxes | ForEach-Object {
    
    # Retrieve the identity and store it in the variable $_
    
    $stats = Get-MailboxStatistics -id $_
    $mail = get-mailbox -id $_
    
    # Create a new instance of a .Net object
    
    $mbx = New-Object System.Object
    
    # Add user-defined customs members: the records retrieved with the three PowerShell commands
    
    $mbx | Add-Member -MemberType NoteProperty -Value $stats.Displayname -Name DisplayName
    $mbx | Add-Member -MemberType NoteProperty -Value $mail.OrganizationalUnit -Name OrganizationalUnit
    $mbx | Add-Member -MemberType NoteProperty -Value $mail.database -Name Database
    $mbx | Add-Member -MemberType NoteProperty -value $mail.issuewarningquota -Name WarningQuota
    $mbx | Add-Member -MemberType NoteProperty -value $mail.prohibitsendquota -Name ProhibitSendQuota
    $mbx | Add-Member -MemberType NoteProperty -value $mail.ProhibitSendReceiveQuota -Name ProhibitSendReceiveQuota
    $mbx | Add-Member -MemberType NoteProperty -Value $stats.TotalItemSize.value.ToMB() -Name TotalSizeMB
    
    # Add right hand operand to value of variable ($mbx) and place result in variable ($mbxStats)
    
    $mbxStats += $mbx
    }
    
    # Pipe the result. Export to .csv file. Use Unicode format
    
    $mbxStats | Export-Csv -Encoding ‘Unicode’ c:\powershell\MailboxSizes.csv
Matt Levy (@skrods) says:

2012-11-02 at 07:27

Jason your script rocks. I was expecting to get the stats for all the mailboxes but I see you have the “Where {$_.UseDatabaseQuotaDefaults -eq $false}” condition so It’s only those mailboxes that are not using database default quotas. I was going to sort them out in excel after the export.

I tried Martin’s script again using “select-object” instead of “Format-Table” and I get readable text in my CSV file but still no data for
IssueWarningQuota :
ProhibitSendQuota :
TotalItemSize(MB) :
ItemCount :
Even if I view the output on screen. Oh well I get what I need from Jason’s script anyway.

Thanks to everyone, I’ve learnt a lot.

Reply
kizer_frame says:

2013-01-24 at 16:27

Thank’s, great code :)

Reply
- Martin Sundström says:
  
  2013-01-24 at 16:30
  
  I´m glad that you liked it :)
  
  Thanks for Reading and for the feedback!
  
  Reply
MarioTunes says:

2013-03-13 at 00:12

Hello, how to adjust it to work on Exchange 2010 SP2? When we get report, total item size and Item count are empty.
Thank you

Reply
- Martin Sundström says:
  
  2013-03-13 at 01:45
  
  Hi, the last command looks for the UseDatabaseQuotaDefaults property to be $false. It is $true by default, so if you have not changed it on any user the results should be empty. Try running the first complete command, it works just fine on my Exchange 2010 SP3.
  
  Thanks for Reading and let me know if it does not work.
  
  Reply
  - MarioTunes says:
    
    2013-03-13 at 17:02
    
    Hello Martin,
    
    I have adjust the script (named it MailboxStatisticsReport.ps1)and add send report by email:
    
    $AllMailboxes = @()
    $Mailboxes = Get-Mailbox -ResultSize Unlimited | Select DisplayName, Database, IssueWarningQuota, ProhibitSendQuota, ProhibitSendReceiveQuota, Alias
    foreach ($Mailbox in $Mailboxes){
    $MailboxStats = “” |Select DisplayName,Database,IssueWarningQuota,ProhibitSendQuota,ProhibitSendReceiveQuota,TotalItemSize,ItemCount,StorageLimitStatus
    $Stats = Get-MailboxStatistics -Identity $Mailbox.Alias
    $MailboxStats.DisplayName = $Mailbox.DisplayName
    $MailboxStats.Database = $Mailbox.Database
    $MailboxStats.IssueWarningQuota = $Mailbox.IssueWarningQuota
    $MailboxStats.ProhibitSendQuota =$Mailbox.ProhibitSendQuota
    $MailboxStats.ProhibitSendReceiveQuota =$Mailbox.ProhibitSendReceiveQuota
    $MailboxStats.TotalItemSize = $Stats.TotalItemSize
    $MailboxStats.ItemCount = $Stats.ItemCount
    $MailboxStats.StorageLimitStatus = $Stats.StorageLimitStatus
    $AllMailboxes += $MailboxStats
    }
    $AllMailboxes | Export-Csv G:\tools\MailboxStatisticsReport.csv -NoTypeInformation
    send-mailmessage -from “support ” -to “admin ” -subject “Mailbox size report” -body “Mailbox size report” -Attachments “G:\tools\MailboxStatisticsReport.csv” -priority High -smtpServer smtp.server.local
    
    To run it via Windows Task, I have created .bat file and put in Windows task.
    
    PowerShell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\V14\Bin\ExShell.psc1” -Command “. ‘G:\Script\MailboxStatisticsReport.ps1′”
    
    Thank you for your support
  - Derrick says:
    
    2016-04-06 at 15:17
    
    Hello, I am running your full command Get-Mailbox -ResultSize Unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
    
    But the TotalItemSize(MB) ItemCount are empty, how was this fixed?
  - Derrick says:
    
    2016-04-06 at 15:18
    
    Hello, I am running your full command Get-Mailbox -ResultSize Unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=”TotalItemSize(MB)”;expression={(Get-MailboxStatistics $_).TotalItemSize.Value.ToMB()}}, @{label=”ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation
    
    But the TotalItemSize(MB) ItemCount are empty, how was this fixed?
Robb says:

2013-03-20 at 21:13

Get more info using MarioTunes example:
$date = (Get-Date).toString(‘yyyy-MM-dd’);
$AllMailboxes = @()
$Mailboxes = Get-Mailbox -ResultSize Unlimited | Select Alias, DisplayName, Title, Department, OrganizationalUnit, LastLogonTime, Database, UseDatabaseQuotaDefaults, IssueWarningQuota, ProhibitSendQuota, ProhibitSendReceiveQuota, StorageLimitStatus, TotalItemSize, ItemCount, SingleItemRecoveryEnabled, UseDatabaseRetentionDefaults, RetainDeletedItemsFor, RetentionPolicy, ManagedFolderMailboxPolicy, ExchangeVersion
foreach ($Mailbox in $Mailboxes){
$MailboxStats = “” | Select Alias, DisplayName, Title, Department, OrganizationalUnit, LastLogonTime, Database, UseDatabaseQuotaDefaults, IssueWarningQuota, ProhibitSendQuota, ProhibitSendReceiveQuota, StorageLimitStatus, TotalItemSize, ItemCount, SingleItemRecoveryEnabled, UseDatabaseRetentionDefaults, RetainDeletedItemsFor, RetentionPolicy, ManagedFolderMailboxPolicy, ExchangeVersion
$Stats = Get-MailboxStatistics -Identity $Mailbox.Alias
$UserInfo = Get-User -Identity $Mailbox.Alias
$MailboxStats.Alias = $Mailbox.Alias
$MailboxStats.DisplayName = $Mailbox.DisplayName
$MailboxStats.Title = $UserInfo.Title
$MailboxStats.Department = $UserInfo.Department
$MailboxStats.OrganizationalUnit = $Mailbox.OrganizationalUnit
$MailboxStats.LastLogonTime = $Stats.LastLogonTime

$MailboxStats.TotalItemSize = $Stats.TotalItemSize.Value.ToMB()
$MailboxStats.ItemCount = $Stats.ItemCount
$MailboxStats.Database = $Mailbox.Database
$MailboxStats.UseDatabaseQuotaDefaults = $Mailbox.UseDatabaseQuotaDefaults
$MailboxStats.IssueWarningQuota = $Mailbox.IssueWarningQuota
$MailboxStats.ProhibitSendQuota =$Mailbox.ProhibitSendQuota
$MailboxStats.ProhibitSendReceiveQuota =$Mailbox.ProhibitSendReceiveQuota
$MailboxStats.StorageLimitStatus = $Stats.StorageLimitStatus

$MailboxStats.SingleItemRecoveryEnabled = $Mailbox.SingleItemRecoveryEnabled
$MailboxStats.UseDatabaseRetentionDefaults = $Mailbox.UseDatabaseRetentionDefaults
$MailboxStats.RetainDeletedItemsFor = $Mailbox.RetainDeletedItemsFor
$MailboxStats.RetentionPolicy = $Mailbox.RetentionPolicy
$MailboxStats.ManagedFolderMailboxPolicy = $Mailbox.ManagedFolderMailboxPolicy
$MailboxStats.ExchangeVersion = $Mailbox.ExchangeVersion

$AllMailboxes += $MailboxStats
}
$AllMailboxes | Export-Csv C:\temp\Audit_MailBoxes_$Date.csv -NoTypeInformation

Reply
- Robb says:
  
  2013-03-20 at 21:14
  
  Or here’s another way to do it:
  $date = (Get-Date).toString(‘yyyy-MM-dd’);
  $MailboxData = @()
  $mailboxes = Get-Mailbox -resultsize unlimited
  foreach ($mailbox in $mailboxes) {
  $DBObject = new-object PSObject
  $DBObject | add-member NoteProperty -Name Alias -Value $Mailbox.alias
  $DBObject | add-member NoteProperty -Name DisplayName -Value $Mailbox.DisplayName
  $DBObject | add-member NoteProperty -Name Title -Value (Get-User $mailbox.alias).Title
  $DBObject | add-member NoteProperty -Name Department -Value (Get-User $mailbox.alias).Department
  $DBObject | add-member NoteProperty -Name OrgUnit -Value $Mailbox.organizationalunit
  $DBObject | add-member NoteProperty -Name LastLogonTime -Value (Get-MailboxStatistics $mailbox.alias).LastLogonTime
  
  $DBObject | add-member NoteProperty -Name SizeMB -Value (Get-MailboxStatistics $mailbox.alias).totalitemsize.value.ToMB()
  $DBObject | add-member NoteProperty -Name Items -Value (Get-MailboxStatistics $mailbox.alias).itemcount
  $DBObject | add-member NoteProperty -Name Database -Value (Get-MailboxStatistics $mailbox.alias).databasename
  $DBObject | add-member NoteProperty -Name UseDBQuotaDefaults -Value $Mailbox.UseDatabaseQuotaDefaults
  $DBObject | add-member NoteProperty -Name IssueWarningQuota -Value $Mailbox.IssueWarningQuota
  $DBObject | add-member NoteProperty -Name ProhibitSendQuota -Value $Mailbox.ProhibitSendQuota
  $DBObject | add-member NoteProperty -Name StorageLimit -Value (Get-MailboxStatistics $mailbox.alias).storagelimitstatus
  
  $DBObject | add-member NoteProperty -Name SingleItemRecovery -Value $Mailbox.SingleItemRecoveryEnabled
  $DBObject | add-member NoteProperty -Name UseDBRetentionDefaults -Value $Mailbox.UseDatabaseRetentionDefaults
  $DBObject | add-member NoteProperty -Name RetainDeletedItemsFor -Value $Mailbox.RetainDeletedItemsFor
  $DBObject | add-member NoteProperty -Name RetentionPolicy -Value $Mailbox.RetentionPolicy
  $DBObject | add-member NoteProperty -Name ManagedFolderMBPolicy -Value $Mailbox.ManagedFolderMailboxPolicy
  
  $DBObject | add-member NoteProperty -Name ExchangeVersion -Value $Mailbox.ExchangeVersion
  
  $MailboxData += $DBObject
  }
  $MailboxData | Export-Csv “c:\temp\Audit_MailBoxes_$Date.csv” -NoTypeInformation
  
  Reply
manjunatha says:

2013-09-10 at 15:28

I need to add 2 attributes like – Email ID & Employee ID for the above Script.
Please let me know how to do that

Reply
Bill Neaveill says:

2013-10-29 at 21:04

I ran this script and it returns unlimited for all of my quotas but I have limits set. Any ideas ?

Reply
Honkyoq says:

2015-09-01 at 11:20

how to place this : sort-object -ascending TotalItemSize ?

Reply
Jinagouda says:

2017-10-28 at 21:40

How can I extract mailbox report with some more attributes like, last login, display name, database name, server name, total mailbox size, deleted item size, etc. from specific set of servers?

Reply