Exchange 2007 Database Size and User Count

2010-08-26T00:00:51Z

Although the Exchange Management Shell has added a bunch of information to make it right at your fingertips, I think that there is some information that is lacking. For one, I'd like the ability to see the size of a mailbox database and the number of users on that database. We use this information all the time to determine where to put new users. Since I'm always the one asked, I decided to do something about it.

Parts of this script were taken from various posts around the web and some digging I did within the Exchange Management Shell. I'd like to thank all of those people, but I just don't remember where the script parts came from. If you happen to be an author or contributor, leave me a comment and I'll be sure to link to your information.

After a little tinkering I cam up with this PowerShell function:

#REQUIRES -PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

Function Get-MailboxDatabaseInfo()
#######################################################################
# Function Get-MailboxDatabaseInfo
#######################################################################
# Purpose:     Returns an Object with the Database Size and User Count
#              for all Mailbox Servers in the Environment
# Parameters: (none)
# Returns:    Object Containing:
#                Database (in long format)
#                Size (in GB)
#                Size (in MB)
#                User Count

{

    # Create an Empty Collection for the Objects
    $colObjects = @()

    # Cycle Through Each Mailbox Server
    ForEach ( $Server in Get-MailboxServer )
    {
       # For each Mailbox server, get all the databases on it
       $strDB = Get-MailboxDatabase -Server $Server

       # For each Database, get the information from it
       ForEach ( $objItem in $strDB )
       {
          # Count the number of users and store it as a variable
          $intUsers = ( $objitem | Get-Mailbox -ResultSize Unlimited ).count

          # Determine the location of the EDB File
          $EDBFilePath = $objItem.EDBFilePath
          # Create the Path in a Variable
          $Path = "`\`\" + $Server + "`\" + $objItem.EDBFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EDBFilePath.PathName.Remove(0,2)
          # Get the Child Item (and all the members)
          $strDBsize = Get-ChildItem $Path
          # Create new Object
          $ReturnedObj = New-Object System.Object
          # Add the information for the database to the new System.Object
          $ReturnedObj | Add-Member NoteProperty -Name "Database" -Value $objItem.Identity
          $ReturnedObj | Add-Member NoteProperty -Name "Size (GB)" -Value ("{0:n2}" -f ($strDBsize.Length/1024MB))
          $ReturnedObj | Add-Member NoteProperty -Name "Size (MB)" -Value ("{0:n2}" -f ($strDBsize.Length/1024KB))
          $ReturnedObj | Add-Member NoteProperty -Name "User Count" -Value $intUsers
          # Add the Object to the Collection
          $colObjects += $ReturnedObj
       }
    }
    # Return the Collection
    Return $colObjects
}

BlackBerry User Summary Reports (5.0.2 Compatible)

2010-08-25T22:36:57Z

Although many people really like the standard reporting in the BlackBerry Monitor Server, we needed some more details for our usage at my company.

This resulted in a new view being created for queries that spanned multiple tables to give "point in time" status on BlackBerry devices. I wrote something similar a few years ago while we were still on 4.1.X, but decided to update it for the 5.0 servers.

The information I felt was absolutely necessary was:

Display Name (of the user)
Model of the BlackBerry
Handheld Application Version
Handheld Platform Version
Phone Number assigned to device (even if it is a data-only device)
IMEI (Serial Number)
Home Network (or Cellular Provider)
IT Policy Name (in which policy are they placed)
PIN
Email Address
Exchange Server
BES Server (we run a total of seven and identification of the BES is a must)
Number of Messages Pending
Number of Messages Sent
Number of Messages Forwarded
Last Contact (as a time difference from the current)
User ID in the Database

After tinkering around with it for a while and getting assistance from a friend who knows the cool SQL stuff better than I do, we came up with a view. Most of this information is available in one view, the vUserConfigStats view, but some is buried in the ServerConfig Table. Here's what my view looks like with some very simple comments.

-- ***********************************************

-- * VIEW: vUserSummary

-- ***********************************************

-- Purpose: Collect user-specific information from multiple tables and return it in a very simple (and readable) format.

CREATE VIEW [dbo].[vUserSummary]

SELECT dbo.vUserConfigStats.DisplayName

, dbo.vUserConfigStats.ModelName AS Model

, dbo.vUserConfigStats.AppsVer AS ApplicationVersion

, dbo.vUserConfigStats.PlatformVer AS PlatformVersion

, CASE -- This checks the phone number for the number of digits (10 or 11) in the US and parses it to the ###-###-#### format

WHEN len(ltrim(rtrim(vUserConfigStats.PhoneNumber))) = '10'

THEN '(' + SUBSTRING(vUserConfigStats.PhoneNumber, 1, 3) + ')' + ' ' + SUBSTRING(vUserConfigStats.PhoneNumber, 4, 3) + '-' + SUBSTRING(vUserConfigStats.PhoneNumber, 7, 4)

WHEN len(ltrim(rtrim(vUserConfigStats.PhoneNumber))) = '11'

THEN '(' + SUBSTRING(vUserConfigStats.PhoneNumber, 2, 3) + ')' + ' ' + SUBSTRING(vUserConfigStats.PhoneNumber, 5, 3) + '-' + SUBSTRING(vUserConfigStats.PhoneNumber, 8, 4)

ELSE 'INVALID' -- if it's not 10 or 11 digits throw an error (and return 'INVALID')

END AS PhoneNumber

, UPPER(dbo.vUserConfigStats.IMEI) AS IMEI

, dbo.vUserConfigStats.HomeNetwork

, dbo.vUserConfigStats.ITPolicyName

, dbo.vUserConfigStats.PIN

, dbo.vUserConfigStats.MailboxSMTPAddr AS SMTPAddress -- MailboxSMTPAddr is a poor name for this, so I changed it

-- The substring below makes me especially proud

-- Exchange Servers are stored according to distinguished name. Since we only need a part of this name, this very large substring extracts just what is needed

, SUBSTRING(dbo.vUserConfigStats.ServerDN, PATINDEX('%Servers/cn=%', dbo.vUserConfigStats.ServerDN) + 11, PATINDEX('%/cn=Microsoft%', dbo.vUserConfigStats.ServerDN) - (PATINDEX('%Servers/cn=%', dbo.vUserConfigStats.ServerDN) + 11)) AS ExchangeServer

, dbo.ServerConfig.ServiceName AS BESServer -- Changed this name to make more sense

, dbo.vUserConfigStats.MsgsPending

, dbo.vUserConfigStats.MsgsSent

, dbo.vUserConfigStats.MsgsForwarded

-- This calculates the difference from the lst contact (in or out) of the device to now and returns the number of seconds

-- This value is VERY useful in determining who has devices turned off, in desks, lost, or otherwise out of touch

, DATEDIFF(second, CASE

WHEN LastFwdTime >= LastSentTime

THEN LastFwdTime

ELSE LastSentTime

END, GetUTCDate()) AS LastContact

-- This ID Number for the user is unique in the database and has some uses in running the Resource Tools

, dbo.vUserConfigStats.Id

FROM dbo.vUserConfigStats INNER JOIN

dbo.ServerConfig ON dbo.vUserConfigStats.ServerConfigId = dbo.ServerConfig.Id

Kevin's Ramblings

Exchange 2007 Database Size and User Count

BlackBerry User Summary Reports (5.0.2 Compatible)