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

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!

Advertisements

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

  1. G.B says:

    after exporting where is the file stored?

    • Martin Sundström says:

      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

      • Derrick says:

        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?

  2. Paul says:

    good oneliner

  3. Gus Arman says:

    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

    • Martin Sundström says:

      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!

      • andy says:

        I don’t think department is an obj member of get-mailbox. you have to pipe it from get-user

      • andy says:

        btw, I have the same problem as robb, only last row show the fields from get-statistics.

      • Shiva Sharma says:

        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”

  4. Brendan says:

    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.

    • Erik says:

      Any day where the closing } has to go?

      • Martin Sundström says:

        Sorry for the delay, I have now updated the final command and it should work. Thanks for Reading and letting me know!

  5. Jason says:

    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.

    • Steve Koivisto says:

      I’m seeing the same behavior. Exchange 2010 SP2

    • john says:

      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.

    • Robb says:

      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.

  6. 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.

    • john says:

      you can try using “select-object” instead of “Format-Table”

      • Jason says:

        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

  7. 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.

  8. kizer_frame says:

    Thank’s, great code :)

    • Martin Sundström says:

      I´m glad that you liked it :)

      Thanks for Reading and for the feedback!

  9. MarioTunes says:

    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

    • Martin Sundström says:

      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.

      • MarioTunes says:

        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:

        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:

        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?

  10. Robb says:

    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

    • Robb says:

      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

  11. manjunatha says:

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

  12. Bill Neaveill says:

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

  13. Honkyoq says:

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

  14. Jinagouda says:

    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?

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

%d bloggers like this: