With the new year come new resolutions. Oh, I was talking about the site. PowerShell Pro will now show some PowerShell scripts that would help you with automation. Today’s automation snippet is all about getting an inventory of all mailboxes in your Microsoft Exchange environment.

Here are the topics covered in this post:

Microsoft Exchange is one of those systems whose management almost completely relies on PowerShell. It is often said that only 40% of Exchange administration can be done using the GUI, and the rest 60% can be done only using PowerShell. Also, the GUI actually runs PowerShell in the background to help you do what you want to.

The output of the script/function

This post is based on Exchange 2010. While the newer versions should ideally support the queries made in this script, the script has only been tested on Exchange 2010. This function gets an inventory of all the databases in your Exchange environment, with information such as:

  • Server name
  • Database name
  • Database size
  • Number of mailboxes in the database
  • Total item size of all the mailboxes in the database
  • The whitespace
  • The drive the database is hosted on
  • The free space on the drive
  • The percent free space on the drive

We get this sort of report on a weekly basis, to understand the storage resource consumption in our Exchange environment. This helps us plan for cleanup activities or expansion, build a mailbox space usage trend and plan space management, whether it be communicating to the users about best practices in using emails, or moving around the mailboxes to normalise space usage across the drives across servers.

The script itself

This script is complete with all the necessary help information required to run it. The output of this script is an object of type PSObject, which can be further used, or exported. The simplest way to run the function would be:

# Load the function into the session
. \\path\to\TheScript.ps1

# Call the function
Get-ExchangeInventory MBXSVR001

The script would attempt to load the Exchange session with the credentials you have used to sign into Windows. If these credentials do not have the necessary permissions on Exchange, you would be prompted for alternate credentials.

Tip: If you would like to run the function with verbose information, use the -Verbose parameter.

Here is the function (also available in my PowerShell script repository on GitHub):

function Get-ExchangeInventory {
    <#
    .SYNOPSIS
    Query an inventory of all the databases in the environment, with relevant statistics.

    .DESCRIPTION
    Query a complete inventory of all the databases in the environment, with relevant statistics such as the database name, the size, total item sizes of mailboxes for each database, the server and the drive on which the database is stored, whitespace details, etc.

    .PARAMETER MailboxServerFqdn
    The FQDN of an Exchange server, preferably a mailbox database server.

    .EXAMPLE
    Get-ExchangeInventory -MailboxServerFqdn EXMB001

    .NOTES
    Created by Ram Iyer (https://ramiyer.me)
    #>
    [CmdletBinding()]
    param (
        # The FQDN of a mailbox database server
        [Parameter(Mandatory=$true,Position=0)]
        [string]
        $MailboxServerFqdn
    )
    begin {
        $Statistics = @()

        try {
            Write-Verbose 'Intiating a PowerShell session to the Exchange server.'
            $ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $ExServerFqdn -Authentication Kerberos -ErrorAction Stop
            Import-PSSession $ExchangeSession -ErrorAction Stop -WarningAction SilentlyContinue | Out-Null
        }
        catch {
            try {
                Write-Verbose 'Initiation of the session failed. Trying alternate credentials.'
                Write-Warning "You do not have necessary access. Attempting fallback method."
                $Credentials = Get-Credential -Message "Enter the credentials that have 'Mailbox import-export permissions.'"
                $ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $ExServerFqdn -Credential $Credentials -Authentication Kerberos -ErrorAction Stop
                Import-PSSession $ExchangeSession -ErrorAction Stop -WarningAction SilentlyContinue | Out-Null
            }
            catch {
                Write-Verbose 'Initiation of the session with alternate credentials failed.'
                Write-Error 'Unable to connect to the Exchange Server. Please check your credentials, or contact your Exchange Administrator.'
                Write-Verbose 'Aborting operation.'
                break
            }
        }
    }
    process {
        Write-Verbose 'Querying a list of all databases in the environment.'
        $DatabaseList = (Get-MailboxDatabase | Select-Object Name -ExpandProperty Name | Sort-Object)

        foreach ($Database in $DatabaseList) {
            Write-Verbose "Getting details for $Database."
            $DatabaseDetails    = Get-MailboxDatabase $Database -Status
            Write-Verbose "Getting statistics for $Database."
            $MailboxStatistics  = Get-MailboxStatistics -Database $Database

            $TotalItemSize      = ($MailboxStatistics.TotalItemSize.Value.ToBytes() | Measure-Object -Sum).Sum/1GB
            $MailboxCount       = $MailboxStatistics.Count
            $DatabaseSize       = $DatabaseDetails.DatabaseSize.ToBytes()/1GB
            $DatabaseDrive      = $DatabaseDetails.EdbFilePath.DriveName
            $ServerName         = $DatabaseDetails.Server.Name
            $WhiteSpace         = $DatabaseDetails.AvailableNewMailboxSpace.ToBytes()/1GB
            $Difference         = $DatabaseSize - $TotalItemSize
            $FreeSpace          = ((Get-WmiObject Win32_LogicalDisk -ComputerName $ServerName -Filter "DeviceID='$DbDrive'").FreeSpace)/1GB
            $PercentFreeSpace   = ((Get-WmiObject Win32_LogicalDisk -ComputerName $ServerName -Filter "DeviceID='$DbDrive'").FreeSpace / (Get-WmiObject Win32_LogicalDisk -ComputerName ($DatabaseDetails.Server.Name) -Filter "DeviceID='$DbDrive'").Size) * 100

            Write-Verbose "Creating a record for $Database."
            $Fields = [ordered]@{
                ServerName        = $ServerName
                DatabaseName      = $DatabaseName
                DatabaseSizeGB    = [math]::Round($DatabaseSize, 2)
                MailboxCount      = $MbCount
                TotalItemSizeGB   = [math]::Round($TotalItemSize, 2)
                WhitespaceGB      = [math]::Round($WhiteSpace, 2)
                DifferenceGB      = [math]::Round($Difference, 2)
                Drive             = $DbDrive
                FreeSpaceGB       = [math]::Round($FreeSpace, 2)
                PercentFreeSpace  = [math]::Round($PercentFreeSpace, 2)
            }

            $Statistics += New-Object -TypeName PsObject -Property $Fields
        }

        Write-Verbose "Completed fetching information for all the databases."
        $Statistics
    }
    end {
        Write-Verbose "Terminating the session to the Exchange server."
        Remove-PSSession $ExchangeSession
    }
}

How it works

The functioning of this script is very simple. First, the function declares its parameter (which is just one, the FQDN of the mailbox server). We then initialise the $Statistics variable; this variable is what is finally output. We then attempt a connection to the specified mailbox server with a basic fallback.

Next, in the main process, we query the databases in the environment. You can modify the script to exclude any databases, if you want.

$DatabaseList = (Get-MailboxDatabase).Name | Sort-Object

Then, we loop through the entire list of databases. For each database, we perform two primary queries; these are the ones that fetch information from Exchange.

$DatabaseDetails    = Get-MailboxDatabase $Database -Status
$MailboxStatistics  = Get-MailboxStatistics -Database $Database

The first query fetches all the necessary database information. This includes the name of the server the database resides, on which drive the database resides, what the database size is, what the whitespace in the database is, etc.

The second query is used for two properties: the number of mailboxes and the total actual space used by the mailboxes1.

We then assign the selected values to variables. It is possible to directly assign these values to the properties that we create for the object, however, it would look quite complicated to read.

Next, we create an ordered hashtable. We assign the values to keys, such as TotalItemSizeGB. When assigning the values, we use the [math] accelerator and the Round() method within it to round the numbers to the nearest hundredth.

When creating a PSObject, we first create a hashtable. The keys in the hastable are the ones that become property names within the object. When this is added to the object, the hashtable is transposed and added to the newly-created object. Each hashtable is a single instance of the object—a row in a table, if you will.

Next, we use the += assignment operator to add the row to the object. This is the reason we initialised the object in the begin block of the function: we do not want the variable to carry values across runs.

Finally, we call the variable in the function. This is like the return statement in functions in languages such as C++.

In the end block, we perform a little cleanup—we terminate the session the function established with the Exchange server.

Extending the script

The function can be extended. For instance, if you would like the script to send you an email, just add the following to the script (after the function):

$OutputPath = "\\Path\to\InventoryDirectory"
$Date = Get-Date -Format 'MM-dd-yyyy'
$FileName = "Exchange Data Analysis Report ($Date)"
$Recipients = 'one@domain.com', 'two@domain.com', 'three@domain.com'

Get-ExchangeInventory | Export-Csv "$OutputPath\$FileName.csv" -NoTypeInformation

Send-MailMessage `
-From "MyName@domain.com" `
-To $Recipient `
-SmtpServer "smtp.domain.com" `
-Subject "Exchange Inventory" `
-Body "Please find attached, the inventory with all the relevant Exchange data usage information." `
-Attachments "$OutputPath\$FileName.csv"

Or, if you would like only the database-related information (and not the drive-related information), change the line that calls the function and exports the data, this way:

Get-ExchangeInventory |
 Select-Object ServerName, DatabaseName, DatabaseSizeGB, TotalItemSizeGB, WhitespaceGB |
  Sort-Object -Property ServerName, DatabaseName |
   Export-Csv "$OutputPath\$FileName.csv" -NoTypeInformation

The beauty of modularity. Everything is a separate piece. Every piece does only one task. And pieces can be connected with other pieces to do something bigger.

I hope this script helps Exchange administrators get their database inventory. [Follow me on GitHub](https://github.com/{{ site.github }}) for more such scripts and functions.

  1. The calculation of the total item size interested me the most in this, as a person just starting out with PowerShell scripting. Back then, I used ($MailboxStatistics | ForEach-Object { $PsItem.$TotalItemSize.Value.ToBytes() } | Measure-Object -Sum).Sum/1GB to get the information. At that time, I thought I must pull out the total item size of each mailbox separately within the database, using Foreach-Object. When I revisited this script to write about it (now that I have a better understanding of how objects work in PowerShell), I realised, the Foreach-Object wasn’t necessary there. Therefore, I simplified the statement to ($MailboxStatistics.TotalItemSize.Value.ToBytes() | Measure-Object -Sum).Sum/1GB. Does it help in any way? Sure, I think it should bring down the running time of the script significantly. ↩︎