Mailbox Report

A report that is often asked for by anybody interested in the operation of the Exchange Server is one that includes a complete listing of users, last login times, mailbox size and database membership. Here is a useful script that can provide all of this in a CSV file, which is easily manipulated in Excel. A recent addition is the ‘Delegates’ column that tracks all the delegates for that particular mailbox. Handy for migration efforts.

Copy/Paste the script into your Exchange server and run it via the Exchange Shell. It will give you everything you need.

# +---------------------------------------------------------------------------
# | File : MailboxReport.ps1
# | Description : This script will create the CSV file for importing into MigrationWiz
# | Usage : .\CSVCreation.ps1
# | Author: Mark Rochester | https://thecloudgeezer.com
# +-------------------------------------------------------------------------------
$ErrorActionPreference = "SilentlyContinue"

write-host "Exchange Mailbox Report Creator"
write-host "------------------------------------------"
write-host "Adding Exchange Management Snap In"

# Add the Exchange PowerShell snap-in to the current console
$exchangeSnapins = Get-PSSnapin | Where-Object {$_.Name.Contains("Microsoft.Exchange.Management.PowerShell")}
if (-not $exchangeSnapins) {
    Add-PSSnapin Microsoft.Exchange.Management.PowerShell.*
}

Write-host ”"

$CSVfile = Read-Host “Enter the Path of CSV file (Eg. C:\Report.csv)”
write-host "Retrieving Mailboxes"
$AllMailbox = Get-mailbox -resultsize unlimited
$mbxcount = $AllMailbox.count
$mbxprocess = 1
$output = @()

write-host "Found $mbxcount Mailboxes"

Foreach($mbx in $AllMailbox) {

Write-Progress -Activity “Report in Progress” -Status “Processing $mbxprocess of $mbxcount – $($mbx.displayname)” -percentcomplete ($mbxprocess/$mbxcount*100)

    $userObj = New-Object PSObject

    $Stats = Get-mailboxStatistics -Identity $Mbx.distinguishedname

    $userObj | Add-Member NoteProperty -Name "SMTPAddress” -Value $mbx.PrimarySmtpAddress
    $userObj | Add-Member NoteProperty -Name “Alias” -Value $Mbx.Alias
    $userObj | Add-Member NoteProperty -Name “RecipientType” -Value $Mbx.RecipientType

    # Get the organizatinal unit
	$organizationalUnit = $mbx.OrganizationalUnit

	# Remove the domain name in the organizational unit
	if ($organizationalUnit -and $organizationalUnit.IndexOf("/") -ge 0) {
		$organizationalUnit = [string]$organizationalUnit.Substring(($organizationalUnit.IndexOf("/") + 1))
	}

	# Add the organizational unit
	$userObj | Add-Member NoteProperty -Name "OrganizationalUnit" -Value $organizationalUnit.replace(",","|") -Force
	# Add the guid
	$userObj | Add-Member NoteProperty -Name “BatchName” -Value ""

    $userObj | Add-Member NoteProperty -Name “Database” -Value $Stats.Database
    $userObj | Add-Member NoteProperty -Name “ServerName” -Value $Stats.ServerName
    $userObj | Add-Member NoteProperty -Name “TotalItemSize” -Value $Stats.TotalItemSize
    $userObj | Add-Member NoteProperty -Name “ItemCount” -Value $Stats.ItemCount
    $userObj | Add-Member NoteProperty -Name “DeletedItemCount” -Value $Stats.DeletedItemCount
    $userObj | Add-Member NoteProperty -Name “TotalDeletedItemSize” -Value $Stats.TotalDeletedItemSize
    $userObj | Add-Member NoteProperty -Name “DatabaseProhibitSendReceiveQuota” -Value $Stats.DatabaseProhibitSendReceiveQuota
    $userObj | Add-Member NoteProperty -Name “LastLogonTime” -Value $Stats.LastLogonTime

    # Check if IMAP, POP, OWA and ActiveSync are enabled
        $casMailbox = Get-CASMailbox $mbx.Identity
    		if ($casMailbox) {
				$userObj | Add-Member NoteProperty -Name "IMAP" -Value $([string]$casMailbox.ImapEnabled)
				$userObj | Add-Member NoteProperty -Name "POP" -Value $([string]$casMailbox.PopEnabled)
				$userObj | Add-Member NoteProperty -Name "OWA" -Value $([string]$casMailbox.OWAEnabled)
				$userObj | Add-Member NoteProperty -Name "ActiveSync" -Value $([string]$casMailbox.ActiveSyncEnabled)
			}
			else {
				$userObj | Add-Member NoteProperty -Name "IMAP" -Value ""
				$userObj | Add-Member NoteProperty -Name "POP" -Value ""
				$userObj | Add-Member NoteProperty -Name "OWA" -Value ""
				$userObj | Add-Member NoteProperty -Name "ActiveSync" -Value ""

				# Update status and status message
				$status = "Failure"
				$statusMessage = "Unable to retrieve CAS properties for {$mbx.Guid.ToString()}"
			}

		# Get delegates information for the specified mailbox
		
        $delegates = Get-MailboxPermission $mbx.Identity | Where {$_.User.ToString() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $false} | Select User | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_ -replace '"',''} | select -Skip 1 | Get-User | Select Identity | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_ -replace '"',''}
		
			# Check if delegates are not null
			if ($delegates) {
				# Skip the header line
				$delegates = $delegates | Select -Skip 1

				# Define delegatesSmtp list
				$delegatesSmtp = New-Object System.Collections.Generic.List[string]
			
				# Iterate through the list of delegates to get smtp of each delegate
				foreach ($delegate in $delegates) {
					# Get smtp for the specified delegate
					$delegateSmtp = Get-Mailbox $delegate | Select PrimarySmtpAddress

					# Check mailbox is not null
					if ($delegateSmtp)
					{
						$delegateSmtpStr = [string]$delegateSmtp.PrimarySmtpAddress
						if (-Not $delegateSmtpStr.Contains(",")) {
							# Add smtp to the list
							$delegatesSmtp.Add($delegateSmtpStr)
						} 
					}
				}
            
				# Concatenate all the delegates with | delimiter
				$delegatesSmtpString = $delegatesSmtp -Join '|'

				# Add the Delegates property
				$userObj | Add-Member NoteProperty -Name "Delegates" -Value $delegatesSmtpString -force
			} else {
				# Add the Delegates property
				$userObj | Add-Member NoteProperty -Name "Delegates" -Value "" -force
			}
        	

	
    $output += $userObj
    $mbxprocess ++

}


$output |export-csv -Path $CSVfile -NoTypeInformation

(Get-Content $CSVFile) -replace '(?m)"([^,]*?)"(?=,|$)', '$1' | Set-Content $CSVFile

write-host "Data Collected and stored in CSV"