How to list all active and disconnected Exchange mailboxes per database using Powershell

Exchange administrators often need to list all disconnected mailboxes in Exchange DBs and their relative state, sorted per DB so that a report can be obtained. 

A disconnected mailbox is a mailbox object in the mailbox database that isn't associated with an Active Directory user account. As per this "Disconnected Mailboxes" article by Microsoft, there are two types of disconnected mailboxes:

Disabled or deleted mailboxes:

Disabled Mailboxes: When a mailbox is disabled or deleted in the Exchange admin center (EAC) or using the Disable-Mailbox or Remove-Mailbox cmdlet in the Exchange Management Shell, Exchange retains the deleted mailbox in the mailbox database, and switches the mailbox to a disabled state. This is why mailboxes that are either disabled or deleted are referred to as disabled mailboxes. The difference is that when you disable a mailbox, the Exchange attributes are removed from the corresponding Active Directory user account, but the user account is retained. When you delete a mailbox, both the Exchange attributes and the Active Directory user account are deleted.

Disabled and deleted mailboxes are retained in the mailbox database until the deleted mailbox retention period expires, which is 30 days by default. After the retention period expires, the mailbox is permanently deleted (also called purged). If a mailbox is deleted using the Remove-Mailbox cmdlet, it's also retained for the duration of the retention period.

Important: If a mailbox is deleted using the Remove-Mailbox cmdlet using the -Permanent or -StoreMailboxIdentity parameter, it will be immediately deleted from the mailbox database.

Soft-deleted mailboxes: When a mailbox is moved to a different mailbox database, Exchange doesn't fully delete the mailbox from the source mailbox database when the move is complete. Instead, the mailbox in the source mailbox database is switched to a soft-deleted state. Like disabled mailboxes, soft-deleted mailboxes are retained in the source database either until the deleted mailbox retention period expires or until the Remove-StoreMailbox cmdlet is used to purge the mailbox.

To identify the disabled mailboxes in your organization, run the following command in the Exchange Management Shell.

Get-MailboxDatabase | Get-MailboxStatistics | Where { $_.DisconnectReason -eq "Disabled" } | Format-Table DisplayName,Database,DisconnectDate

Powershell Get-mailboxstatistics can be used on a mailbox to retrieve its disconnect state (disconnectReason, which can be SoftDeleted, Disconnected or empty if the mailbox is active).

To report on disconnected mailboxes in a single Exchange server or in a DAG we'll pipe the output of powershell get-mailboxdatabase to get-mailboxstatistics to retrieve the state of all mailboxes, filtering out the ones for which the field disconnectReason is empty, and save a report in csv file format sorted by database and showing Database, disconnectDate, disconnectReason, DisplayName, WindowsEmailAddress (present for soft-deleted mailboxes - absent for deleted mailboxes)

Note: this one-liner has to be run in Exchange Management Shell, be it on a server with Exchange Management Tools Installed or directly on an Exchange Mailbox. It has been tested on Exchange 2013 and 2016. The exported CSV can be opened in Excel and sorted.

get-mailboxdatabase | Get-MailboxStatistics | ? { $_.disconnectreason } | select Database,disconnectDate, disconnectReason, DisplayName, @{label="WindowsEmailAddress";expression={ get-user -identity ($_).MailboxGuid.Guid | select -expandproperty WindowsEmailAddress } } | Export-Csv -NoTypeInformation $env:userprofile\mailbox_report.csv