Combining two psobject tables(arrays) into one

PowerShell Help
1

Hey guys, struggling a little on a script I’m trying to write that is meant to gather various stats across our environment into a single exec summary report and am hoping someone here could shed a little light on how to accomplish what I’m trying to accomplish.

I have two separate pieces, one that collects vmware stats and one that collects netapp stats. Here is my code for that:

Connect-VIServer -Server vcenter1, vcenter2

$myVCCol = @()
foreach($cluster in (get-cluster | sort Name)){
$ClusterVMs = $Cluster | Get-VM
$ClusterCPUCores = $Cluster.ExtensionData.Summary.NumCpuCores
$ClusterAllocatedvCPUs = ($ClusterVMs | Measure-Object -Property NumCPu -Sum).Sum
$ClusterEffectiveMemoryGB = [math]::round(($Cluster.ExtensionData.Summary.EffectiveMemory / 1KB),0)
$ClusterAllocatedMemoryGB = [math]::round(($ClusterVMs | Measure-Object -Property MemoryMB -Sum).Sum / 1KB)
$vcstats = New-Object -TypeName psobject -Property @{
date = Get-Date -Format ddMMyyyy
cluster = $cluster.Name
clustercores = $ClusterCPUCores
clusterassignedcores = $ClusterAllocatedvCPUs
clusterram = $ClusterEffectiveMemoryGB
clusterassignedram = $ClusterAllocatedMemoryGB
}
$vcstats = $vcstats | select date, cluster, clustercores, clusterassignedcores, clusterram, clusterassignedram
$myvccol += $vcstats
}

$nacs = “nac1”,“nac2”
$myNACol = @()
foreach($nac in $nacs){
Connect-NcController $nac -Credential $creds
$filerTtl = 0
$filerAvail = 0
$filerUsed = 0
foreach($aggr in (get-ncaggr | sort name)){
$aggrTtl = [math]::Round($aggr.TotalSize / 1TB, 2)
$aggrAvail = [math]::Round($aggr.Available / 1TB, 2)
$aggrUsed = $aggrTtl - $aggrAvail
$filerttl += $aggrTtl
$filerAvail += $aggrAvail
$filerUsed += $aggrUsed
}
$nastats = New-Object -TypeName psobject -Property @{
date = Get-Date -Format ddMMyyyy
filer = $nac
filerttlspaceTB = $filerTtl
filerusedspaceTB = $filerUsed
fileravailspaceTB = $filerAvail
}
$nastats = $nastats | select date, filer, filerttlspaceTB, filerusedspaceTB, fileravailspaceTB
$myNACol += $nastats
}

Both the $myVCCol and $myNACol objects are reporting all the data I need correctly, I’m just struggling on how to get them together into a single object that I can then send in a single table via an html email (much praise to Milo for their send-htmlemail function).

I’m no powershell expert (as is probably apparent by my code :slight_smile: ) but I’ve tried stuff like $myVCCol += $myNACol and $mycols = $myVCCol, $myNCCol and doing a foreach loop to populate a new object, but neither is giving me my desired results.

Something tells me I’m missing something obvious but for whatever reason, I just can’t seem to figure it out. Any pointers?

2

There’s no common property between the two tables/arrays. This is essentially not a PowerShell question but a SQL database join question.

3

Thanks you for your reply Sam. Since I built both tables as a psobject, if I were to create each table to have all the same properties, would that make it doable? I have 0 SQL experience so I’m not sure I would be able to go down that route just yet.

4

maybe something like this -

vcstats = New-Object -TypeName psobject -Property @{
date = Get-Date -Format ddMMyyyy
cluster = $cluster.Name
clustercores = $ClusterCPUCores
clusterassignedcores = $ClusterAllocatedvCPUs
clusterram = $ClusterEffectiveMemoryGB
clusterassignedram = $ClusterAllocatedMemoryGB
filer = $null
filerttlspaceTB = $null
filerusedspaceTB = $null
fileravailspaceTB = $null
}
$vcstats = $vcstats | select date, filer, filerttlspaceTB, filerusedspaceTB, fileravailspaceTB, cluster, clustercores, clusterassignedcores, clusterram, clusterassignedram

$nastats = New-Object -TypeName psobject -Property @{
date = Get-Date -Format ddMMyyyy
filer = $nac
filerttlspaceTB = $filerTtl
filerusedspaceTB = $filerUsed
fileravailspaceTB = $filerAvail
cluster = $null
clustercores = $null
5

Rather than trying to join data that cannot be correlated, you can send an email like this (there is no need for a Send-HTMLEmail function):

$html = @"
<html>
<head></head>
<body>

Clusters:

$($myvccol | ConvertTo-Html -Fragment)


Controllers:

$($myNACol | ConvertTo-Html -Fragment)
</body>
</html>
"@

$mailParams = @{
    To = 'foo@company.com'
    From ='fah@company.com'
    Subject = 'VM Stuff'
    Body = ($html | Out-String)
    BodyAsHtml = $true 
    SmtpServer = 'relay.company.com'

}

Send-MailMessage @mailParams

If you were getting controller information for each VM, that makes sense to join it so you see correlated information for VM. Imagine you have something like this:

PS C:\Users\rasim> $csv

Color 
----- 
Red   
Purple
Orange



PS C:\Users\rasim> $csv2

Fruit 
----- 
Orange
Apple 
Pear  
Grape

How can you correlate those two seperate datasets? If you ‘joined’ on Color, only Orange would work.

PS C:\Users\rasim> $csv

Id Color 
-- ----- 
1  Red   
2  Purple
3  Orange



PS C:\Users\rasim> $csv2

id  Fruit  ColorId
--  -----  -------
122 Orange 3      
124 Apple  1      
126 Pear          
127 Grape  2

Now there is a ColorId (i.e. Foreign Key) associated with Id (i.e. Primary Key) that we can join on. As Sam stated, there is no way to associate a VM to a specific controller in the code you provided. There needs to be a relationship. You can join objects with no association by adding controller information to every VM, but what are you gaining?

6

Thank you Rob for your reply. I was able to confirm your first example works however, this is an executive summary report meant for my bosses boss who has grown accustom to the pretty tables that Milo’s send-htmlemail function outputs. The only real formatting requirements that I’ve been given is that it looks like all the other reports I’ve created using the aforementioned function and that it be in a single email. Here is a rough draft of what they are expecting the output to look like:

Date Name Total pCores Assigned vCores Total RAM Assigned RAM Total Space TB Used Space TB Avail. Space TB
09252020 cluster1 64 222 1480 1040 n/a n/a n/a
09252020 cluster2 36 124 2200 1700 n/a n/a n/a
09252020 filer1 n/a n/a n/a n/a 97 77 20
09252020 filer2 n/a n/a n/a n/a 40 35 15
 
7 
Connect-VIServer -Server vcenter1, vcenter2

$Stats = foreach($cluster in (get-cluster | sort Name)){
    $ClusterVMs = $Cluster | Get-VM
    New-Object -TypeName psobject -Property ([Ordered]@{
        Date              = Get-Date -Format ddMMyyyy
        Name              = $cluster.Name
        'Total pCores'    = $Cluster.ExtensionData.Summary.NumCpuCores
        'Assigned vCores' = ($ClusterVMs | Measure-Object -Property NumCPu -Sum).Sum
        'Total RAM'       = [math]::round(($Cluster.ExtensionData.Summary.EffectiveMemory / 1KB),0)
        'Assigned RAM'    = [math]::round(($ClusterVMs | Measure-Object -Property MemoryMB -Sum).Sum / 1KB)
        'Total Space TB'  = 'n/a'
        'Used Space TB'   = 'n/a'
        'Avail Space TB'  = 'n/a'
    })
}

$nacs = "nac1","nac2"

$Stats += foreach($nac in $nacs){
    Connect-NcController $nac -Credential $creds
    $filerTtl = $filerUsed = 0
    foreach($aggr in (get-ncaggr | sort name)){
        $aggrTtl     = $aggr.TotalSize
        $aggrAvail   = $aggr.Available
        $filerttl   += $aggrTtl
        $filerAvail += $aggrAvail
    }
    New-Object -TypeName psobject -Property ([Ordered]@{
        Date              = Get-Date -Format ddMMyyyy
        Name              = $nac
        'Total pCores'    = 'n/a'
        'Assigned vCores' = 'n/a'
        'Total RAM'       = 'n/a'
        'Assigned RAM'    = 'n/a'
        'Total Space TB'  = [Math]::Round($filerTtl/1TB,2) # It's more accurate to round once at the end..
        'Used Space TB'   = [Math]::Round(($filerTtl-$filerAvail)/1TB,2)
        'Avail Space TB'  = [Math]::Round($filerAvail/1TB,2)
    })
}
8

Thank you for your reply Sam! This gets me 99% of the way there however the nacs are getting repeated in the output resulting in this table:

Date Name Total pCores Assigned vCores Total RAM Assigned Ram Total Space TB Used Space Tb Avail Space TB
29092020 cluster1 64 228 1480 1040 n/a n/a n/a
29092020 cluster2 36 134 2251 1710 n/a n/a n/a
nac1
29092020 nac1 n/a n/a n/a n/a 97 77 20
nac2
29092020 nac2 n/a n/a n/a n/a 40 17 23
I assumed it was something to do with the new-object being built outside of the nested foreach loop but when I put it inside the loop, it created even more duplicates. Any ideas what would be causing the blank duplicate rows?
9

Update - figured out that it was this line causing the blank rows

Connect-NcController $nac -Credential $creds

fixed it by doing this

Connect-NcController $nac -Credential $creds | out-null