Mailbox Database Statistics
UPDATED: Minor Script Modifications made, detailed help comments added mainly for PSv2 and units added to property names
First of all, let me apologize as I have been posting very much lately. My plan with this blog was to post at least twice a week. Obviously my posting has slowed way down. I haven't at all lost interest in sharing with you, I just have run a bit low on ideas for the simpler, easier to write posts. These days, the subjects I post about are tending to be more complex scripts and by their nature, they take a lot longer to write. That being said, if anyone of the 3 of you out there reading this blog :) have any ideas for posts you would like to see, PLEASE comment on here or email me to ask for a post. I need subjects to post about that I know there is interest to read about.
Enough with the sob story…lets get to it.
This post is about a script I wrote last night to generate some mailbox database statistics that I think will be useful to many out there. I must thank Wayne Siegler as he was the inspiration for this script since he contacted me through this blog for help in writing something similar. Thanks Wayne for asking, as it got to me write this and be able to share it with others.
This script basically walks through your Exchange 2007 and above databases and gets their file size…but wait…there’s more. I already posted a one-liner to do that. This script though, then looks at the mailboxes on that database and generates stats about the total size of those mailboxes, the average size, and the max size. As well it gives you counts. It does this for all your databases and then at the end it simply looks at all the data its gathered so far and totals it all up to give you the org-wide stats. Again though, this right now only works against Exchange 2007 and above DBs. It could be modified to work against pre-Exchange 2007 DBs as well, but it would take a bit more code (the samples for what is needed can be found in others posts on this blog) and for now I want to keep the code a bit simpler.
Folks, like all my posts, this is no production-level script. I haven't built in any error checking, help information, parameters, etc. into this. This is meant to be a demonstration of some techniques that can be used to get this data and output it as an object.
Here is the code:
# .SYNOPSIS # This script gather statistics about Mailbox Databases # # .DESCRIPTION # This script gathers the following database statistics about Exchange # 2007 and above mailbox databases: # # - Mailbox Count on Database # - Database Physical FileSize # - Total of Mailbox Sizes # - Average Size of Mailboxes # - Largest Mailbox on Database # # The script also totals the gathered statistics from all databases # and adds the results to the end of the resultant array output. This # Script outputs a custom object to the success pipeline and its # results can be piped to any typical cmdlet like export-csv or out-file # # This script was created by Gary Siepser of Microsoft. This script is # provided "AS IS" with no warranties, and confers no rights. Use of # any portion or all of this script are subject to the terms specified # at https://www.microsoft.com/info/cpyright.htm. # # .INPUTS # None. You cannot pipe objects to this script # # .OUTPUTS # PSCustomObject with NoteProperties for each of the statistics # # .EXAMPLE # C:\PS> .\script.ps1 # # The script run alone will output in List format. # # .EXAMPLE # C:\PS> .\script.ps1 | Format-Table -Autosize # # This example will out a formatted table of the results. # # .EXAMPLE # C:\PS> .\script.ps1 | Export-Csv c:\export.csv # # This example will export the piplined object to a CSV file. # # .EXAMPLE # C:\PS> Get-Help .\script.ps1 -Full # # This example will retrieve the full help of this script. #Create an empty template object to be used as the blueprint of the final output objects $TemplateObject = New-Object PSObject | Select-Object DatabaseName,DatabaseFileSizeGB,TotalMailboxSizeGB,NumberofMailboxes,AverageMailboxSizeMB,MaxMailboxSizeMB #Create the empty array to hold the Output Results $ResultSet = @() #Retrieve all DB Objects and loop through them $databases = get-mailboxdatabase foreach ($DB in $databases) { #Create a copy of the Template $TempObject = $TemplateObject | Select-Object * #Get the DB File Size Info $DBRawFileInfo = get-wmiobject cim_datafile -computername $DB.server -filter ('name=''' + $DB.edbfilepath.pathname.replace("\","\\") + '''') $DBFileSize = [math]::Round([Decimal]( $DBRawFileInfo.filesize / 1GB),2) #Get Stats about the mailboxes in this database $AllMailboxstats = get-mailboxstatistics -database $DB.identity | Where-Object {$_.objectclass -eq "Mailbox"} $CombinedMailboxSizes = @() foreach ($mailbox in $AllMailboxstats) { $CombinedMailboxSizes += ($mailbox.totalitemsize.value.ToBytes() + $mailbox.totaldeleteditemsize.value.ToBytes()) } $Stats = $CombinedMailboxSizes | Measure-Object -Average -Maximum -Sum #Save all this data we have into the template object $TempObject.DatabaseName = $DB.Name $TempObject.DatabaseFileSizeGB = $DBFileSize $TempObject.TotalMailboxSizeGB = [math]::Round($Stats.sum / 1GB ,2) $TempObject.NumberofMailboxes = $Stats.count $TempObject.AverageMailboxSizeMB = [math]::Round($Stats.average / 1MB,2) $TempObject.MaxMailboxSizeMB = [math]::Round($Stats.maximum / 1MB,2) #Add the TempObject to the Result Array for final output $ResultSet += $TempObject } #Compute Organizational Totals From Existing ResultSet #Create a copy of the Template $TempObject = $TemplateObject | Select-Object * $AllDBFilesizeGB = $ResultSet | Measure-Object DatabaseFileSizeGB -Sum $AllMailboxSizeGB = $ResultSet | Measure-Object TotalMailboxSizeGB -Sum $AllMailboxCount = $ResultSet | Measure-Object NumberofMailboxes -Sum $AllAverageMailboxSizeMB = $ResultSet | Measure-Object AverageMailboxSizeMB -Average $AllMaxMailboxSizeMB = $ResultSet | Measure-Object MaxMailboxSizeMB -Max #Save all this data we have into the template object $TempObject.DatabaseName = "All Databases (Pre-Exchange 2007 not included)" $TempObject.DatabaseFileSizeGB = $AllDBFilesizeGB.Sum $TempObject.TotalMailboxSizeGB = $AllMailboxSizeGB.Sum $TempObject.NumberofMailboxes = $AllMailboxCount.Sum $TempObject.AverageMailboxSizeMB = $AllAverageMailboxSizeMB.Average $TempObject.MaxMailboxSizeMB = $AllMaxMailboxSizeMB.Maximum #Add the TempObject to the Result Array for final output $ResultSet += $TempObject $ResultSet
I hope this helps
-Gary Siepser
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.