Exchange 2010 DAG DB Distribution Report

This script generates a report on your current DBs, how they are distributed, if they are having issues, etc.. It would be as if you ran Get-MailboxDatabaseCopyStatus and generated a report on their activation preference, current status (mounted, healthy, initializing, etc.), and finally counts (# of copies, # of mailboxes on DB).

---

 Thursday, January 09, 2014 7:10:46 AM

dag1

DB	DAG1S01	DAG1S02	DAG1S03	DAG1S04	DAG1S05	DAG1S06	DB Copies	Mbx Count
HR-DB01		3	1*			2	3	80
SALES-DB01	1*	2	3				3	250

---

This script takes 2 variables:

• DAGNames - name(s) of the DAGs you want the report on. 
• FilePath - File path for where to save the HTML report. If left blank, it will put the report in the local drive. 

If you put in multiple DAGs, it will generate an additional report that includes all DAGs that you specified. I have a schedule task that runs the script against all DAGs here, then emails the HTML file to me for review each morning. This report is a great way to see if a server went down and the DBs rolled to another server (red = not mounted on activation preference 1), and see if anything is dismounted or in failing state.

<#
.SYNOPSIS
   .\Report-DAGDBDistribution.ps1 - Create Graphical report of DAG DB distribution
.DESCRIPTION
   Reads DAG DB layout, then generates an HTML report detailing what server each DB has a copy on and their activation preference.
.PARAMETER 
   DAGName - Simple Name of a DAG
.EXAMPLE
   .\Report-DAGDBDistribution.ps1 -DAGName DAG5
.SOURCE
 http://stackoverflow.com/questions/9397137/powershell-multidimensional-arrays
#>

[CmdLetBinding()]
param(
 [parameter(Mandatory=$true)]
 [string[]]$DAGNames,
 [parameter()]
 [string]$FilePath
)


#Cell color for each Activation Preference on the DB. 
$CellColor = @{1 = "#ffff00";2 = "#00ff00";3 = "#ff00ff";4 = "#00ffff";5 = "#ffffff";6 = "#ffffff"}
$errorColor = "#FF0000"  

if ($filepath -eq "" ) {$filepath = ".\"}
if ($filepath -notlike "*\") {$filepath += "\"}
if ($DagNames -eq "") {break}
$DoAllInOne = ($DagNames.count -gt 1)


#Return # of mailboxes per DB. 
function Get-MDBMailboxCount ([string]$DN) {
 $Searcher = New-Object System.DirectoryServices.DirectorySearcher
 $Searcher.SearchRoot = New-Object System.DirectoryServices.DirectoryEntry ("LDAP://$(([system.directoryservices.activedirectory.domain]::GetCurrentDomain()).Name)")
 $Searcher.Filter = "(&(objectClass=user)(homeMDB=$DN))"
 $Searcher.PageSize = 10000
 $Searcher.SearchScope = "Subtree"
 $results = $Searcher.FindAll()
 $returnValue = $results.Count
 #dispose of the search and results properly to avoid a memory leak
 $Searcher.Dispose()
 $results.Dispose()
 return $returnValue
}




if ($doAllInOne) {
 $AIOreportName = $filepath+"DAGReport.HTML"
 Get-Date | Out-File -FilePath $AIOreportName
 '' | Out-File -FilePath $AIOreportName -Append 
}


foreach ($dagname in ($DagNames | Sort)) {
 $DagMBXCount = 0
 #Reads list of current server names in the DAG
 $serverList = (Get-DatabaseAvailabilityGroup $DagName).servers | sort name| %{$_.name} 
 if ($serverList -eq $null) {
  Write-Host "ERROR: No servers found in DAG: $DAGNAME" -ForegroundColor red 
  break}

 #reads Unique names of DBS on these servers 
 $dbs = $serverList | %{Get-MailboxDatabase -server $_} | sort name | select -Unique Name | %{$_.name}

 #Build HashTable for report
 $hashtable = @{}
 ForEach ($s in $dbs) {$hashtable[$s] = @{}} #Populate, then add a second dimension
 ForEach ($db in $dbs) {
  $ap = (get-mailboxdatabase $db).ActivationPreference | sort key
  ForEach ($k in $ap) {
   $hashtable[$db][$k.key.name] = $k.value 
  }
 }

 #Build Report
 if ($FilePath -eq "") { 
  $reportName = ".\"+$DagName + ".HTML"
 } else {
  $reportName = $filepath+$DagName + ".HTML"
 }
 
 "

"+$DAGName + "
" | Out-File -FilePath $reportName
 ForEach ($s in $serverList) {
  "" | Out-File -FilePath $reportName -Append
 }
 "" | Out-File -FilePath $reportName -Append 
 ForEach ($db in $dbs) {
  $WhereMounted = (Get-MailboxDatabaseCopyStatus $db | ?{$_.status -eq "mounted"}).activedatabasecopy
  "" | Out-File -FilePath $reportName -Append 
  ForEach ($s in $serverList) {  
   $htv = $hashtable[$db][$s]
   if ( $htv -eq $null) {
    #no copy of this DB on this Server. 
    "" | Out-File -FilePath $reportName -Append 
   } else {
    #create cell and color based on Activation Preference
    $strHT = [string]$htv
    $namestr = $db + "\"+$s    
    $dbstatus = (Get-MailboxDatabaseCopyStatus $namestr).status    
    #Add db status into the table if not mounted or healthy.
    Switch ($dbstatus) {
     "Healthy" {}
     "Mounted" {}
     default {$strHT += "
"+$dbstatus+""}
    }
    
    #indicate if copy is active.
    $strColor = $CellColor[$htv]
    if ($s -eq $WhereMounted) {
     $strHT += "*"
     #Show error color if not active on AP 1. 
     if ($htv -ne 1) { $strColor = $errorColor}     
    }    
    "" | Out-File -FilePath $reportName -Append 
   }
  }
  
  ""  | Out-File -FilePath $reportName -Append 
  $MbxCount = Get-MailboxDatabase $db | %{Get-MDBMailboxCount $_.distinguishedname}
  $DagMbxCount += $mbxCount
  "" | Out-File -FilePath $reportName -Append 
  "" | Out-File -FilePath $reportName -Append 
 }
 "" | Out-File -FilePath $reportName -Append 
 ForEach ($s in $serverList) {
  "" | Out-File -FilePath $reportName -Append
 }
 "" | Out-File -FilePath $reportName -Append 
 "" | Out-File -FilePath $reportName -Append 
 "

DB
"+$s + "
DB Copies
Mbx Count

"+$db + "

"+$strHT + "
" + [string](Get-MailboxDatabaseCopyStatus $db).count+"
" + [string]($mbxCount) +"
"+ ((Get-DatabaseAvailabilityGroup $DAGName).servers | %{Get-MailboxDatabase -Server $_.name}  | select -Unique Name).count+"
"+(Get-MailboxDatabase -server $s).count + "
Total Mailboxes: "+[string]$DagMBXCount+"

" | Out-File -FilePath $reportName -Append 
 "
* = active db
" | Out-File -FilePath $reportName -Append 
 
 if ($DoAllinOne) {      #Add report to all-in-one report.
  Get-Content $reportName | Out-File -FilePath $AIOreportName -Append
  #Add Page Break for printout. 
  '

' | Out-File -FilePath $AIOreportName -Append
 }
 
 #Display report on screen.
 Invoke-Expression $reportName
}
if ($doAllInOne) {
 #Open All In One DAG Report
 Invoke-Expression $AIOreportName
}