There are a plethora of software packages out there to report against your Exchange Infrastructure and many of them are great. I’ve worked with several in the past. However, if you want just a simple report for mailbox sizes, and you have access to a SQL Server, you can write your own process.
My script does the following:
- Check to see if the Exchange Snapin is loaded and loads it if necessary.
- Build up a connection to the SQL Server.
- Cycle through each Mailbox Server in your environment.
- Cycle through each Mailbox Database in your environment for each Server.
- Cycle through each Mailbox for each Mailbox Database.
- Execute a single SQL Insert command to add an entry to the table in question.
- Finally, it closes the SQL Connection and disposes of it.
First, let’s build the SQL Table. Assuming that you have a database where you have proper credentials, you can just run the following SQL script to create the “MailboxStats” Table.
USE [Scratch] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MailboxStats] ( [Id] [int] IDENTITY(1, 1) NOT NULL , [RunDate] [datetime] NOT NULL , [MailboxGuid] [varchar](36) NOT NULL , [DisplayName] [nvarchar](255) NOT NULL , [ServerName] [nvarchar](255) NOT NULL , [DatabaseName] [nvarchar](255) NOT NULL , [IsArchiveMailbox] [bit] NOT NULL , [DeletedItemCount] [bigint] NOT NULL , [DeletedItemSize] [bigint] NOT NULL , [ItemCount] [bigint] NOT NULL , [MailboxSize] [bigint] NOT NULL , CONSTRAINT [PK_MailboxStats] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Then all you need to do is run the following script on a scheduled basis. I’ve found that even daily entries do not grow the database to insane sizes too quickly.
######################################################## # # Daily Mailbox Size Report # ######################################################## #region Check for Exchange 2010 Snapin if ( -not ( Get-Command -Name Get-Mailbox -ErrorAction SilentlyContinue ) ) { Add-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.E2010 -ErrorAction SilentlyContinue } #endregion #region Script Variables $RunDate = Get-Date -Format "MM/dd/yyyy" $SqlServer = "SQLSERVERNAME" $SqlDatabase = "Scratch" $SqlTable = "MailboxStats" #endregion $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source=$( $SqlServer ); Initial Catalog=$( $SqlDatabase ); Integrated Security=SSPI;" $SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand $SqlCommand.Connection = $SqlConnection if ( -not ( $SqlConnection.State -like "Open" ) ) { $SqlConnection.Open() } #region Cycle Through Servers, then Databases, and finally Mailboxes $Servers = Get-ExchangeServer | Where-Object { $_.IsE14OrLater -and $_.IsMailboxServer } | Sort-Object -Property Name $iCount = $Servers.Count; $i = 0 ForEach ( $Server in $Servers ) { Write-Progress -Activity "Collecting Server Information" -CurrentOperation "$( $Server.Name )" -Id 1 -PercentComplete ( $i * 100 / $iCount ) -Status "Collecting Databases from Server: $( $Server.Name )" $Databases = Get-MailboxDatabaseCopyStatus -Server ( $Server.Name ) | Where-Object { $_.Status -eq "Mounted" } -ErrorAction SilentlyContinue | Sort-Object -Property DatabaseName $jCount = $Databases.Count; $j = 0 ForEach ( $Database in $Databases ) { Write-Progress -Activity "Collecting Database Information" -CurrentOperation "$( $Database.DatabaseName )" -Id 2 -ParentId 1 -PercentComplete ( $j * 100 / $jCount ) -Status "Collecting Mailboxes from Database: $( $Database.DatabaseName )" $Mailboxes = Get-Mailbox -ResultSize Unlimited -Database ( $Database.DatabaseName ) -ErrorAction SilentlyContinue | Sort-Object -Property DisplayName $kCount = $Mailboxes.Count; $k = 0 $lCount = $ArchiveMailboxes.Count; $l = 0 ForEach ( $Mailbox in $Mailboxes ) { Write-Progress -Activity "Collecting Mailbox Information" -CurrentOperation "$( $Mailbox.DisplayName )" -Id 3 -ParentId 2 -PercentComplete ( $k * 100 / $kCount ) -Status "Collecting Statistics from Mailbox: $( $Mailbox.DisplayName )" $MailboxInfo = $Mailbox | Get-MailboxStatistics | Select-Object DisplayName, MailboxGuid, ServerName, DatabaseName, IsArchiveMailbox, DeletedItemCount, @{ Name = "DeletedSize"; Expression = { $_.TotalDeletedItemSize.Value.ToBytes() } }, ItemCount, @{ Name = "MailboxSize"; Expression = { $_.TotalItemSize.Value.ToBytes() } } if ( $MailboxInfo ) { $DisplayName = $MailboxInfo.DisplayName.Replace("'", "''") $ArchiveBit = 0 $SqlInsert = "INSERT INTO [dbo].[MailboxStats] ([RunDate], [DisplayName], [MailboxGuid], [ServerName], [DatabaseName], [IsArchiveMailbox], [DeletedItemCount], [DeletedItemSize], [ItemCount], [MailboxSize]) VALUES ('$( $RunDate )', '$( $DisplayName )', '$( $MailboxInfo.MailboxGuid.guid )', '$( $MailboxInfo.ServerName )', '$( $MailboxInfo.DatabaseName )', $( $ArchiveBit ), $( $MailboxInfo.DeletedItemCount ), $( $MailboxInfo.DeletedSize), $( $MailboxInfo.ItemCount ), $( $MailboxInfo.MailboxSize))" $SqlCommand.CommandText = $SqlInsert $SqlCommand.ExecuteNonQuery() | Out-Null $MailboxInfo = $Mailbox | Get-MailboxStatistics -Archive -ErrorAction SilentlyContinue | Select-Object DisplayName, MailboxGuid, ServerName, DatabaseName, IsArchiveMailbox, DeletedItemCount, @{ Name = "DeletedSize"; Expression = { $_.TotalDeletedItemSize.Value.ToBytes() } }, ItemCount, @{ Name = "MailboxSize"; Expression = { $_.TotalItemSize.Value.ToBytes() } } if ( $MailboxInfo ) { $ArchiveBit = 1 $SqlInsert = "INSERT INTO [dbo].[MailboxStats] ([RunDate], [DisplayName], [MailboxGuid], [ServerName], [DatabaseName], [IsArchiveMailbox], [DeletedItemCount], [DeletedItemSize], [ItemCount], [MailboxSize]) VALUES ('$( $RunDate )', '$( $DisplayName )', '$( $MailboxInfo.MailboxGuid.guid )', '$( $MailboxInfo.ServerName )', '$( $MailboxInfo.DatabaseName )', $( $ArchiveBit ), $( $MailboxInfo.DeletedItemCount ), $( $MailboxInfo.DeletedSize), $( $MailboxInfo.ItemCount ), $( $MailboxInfo.MailboxSize))" $SqlCommand.CommandText = $SqlInsert $SqlCommand.ExecuteNonQuery() | Out-Null } } $k++ } Write-Progress -Activity "Collecting Mailbox Information" -CurrentOperation "$( $Mailbox.DisplayName )" -Id 3 -ParentId 2 -Completed -Status "Completed" $j++ } $i++ Write-Progress -Activity "Collecting Database Information" -CurrentOperation "$( $Database.Name )" -Id 2 -ParentId 1 -Completed -Status "Completed" } Write-Progress -Activity "Collecting Server Information" -CurrentOperation "CurrentOperation" -Id 1 -Completed -Status "Completed" #endregion #region Close SQL Connection $SqlConnection.Close() $SqlConnection.Dispose() #endregion
Once the script is run, each record will look something like this:
Id | RunDate | MailboxGuid | DisplayName | ServerName | DatabaseName | IsArchiveMailbox | DeletedItemCount | DeletedItemSize | ItemCount | MailboxSize |
---|---|---|---|---|---|---|---|---|---|---|
20501 | 2014-02-25 00:00:00.000 | 69633637-b359-4654-8dad-eee2dab98502 | Sparenberg, Kevin M. | MBXSERVER02v | DB02 | 0 | 10069 | 40603360 | 63191 | 1249148371 |
20502 | 2014-02-25 00:00:00.000 | 38c4add6-4dad-486a-afae-0770136fafd3 | Sparenberg, Kevin M. | MBXSERVER02v | DB05 | 1 | 652 | 1879831 | 11985 | 264359378 |
You can see that there are separate records for the Primary and Archive Mailbox. This script should run in either Exchange 2010 or Exchange 2013, though I’ve not had opportunity to check on the latter as yet.
The script is pretty straight forward, but if you have any questions, all you need to do is ask.
Until next time, Ramblers.
Helpful article, thanks for the wonderful information, that provides the script related to exchange mailbox size report, I tested this tool from http://www.lepide.com/exchange-reporter/ that allows to generate of exchange mailbox reports as per requirement and view status of scheduled reports (success or failure). This tool helps to analyze mailboxes, attachment size, mailbox folder, and Outlook Web access (OWA) with the help of the reports generated and filter the reports up to the granular level.
Jon:
Thanks for the note. The tool does look interesting, but I’m partial to SolarWinds Server and Application Monitor with AppInsight for Exchange (Demo Here) when it comes to large scale reporting on environments.
This was a script that I wrote prior to gain this feature.
–K