# Combining two psobject tables(arrays) into one

**URL:** <https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007>\
**Category:** PowerShell Help\
**Created:** [September 25, 2020, 8:15am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007 "2020-09-25T08:15:37Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 25, 2020, 8:15am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/1 "2020-09-25T08:15:37Z")

</div>

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 🙂 ) 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?

---

<div class="post-metadata">

**Author:** ![sam-boutros](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/sam-boutros/32/5_2.png) [@sam-boutros](https://forums.powershell.org/u/sam-boutros)\
**Post date:** [September 25, 2020, 9:05am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/2 "2020-09-25T09:05:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 25, 2020, 9:21am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/3 "2020-09-25T09:21:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 25, 2020, 9:31am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/4 "2020-09-25T09:31:12Z")

</div>

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
```

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [September 25, 2020, 10:11am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/5 "2020-09-25T10:11:56Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 25, 2020, 11:04am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/6 "2020-09-25T11:04:38Z")

</div>

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 |

&nbsp;

---

<div class="post-metadata">

**Author:** ![sam-boutros](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/sam-boutros/32/5_2.png) [@sam-boutros](https://forums.powershell.org/u/sam-boutros)\
**Post date:** [September 27, 2020, 1:06am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/7 "2020-09-27T01:06:09Z")

</div>

```
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)
    })
}
```

---

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 29, 2020, 3:39am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/8 "2020-09-29T03:39:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ps\_sob](https://avatars.discourse-cdn.com/v4/letter/p/48db29/32.png) [@ps\_sob](https://forums.powershell.org/u/ps_sob)\
**Post date:** [September 29, 2020, 5:31am UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/9 "2020-09-29T05:31:52Z")

</div>

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
```

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:28pm UTC](https://forums.powershell.org/t/combining-two-psobject-tables-arrays-into-one/15007/10 "2024-05-16T20:28:47Z")

</div>

Closed on May 16, 2024, 8:28pm UTC


