Tuesday, June 22, 2021
Tuesday, June 22, 2021

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://exchange2019.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"
Mark Rochesterhttps://exchange2019.com
Mark currently works in the cloud space assisting large companies to migrate from either on premises to the cloud, or cloud to cloud. His experience with Enterprise migrations spans more than 25 years which basically makes him old. However, with all the oldness creeping up he still finds technology massively exciting. Please reach out for a chat anytime you would like. 🙂
- Advertisment -

Most Popular

Recent Comments