Thursday, December 5, 2024

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"
Mark Rochester
Mark Rochesterhttps://thecloudgeezer.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. :-)

Related Articles

Microsoft 365 Discovery Report

Overview The Microsoft 365 Discovery Report is a collection of outputs from the various workloads inside a Microsoft 365 tenant, all consolidated into a single...

Migrate Microsoft 365 Mailboxes to Google Workspace

This is not a very common subject to talk about as most of the migrations that get performed are people moving into the Microsoft...

Batches Paused in ‘Needs Approval’ Status

When you are using the native Microsoft tools to migrate from Google Workspace (Gmail) into Microsoft 365 the tool works very well. It does...

Stay Connected

88FansLike
36,999FollowersFollow
32,381SubscribersSubscribe
- Advertisement -

Latest Articles