Wednesday, January 21, 2015

Exchange 2013 mailbox quota usage and size report

In Exchange 2013 it can be a bit hard to find mailboxes that are close to their quota limits. You can see it in the Exchange Admin Center, but that is a tedious task if you want to check all mailboxes.
You can also wait for the users to contact you when they get a quota warning, or when they no longer can send email. That's a bit late.
With PowerShell you can easily find out the mailbox size, but the quota limit is either Unlimited, which means that the mailbox database defaults are used, or it can be set at a certain limit.

Find the mailbox quota
get-mailbox mailbox | select-object DisplayName, ProhibitSendQuota, ProhibitSendReceiveQuota

DisplayName ProhibitSendQuota ProhibitSendReceiveQuota
----------- ----------------- ------------------------
User Name   Unlimited         Unlimited

Find the mailbox size and database quota
Get-MailboxStatistics -identity mailbox | select-object Displayname,TotalItemSize,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota

DisplayName               : User Name
TotalItemSize             : 1.125 GB (1,208,281,527 bytes)
TotalDeletedItemSize      : 24.14 MB (25,309,210 bytes)
DatabaseIssueWarningQuota : 5 GB (5,368,709,120 bytes)
DatabaseProhibitSendQuota : 6 GB (6,442,450,944 bytes)


So, the numbers are all there, just not in a nice format...

To make a long story short - here's a PowerShell script that will output all mailboxes with a quota usage over 80%

<#
.SYNOPSIS
    TGet-MailboxSizeQuota.ps1 returns all mailboxes above a certain quota (ProhibitSendQuota) usage.
.DESCRIPTION
    TGet-MailboxSizeQuota.ps1 returns all mailboxes with a quota usage of 80% and above.
    If you want to check for another quota limit, pass the percentage as a parameter.

    TGet-MailboxSizeQuota.ps1 
.EXAMPLE
    TGet-MailboxSizeQuota.ps1
    The above command will return all mailboxes with a quota usage of 80% and above
.EXAMPLE
    TGet-MailboxSizeQuota.ps1 85
    The above command will return all mailboxes with a quota usage of 85% and above
.NOTES
    Author: Peter Haake
    Date:   2015-01-20    
#>
#
# Load the Exchange Management Module
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
# Make sure en-us locale is used no matter what the server/user has configured
# Keep this section if you output decimal numbers
# Slightly modified from From http://occasionalutility.blogspot.com.au/2014/03/everyday-powershell-part-17-using-new.html
[System.Reflection.Assembly]::LoadWithPartialName("System.Threading")>$null
[System.Reflection.Assembly]::LoadWithPartialName("System.Globalization")>$null
[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo]::CreateSpecificCulture("en-us")

# Set quotalimit to the first parameter passed. If no parameter is passed, set it at 80%
if ([INT]$args[0] -gt "") {
    $quotalimit = [INT]$args[0]
    }
else {
    $quotalimit = 80
}

# Get all mailboxes
$Mailboxes = @(Get-Mailbox -ResultSize Unlimited | select-object DisplayName, Identity, ProhibitSendQuota, ProhibitSendReceiveQuota)
# Clear the report object variable
$Report =@()

# Loop through all mailboxes
foreach ($usr_mailbox in $Mailboxes)
{
    # Get statistics for all mailboxes
    $usr_mailboxstats = Get-MailboxStatistics -identity $usr_mailbox.Identity | select-object Displayname,Identity,Database,TotalItemSize,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota

    #Convert TotalItemSize to INT64 and remove crap (looks like this initially "1.123 GB (1,205,513,370 bytes)" and comes out as a numeric 1205513370)
    [int64]$usr_mailboxstats_totalitemsize = [convert]::ToInt64(((($usr_mailboxstats.TotalItemSize.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))
    #Convert TotalDeletedItemSize to INT and remove crap (looks like this initially "1.123 GB (1,205,513,370 bytes)" and comes out as a numeric 1205513370)
    [int64]$usr_mailboxstats_totaldeleteditemsize = [convert]::ToInt64(((($usr_mailboxstats.TotalDeletedItemSize.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))

    # If the mailbox quota is Unlimited, then the database defaults are used.
    if ($usr_mailbox.ProhibitSendQuota -eq "Unlimited") {
        # Get quota from Database
        [INT64]$usr_quota = [convert]::ToInt64(((($usr_mailboxstats.DatabaseProhibitSendQuota.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))
        }
    else {
        # Get quota from user mailbox
        [INT64]$usr_quota = [convert]::ToInt64(((($usr_mailbox.ProhibitSendQuota.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))
    }
    # Calculate the quota percentage
    $usr_quota_percentage = [INT]((($usr_mailboxstats_totalitemsize + $usr_mailboxstats_totaldeleteditemsize) / $usr_quota)*100)

    # Add to report object
    if ($usr_quota_percentage -ge $quotalimit) {
        $usr_reportObject = New-Object PSObject
        $usr_reportObject | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $usr_mailboxstats.DisplayName
        $usr_reportObject | Add-Member -MemberType NoteProperty -Name "TotalItemSize" -Value $usr_mailboxstats_totalitemsize
        $usr_reportObject | Add-Member -MemberType NoteProperty -Name "TotalDeletedItemSize" -Value $usr_mailboxstats_totaldeleteditemsize
        $usr_reportObject | Add-Member -MemberType NoteProperty -Name "ProhibitSendQuota" -Value $usr_quota
        $usr_reportObject | Add-Member -MemberType NoteProperty -Name "QuotaPercent" -Value $usr_quota_percentage
        $report += $usr_reportObject
    }
}
# Output the report, sorted with the highest quota percentage at the top
$Report | Sort-Object QuotaPercent -Descending

 Output looks something like this

DisplayName  TotalItemSize  TotalDeletedItemSize  ProhibitSendQuota  QuotaPercent
-----------  -------------  --------------------  -----------------  ------------
User Name       8316860830              49373170         9663676416            87
Second User    14581624515              17277483        17179869184            85
Third Person    5472939739              15151742         6442450944            85

Send the output from the script as an email

If you want to schedule the script to run every week or so, it might be convenient to have the output sent to you in an email. Create another script, and call TGet-MailboxSizeQuota.ps1 from that as below:

Send-MailMessage -to email@domain.com -Subject "Quota Report" -SmtpServer mail.server.FQDN -From from@domain.com -Body (C:\Scripts\TGet-MailboxSizeQuota.ps1 | ft | Out-String)

This can easily be added as a scheduled task. Just make sure to run the task under a user account that has enough rights in Exchange.

5 comments:

  1. Hello,

    i am receiving the following error when trying to execute the script as it without any modification , can you advise ?

    You cannot call a method on a null-valued expression.
    At D:\Reporting\new2.ps1:49 char:5
    + [int64]$usr_mailboxstats_totalitemsize = [convert]::ToInt64(((($usr_mailboxs ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At D:\Reporting\new2.ps1:51 char:5
    + [int64]$usr_mailboxstats_totaldeleteditemsize = [convert]::ToInt64(((($usr_m ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    ReplyDelete
    Replies
    1. Hi,

      Might be related to the number format in the output from the following command:

      Get-MailboxStatistics -identity mailbox | select-object Displayname,TotalItemSize,TotalDeletedItemSize,DatabaseIssueWarningQuota,DatabaseProhibitSendQuota

      If you run that for a specific mailbox, does your output match this format?

      DisplayName : User Name
      TotalItemSize : 1.125 GB (1,208,281,527 bytes)
      TotalDeletedItemSize : 24.14 MB (25,309,210 bytes)
      DatabaseIssueWarningQuota : 5 GB (5,368,709,120 bytes)
      DatabaseProhibitSendQuota : 6 GB (6,442,450,944 bytes)

      You could anonymize it and post it here if you like.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. hello,
    i have test the script and it work without error, but i have an incorect percentage :

    DisplayName TotalItemSize TotalDeletedItemSize ProhibitSendQuota QuotaPercent
    ----------- ------------- -------------------- ----------------- ------------
    user1 684223 1172668284 97280000 1206
    user2 140895 1135036880 97280000 1167
    user3 12494 1054149862 97280000 1084

    this user are on exchange 2007 or on exchnage 2013 with default database quota used.

    thanks for help

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete