PoshCode Logo PowerShell Code Repository

Get-ExchangeDBSizes by Karl Mitschke 36 months ago
View followups from Karl Mitschke and senjoy | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1902"></script>download | new post

Gathers data from Exchange mailbox servers.
These data include:
Server\StorageGroup\Database (2007) or Database (2010),
Total Size (in GB) of the disk,
Size of the .edb file (in GB),
Free disk space,
Percent Disk Free,
Total Mailboxes on the database,
White Space,
Total Free disk space,
Total Percent Free disk space

  1. <#
  2. .SYNOPSIS
  3.         Get-ExchangeDBSizes - Gather data on Exchange 2007 / 2010 Mailbox Databases.
  4. .DESCRIPTION
  5.         Gathers data from Exchange mailbox servers.
  6.                 These data include:
  7.                 Server\StorageGroup\Database (2007) or Database (2010),
  8.                 Total Size (in GB) of the disk,
  9.                 Size of the .edb file (in GB),
  10.                 Free disk space,
  11.                 Percent Disk Free,
  12.                 Total Mailboxes on the database,
  13.                 White Space,
  14.                 Total Free disk space,
  15.                 Total Percent Free disk space
  16. .EXAMPLE
  17.         Get-ExchangeDBSizes
  18. .LINK
  19.                 http://unlockpowershell.wordpress.com/
  20.                 http://poshcode.org/1901
  21. #>
  22.  
  23.  
  24. Function Get-MountPointInfo($ServerName) {
  25. $Summary = @()
  26. $VolumeHash = @{}
  27. $VolumeCapacityHash = @{}
  28. $DatabaseSizeHash = @{}
  29. $DatabaseFreeHash = @{}
  30. $MountPoints = Get-WmiObject -Class Win32_Mountpoint -ComputerName $ServerName
  31. $MailboxDatabases = Get-MailboxDatabase -Server $ServerName
  32. $Volumes = Get-WmiObject -Class Win32_Volume -ComputerName $ServerName | Where-Object {$_.DriveType -eq 3}| Select-Object Name,FreeSpace,Capacity
  33. $DatabaseLetters = $MailboxDatabases | Select-Object edbfilepath
  34. foreach ($Volume in $Volumes) {
  35. $VolumeHash.Add($Volume.name.TrimEnd("\"),$Volume.FreeSpace)
  36. $VolumeCapacityHash.Add($Volume.name.TrimEnd("\"),$Volume.Capacity)
  37. }
  38. foreach ($drive in $DatabaseLetters)
  39. {
  40.         $letter = $drive.EdbFilePath.Substring(0,$drive.EdbFilePath.lastIndexOf("\"))
  41.         $DatabaseSizeHash.Add($letter.TrimEnd("\"),$VolumeCapacityHash[$letter])
  42.         $DatabaseFreeHash.Add($letter.TrimEnd("\"),$VolumeHash[$letter])
  43. }
  44. $VolumeHash.Clear()
  45. $VolumeCapacityHash.Clear()
  46. #Now I have all mailbox databases, along with the OS reported free space and capacity
  47. foreach ($MP in $Mountpoints) {
  48. $MP.directory = $MP.directory.replace("\\","\").Split("=")[1].Replace("`"","")
  49. if($DatabaseSizeHash[$MP.directory])
  50. {
  51. #This mountpoint is a database
  52. $Record = new-Object PSObject
  53. $OSfree = $DatabaseFreeHash[$MP.directory]
  54. $OSCapacity = $DatabaseSizeHash[$MP.directory]
  55. $DestFolder = "\\"+$ServerName + "\" + $MP.directory
  56. $colItems = (Get-ChildItem $destfolder.Replace(":","$") -Recurse| where{$_.length -ne $null} |Measure-Object -property length -sum)
  57. if($colItems.sum -eq $null) {
  58. $fsize = 0
  59. } else {
  60. $fsize = $colItems.sum
  61. }
  62. $TotFolderSize = $fsize + $OSfree
  63. $percFree = "{0:P0}" -f ( $OSfree/$TotFolderSize)
  64. $Record | add-Member -memberType noteProperty -name Server -Value $ServerName
  65. $Record | add-Member -memberType noteProperty -name "Mount Point" -Value $MP.directory
  66. $Record | add-Member -memberType noteProperty -name "Capacity" -Value ("{0:N2}" -f ($OSCapacity /1gb))
  67. $Record | add-Member -memberType noteProperty -name "Used" -Value ("{0:N2}" -f ($fsize / 1gb))
  68. $Record | add-Member -memberType noteProperty -name "Free" -Value ("{0:N2}" -f ($OSfree / 1gb))
  69. $Record | add-Member -memberType noteProperty -name "Percent Free" -Value $percFree
  70. $Summary += $Record
  71. }
  72. }
  73. return $Summary
  74. }
  75. function Get-ExchangeWhiteSpace {
  76. param(
  77.    $ComputerName = $(throw "ComputerName cannot be empty.")
  78. )
  79.  
  80. # Convert Dates to WMI CIM dates
  81. $tc = [System.Management.ManagementDateTimeconverter]
  82. $Start =$tc::ToDmtfDateTime( (Get-Date).AddDays(-1).Date )
  83. $End =$tc::ToDmtfDateTime( (Get-Date).Date)
  84.  
  85. $whiteSpace = @{}
  86.  
  87. # Create two claculated properties for InsertionStrings values
  88. $DB = @{Name="DB";Expression={$_.InsertionStrings[1]}}
  89. $FreeMB = @{Name="FreeMB";Expression={[int]$_.InsertionStrings[0]}}
  90.  
  91. $freespace = Get-WMIObject Win32_NTLogEvent -ComputerName $ComputerName -Filter "LogFile='Application' AND EventCode=1221 AND TimeWritten>='$Start' AND TimeWritten<='$End'" | Select-Object $DB,$FreeMB | Sort-Object DB –Unique –Descending
  92. $freespace | ForEach-Object {$whiteSpace.Add($_.DB,$_.FreeMB)}
  93. #Preceding function from Shay Levy http://blogs.microsoft.co.il/blogs/scriptfanatic/archive/2009/08/13/exchange-white-space.aspx
  94. }
  95. $AllServers = @()
  96. $ServerVersion = @{}
  97. foreach ($server in Get-MailboxServer)
  98. {
  99.         $ServerName = $server.name
  100.         $version = Get-ExchangeServer -Identity $ServerName | Select-Object AdminDisplayVersion
  101.         if($version.admindisplayversion.major)
  102.         {
  103.                 $ServerVersion.Add($ServerName,$version.admindisplayversion.major)
  104.         }
  105.         else
  106.         {
  107.                 $ServerVersion.Add($ServerName,$version.admindisplayversion.Split()[1].split(".")[0])
  108.         }      
  109. }
  110. foreach ($server in Get-MailboxServer)
  111. {
  112.         $ServerName = $server.Name
  113.         if ([int]$ServerVersion[$ServerName] -gt 8)
  114.         { #An Exchange 2010 server, so use Get-MailboxDatabase <database> -Status | Select-Object AvailableNewMailboxSpace
  115.                 $whiteSpace = @{}
  116.                 $Free = Get-MailboxDatabase -Server $ServerName -Status | Select-Object Server,Name,AvailableNewMailboxSpace
  117.                 foreach ($item in $free)
  118.                 {
  119.                         $db = $ServerName+"\"+$item.Name
  120.                         $whiteSpace.Add($item.Name,[int]$item.AvailableNewMailboxSpace.split("(")[1].Split()[0]/1mb)
  121.                 }
  122.         }
  123.         Else
  124.         {#not an Exchange 2010 server
  125.                 . Get-ExchangeWhiteSpace $ServerName
  126.         }
  127.         $MountPoint = . Get-MountPointInfo $ServerName
  128.         foreach ($objItem in Get-MailboxDatabase -Server $ServerName)
  129.     {
  130.     $edbfilepath = $objItem.edbfilepath
  131.      $path = "`\`\" + $ServerName + "`\" + $objItem.EdbFilePath.Split(":")[0] + "$"+ $objItem.EdbFilePath.Split(":")[1]
  132.     $dbsize = Get-ChildItem $path
  133.         $dbpath=(Split-Path $edbfilepath.Split(":")[1] -Leaf).trimend(".edb")
  134.         if (!$MountPoint)
  135.         {
  136.                 $mailboxpath = $ServerName + $edbfilepath.Split(":")[1].trimend(".edb")
  137.                 $size = get-wmiobject -computername $ServerName win32_logicaldisk |where-object {$_.deviceid -eq $objItem.EdbFilePath.Split("\")[0]} |select-object deviceID, Freespace, Size
  138.                 $freespace = ($size.freespace / 1GB)
  139.                 $total = ($size.size / 1GB)
  140.         }
  141.         if ($MountPoint)
  142.         {
  143.                 $mailboxpath = "$ServerName\$dbpath"
  144.                 $MPPath = $EdbFilePath.Substring(0,$EdbFilePath.LastIndexOf("\"))
  145.                 $freespace = $DatabaseFreeHash[$MPPath ] /1GB
  146.                 $total = $DatabaseSizeHash[$MPPath] /1GB
  147.         }
  148.         $PercentFree = "{0:n2}" -f ($freespace / $total *100)
  149.         if (!$MountPoint)
  150.         {
  151.                 $mailboxcount = Get-MailboxStatistics -database "$mailboxpath"  |Where {$_.DisconnectDate -eq $null -and $_.ObjectClass -eq 'Mailbox'} |measure-object
  152.         }
  153.         if($MountPoint)
  154.         {
  155.                 $mailboxcount = Get-MailboxStatistics -database $mailboxpath.Split("\")[1]  |Where {$_.DisconnectDate -eq $null -and $_.ObjectClass -eq 'Mailbox'} |measure-object
  156.         }
  157.        
  158.         $ReturnedObj = New-Object PSObject
  159.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Server\StorageGroup\Database" -Value $objItem.Identity
  160.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Total Size (GB)" -Value ("{0:n2}" -f ($total))
  161.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Used Space (GB)" -Value ("{0:n2}" -f ($dbsize.Length/1024MB))
  162.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Free Space (GB)" -Value ("{0:n2}" -f ($freespace))
  163.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Percent Disk Free" -Value $percentfree
  164.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "User Mailbox Count" -Value $mailboxcount.count
  165.         if ($objitem.Identity.Split("\").Count -eq 3){$dbasename = $objitem.Identity.Substring($objitem.Identity.IndexOf("\")+1)}
  166.         else{$dbasename = $objitem.Identity}
  167.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "White Space (GB)" -Value ("{0:n2}" -f ($whiteSpace[$dbasename]/1024))
  168.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Total Free (GB)" -Value ("{0:n2}" -f ($freespace + $whiteSpace[$dbasename]/1024))
  169.         $TotalPercent = ($freespace + $whiteSpace[$dbasename]/1024) / $total *100
  170.         $ReturnedObj | Add-Member -MemberType NoteProperty -Name "Total Percent Free" -Value ("{0:n2}" -f ($TotalPercent))
  171.     $AllServers += $ReturnedObj
  172.         }
  173. }
  174. $AllServers |Export-Csv C:\scripts\MailboxDatabaseSizeReport.csv -NoTypeInformation

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me