problem putting different outputs into one array/hashtable in powershell

Question

I'm trying to get info on the status of several Exchange DBs on several Exchange servers using Get-MailboxDatabaseCopyStatus -Server $ExchSvr into one neat table in powershell.

so you get given this for each server:

Name	Status
DB1	Mounted
DB2	Healthy
...	...

and I just want to combine them and output it like this:

Name	Exch Svr1	Exch Svr2	...
DB1	Mounted	Healthy	...
DB2	Healthy	Mounted	...
...	...	...	...

I'm getting the server and database names with the following:

$ExchSvrs = (Get-ExchangeServer).name
$DBs = (get-mailboxdatabase).name

I'm struggling on the actual meat of it though as my first instinct is to create objects for each DB in a foreach loop and add a property for each exchange server then fill those in from a Get-MailboxDatabaseCopyStatus -Server $ExchSvr for each server. I understand that creating objects in a loop is discouraged so didn't do that.

I tried doing a hashtable of hashtables as that makes it easy to create the hashtables first then fill in the sections in a second loop but couldn't find an easy way to output that to a table without just going element by element through them which seems inefficient.

Next I just did an arraylist with new unnamed objects for each DB but then found it difficult to populate the properties of each object as each server has different DBs on it so I had to use where object every time which seemed really slow.

I feel like I'm fighting against powershell to do this but surely there's an easy way that I'm missing. I'll appreciate any help I can get and I'm happy to add any more info that people need.

Answer 1

You could start by organizing the status information in a nested hashtable:

# Discover all servers
$ExchServers = Get-ExchangeServer

# Create the "outer" table
$statusTable = @{}

# Fetch mailbox db copy status per server, store each copy status as an entry in an inner table
foreach($server in $ExchServers){
  # Create new inner table for this particular server
  $statusTable[$server.Name] = @{}

  foreach($dbCopyStatus in Get-MailboxDatabaseCopyStatus -Server $server){
    # Populate inner table
    $statusTable[$server.Name][$dbCopyStatus.Name] = $dbCopyStatus.Status
  }
}

Now we just need to transform the data into the desired format - one object per DB, with separate properties for each hosting server:

$outputPerDB = foreach($db in Get-MailboxDatabase){
  # create a table to hold the properties on the object we're about to create
  $properties = [ordered]@{ Name = $db.Name }
  foreach($server in $ExchServers){
    # grab property name from server, grab status from status table
    $properties[$server.Name] = $statusTable[$server.Name][$db.Name]
  }

  # turn property table into custom object
  [pscustomobject]$properties
}

The resulting objects will now be formatted as you expect:

PS ~> $outputPerDB |Format-List

Name ExchSrv1 ExchSrv2
---- -------- --------
DB1  Mounted  Healthy
DB2  Healthy  Mounted