Quick Mailbox Size Report

Every once in awhile, some department wants a mailbox size report for all of their users. There are a number of easy ways to do this, but sometimes the easiest ways are the slowest. For example:

get-mailbox | get-mailboxstatistics | select displayname, totalItemSize | export-csv "CSV File"

This works all-and-good until they want additional information, like department, or office! Since this additional information is not stored in the mailbox object, but the user object, you need to meld a get-user with your get-mailboxstatistics. This can easily turn your quick little process into a massive, eight hour query pulling data.

This script:

1. Specify an OU or Distribution Group, and it will read all the members. 
2. Read mailbox name, total item size, Office, current size, if using db quotas, issue warning quota, prohibit send quota, when the mailbox was created, last logon and database currently resides on.
3. Output to CSV report.
4. Attach it to an email to multiple recipients. 
5. In email message body, break down the current db limits applied to all dbs in the report. "If UseDatabaseDefaults = True, see the message body for that size limit"

<#
.SYNOPSIS
    Report-MBXSizeQuick.ps1 - Generate detailed mailbox Size Report

.DESCRIPTION
   Generates detailed report for all mailboxes in a specific OU, or distribution group.
.PARAMETER ReportHeader
   Name that will appear on the CSV file before "_MBXSizeReportInMB.CSV"
.PARAMETER Recipients
   One or many SMTP email addresses to receive this report
.PARAMETER FilterSize (Optional)
   Only Return mailboxes EQUAL or LARGER than this size. 
.PARAMETER SOURCEOU
   Name of OU to filter on.
.PARAMETER SOURCENAME
   Name of a distribution group to filter on
.PARAMETER EXCH07Only
   Filter to Exchange 2007 mailboxes only. {ExchangeVersion -eq "0.1 (8.0.535.0)}
.EXAMPLE
   Report-MBXSizeQuick.ps1 -ReportHeader "Sales Department" -Recipients EricWoodford@Example.com,SalesManager@example.com -FilterSize 2GB -SourceOU Example.com/Sales 
   
   Return mailbox sizes on all mailboxes over 2GB in size that reside in the Sales OU. Email report to Eric and the Sales Manager's mailbox.
#>

[CmdLetBinding()]
param(
 [parameter(Mandatory=$true,HelpMessage="Department Field on Mailboxes")]
 [string]$ReportHeader,
 [parameter(Mandatory=$true)]
 [string[]] $recipients, 
 [parameter(HelpMessage="Size in MB to filter reports on. Show mailboxes equal or larger than this value.")]
 [double]$filterSize,
 [parameter(HelpMessage="OU containing the mailboxes for this report")]
 [string] $sourceOU,
 [parameter(HelpMessage="Distribution Group containing mailboxes")]
 [string]$SourceName,
 [parameter(HelpMessage="Filter on Exchange 2007 mailboxes only.")]
 [switch]$Exch07Only
)


$use07Filter = ($Exch07Only.ispresent -ne "")

if ($sourceOU -eq "" -and $sourcename -eq "") {
 Write-Host "ERROR: please provide value for SOURCEOU or SOURCENAME (for group)." -ForegroundColor red
 break
}

Write-Host "Export Mailbox Size and name for select users."

$SMTPServer = "SMTP.Relay.Path" 
$reportPath = "\\FPS_Share\Path\"+ $ReportHeader + "_MBXSizeReportInMB.csv"
$filterOnSize = $filterSize -ne 0

function Test-QADObject {
 [CmdletBinding()]
 param(
  [Parameter(Position=0, Mandatory=$true)]
  [System.String]
  $Identity
 )
 (  Get-QADObject $Identity -DontUseDefaultIncludedProperties `
  -WarningAction SilentlyContinue -ErrorAction SilentlyContinue `
  -SizeLimit 1) -ne $null
}

$reuse = $false
if ($sourcegroup -ne $null) {
 if ($sourcegroup[0].OrganizationalUnit -match $sourceOU) {
  Write-Host "I see previous data:"
  Write-Host "`t $sourcegroup.count"
  $answer = Read-Host "Would you like to reuse this info(y/n)"
  $resuse = ($answer -eq "y")
 }
}
if (-not $reuse) {
 Write-host "Grabbing Mailboxes from $SourceOU"
 if ($use07Filter) {
  Write-host "Exchange 2007 only"
  $SourceGroup = Get-Mailbox -OrganizationalUnit $SourceOU -ResultSize unlimited  -filter {ExchangeVersion -eq "0.1 (8.0.535.0)"}
 } else {
  $SourceGroup = Get-Mailbox -OrganizationalUnit $SourceOU -ResultSize unlimited 
 }
}

if ($SourceGroup -eq $null -and $sourcename -ne "") { 
 $tp = Test-QADObject $SourceName
 if ($tp) {
  Write-Host "reading Group membership"
  $SourceGroup = Get-QADGroupMember $sourcename -Type user -Indirect -SizeLimit 0 | %{Get-Mailbox -Identity $_.dn} 
 } else {
  Write-Host "unable to find DL"
  break
 }
}

if ($sourceGroup -ne $null) {
 Write-host "Found: ",$SourceGroup.Count
 $index = 1
 $mbxCount = $sourcegroup.count
 if ($reuse -and $mbxCount -ne $mastergroup.Count) {
  Write-Host "don't have good sizes. need to rebuild" -foreground yellow
  $reuse = $false
 }
 if ($reuse -and $mastergroup[0].MBSize -ne $null) {
  Write-Host "using existing mailbox exports."
 } else {
  $MasterGroup = @()
  foreach ($s in $SourceGroup) {
   write-progress -activity "Reading Mbx Sizes" -status "$index of $MbxCount - $s" -percentComplete (($index / $mbxCount)*100)
   $index ++
   $GMS = Get-MailboxStatistics $s 
   if ($gms) {  
    $s | Add-Member -Name "MBSize" -MemberType NoteProperty -Value $GMS.totalitemsize.value.tomb()
    $s | Add-Member -Name "LastLogonTime" -MemberType NoteProperty -Value $GMS.lastLogonTime
   } else {
    $s | Add-Member -Name "MBSize" -MemberType NoteProperty -Value 0
    $s | Add-Member -Name "LastLogonTime" -MemberType NoteProperty -Value "0/0/0000"
   }
   $MasterGroup += $s 

  }
 }

 if ($FilterOnSize) {
  $MasterGroup |?{$_.MBSize -ge $filterSize} | Select Displayname,Office,mbsize,usedatabasequotadefaults,issuewarningquota, prohibitSendQuota,database | sort displayname | Export-Csv -Path $reportpath -NoTypeInformation
 } else {
  $MasterGroup | Select Displayname,Office,mbsize,usedatabasequotadefaults,issuewarningquota,whencreated,LastLogonTime,prohibitSendQuota,database | sort displayname | Export-Csv -Path $reportpath -NoTypeInformation
 }

 $tp = Test-Path $reportPath -IsValid

 if ($recipients -ne $null -and $tp) {
  $MsgBody = "When reviewing the attached spreadsheet, if 'UseDatabaseQuotaDefaults' equals True, the mailbox size is defined by the limits posted below."
  if (($mastergroup | ?{$_.UseDatabaseQuotaDefaults -eq $true}).count -ne 0) {
   $dbgroup = $mastergroup | ?{$_.UseDatabaseQuotaDefaults -eq $true} | group database 
   $dblimits = $dbgroup | %{Get-MailboxDatabase $_.name} | select name,issuewarningquota,prohibitsendquota
   $limits = $dblimits | Sort Name | ConvertTo-Html 
   $msgbody += $limits 
  }
  Send-MailMessage -Subject "Mailbox Size Report" -BodyAsHtml $msgbody -From "MailboxReport@example.com" -To $Recipients -Attachments $reportPath -SmtpServer $SMTPServer 
 }
}