How to perform "INNER JOIN" of powershell objects?

Question

I am trying to query for various physical disk health information.

I have two queries that each return separate information about PhysicalDisks.

>Get-PhysicalDisk | Select-Object DeviceId, FriendlyName, Model, SerialNumber, Usage, HealthStatus, CanPool, CannotPoolReason | Format-Table

DeviceId	Model	SerialNumber	FriendlyName	Usage	HealthStatus	CanPool	CannotPoolReason
0	WDC WD80EDAZ-11T	VGJKAJEG	ATA WDC WD80EDAZ-11T	Auto-Select	Healthy	False	In a Pool
1	WDC WD40EFRX-68N	WD-WCC7K6NE2J5J	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	True
2	WDC WD40EFRX-68N	WD-WCC7K5RYNVZD	ATA WDC WD40EFRX-68N	Hot Spare	Healthy	False	In a Pool
3	WDC WD80EDAZ-11T	VGGDU8GG	ATA WDC WD80EDAZ-11T	Auto-Select	Healthy	False	In a Pool
4	WDC WD80EDAZ-11T	VGKE7HJG	ATA WDC WD80EDAZ-11T	Auto-Select	Healthy	False	In a Pool
5	Samsung SSD 970 EVO 500GB	0025_385A_9150_212F.	Samsung SSD 970 EVO 500GB	Auto-Select	Healthy	True
6	WDC WD40EFRX-68N	WD-WCC7K5KP57ZJ	ATA WDC WD40EFRX-68N	Hot Spare	Healthy	False	In a Pool
7	WDC WD40EFRX-68N	WD-WCC7K1URKJV5	ATA WDC WD40EFRX-68N	Hot Spare	Healthy	False	In a Pool
8	WDC WD40EFRX-68N	WD-WCC7K1TZUVDD	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	False	In a Pool
9	Elements 25A3	2SGDZYXJ	WD Elements 25A3	Auto-Select	Healthy	False	Insufficient Capacity
10	WDC WD40EFRX-68N	WD-WCC7K6HJV122	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	False	In a Pool
11	WDC WD40EFRX-68N	WD-WCC7K1PHSSE7	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	False	In a Pool
12	WDC WD40EFRX-68N	WD-WCC7K5XL728J	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	False	In a Pool
13	WDC WD40EFRX-68N	WD-WCC7K3DNAYHT	ATA WDC WD40EFRX-68N	Auto-Select	Healthy	False	In a Pool

And then there's the SMART attributes of the same physical disks:

>Get-PhysicalDisk | Get-StorageReliabilityCounter | Select-Object Number, DeviceId, PowerOnHours, ReadErrorsTotal, Temperature, ReadLatencyMax, WriteLatencyMax | Format-Table

DeviceId	PowerOnHours	ReadErrorsTotal	Temperature	ReadLatencyMax	WriteLatencyMax
4	40340	0	28	155	283
6	25774	0	29	712	226
8	17383	0	29	190	163
12			36	489	287
13	1486	0	31	19376	0
5	25781	0	31	699	208
0	28385	0	27	1
1	35341	0	28	197	168
3	41524	0	27	177	193
11	13743	0	38	242	148
2	33553	0	28	365	422
10	14000	0	34	239	418
9	14000	0	36	234	368
7	25746	0	28	4

Well now i want these merged into single-rows:

DeviceId	FriendlyName	Model	SerialNumber	Usage	HealthStatus	CanPool	CannotPoolReason	PowerOnHours	ReadErrorsTotal	Temperature	ReadLatencyMax	WriteLatencyMax
0	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K6NE2J5J	Auto-Select	Healthy	TRUE		28385	0	27	1
1	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K1TZUVDD	Auto-Select	Healthy	FALSE	In a Pool	35341	0	28	197	168
2	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K6HJV122	Auto-Select	Healthy	FALSE	In a Pool	33553	0	28	365	422
3	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K5KP57ZJ	Hot Spare	Healthy	FALSE	In a Pool	41524	0	27	177	193
4	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K1URKJV5	Hot Spare	Healthy	FALSE	In a Pool	40340	0	28	155	283
5	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K3DNAYHT	Auto-Select	Healthy	FALSE	In a Pool	25781	0	31	699	208
6	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K1PHSSE7	Auto-Select	Healthy	FALSE	In a Pool	25774	0	29	712	226
7	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K5RYNVZD	Hot Spare	Healthy	FALSE	In a Pool	25746	0	28	4
8	ATA WDC WD40EFRX-68N	WDC WD40EFRX-68N	WD-WCC7K5XL728J	Auto-Select	Healthy	FALSE	In a Pool	17383	0	29	190	163
9	ATA WDC WD80EDAZ-11T	WDC WD80EDAZ-11T	VGGDU8GG	Auto-Select	Healthy	FALSE	In a Pool	14000	0	36	234	368
10	ATA WDC WD80EDAZ-11T	WDC WD80EDAZ-11T	VGJKAJEG	Auto-Select	Healthy	FALSE	In a Pool	14000	0	34	239	418
11	ATA WDC WD80EDAZ-11T	WDC WD80EDAZ-11T	VGKE7HJG	Auto-Select	Healthy	FALSE	In a Pool	13743	0	38	242	148
12	Samsung SSD 970 EVO 500GB	Samsung SSD 970 EVO 500GB	0025_385A_9150_212F.	Auto-Select	Healthy	TRUE				36	489	287
13	WD Elements 25A3	Elements 25A3	2SGDZYXJ	Auto-Select	Healthy	FALSE	Insufficient Capacity	1486	0	31	19376	0

In SQL Server relational databases that would be an INNER JOIN on DeviceId.

How can i combine these two results set into one set?

Answer 1

You can use Join-Object module to accomplish the task

Join data from two sets of objects based on a common value.

• Install Join-Object module

Install-Module -Name Join-Object

• Create the two lists

$FirstList = Get-PhysicalDisk | Select-Object DeviceID, FriendlyName, Model, SerialNumber, Usage, HealthStatus, CanPool, CannotPoolReason
$SecondList = Get-PhysicalDisk | Get-StorageReliabilityCounter | Select-Object Number, DeviceId, PowerOnHours, ReadErrorsTotal, Temperature, ReadLatencyMax, WriteLatencyMax

• Join the lists using Join-Object

$JoinedResult = Join-Object -left $FirstList -Right $SecondList -LeftJoinProperty DeviceID -RightJoinProperty DeviceID -KeepRightJoinProperty -Type OnlyIfInBoth -Prefix r_

$JoinedResult | Select DeviceID, FriendlyName, Model, SerialNumber, Usage, HealthStatus, CanPool, CannotPoolReason, r_PowerOnHours,r_ReadErrorsTotal, r_Temperature, r_ReadLatencyMax, r_WriteLatencyMax

From Connect data with PowerShell's Join-Object module, here is an explanation of Join-Object's parameters (replace where mentioned: $process with $FirstList, $services with $Secondlist, name with DevideID and service with r)

1. We have --Left and --Right. These are simply the objects we want to join, which are $processes and $services as defined above.
2. We use the --LeftJoinProperty and --RightJoinProperty commands. This is the property from these two objects we want to join together. In this case, they have the same name, which is "name."
3. The next parameter, --KeepRightJoinProperty includes the right joined property in the output of the join operation. I will demonstrate this further along in this tutorial.
4. The --Type parameter is important, because this is where you specify what type of join you would like to perform. I used OnlyIfInBoth to see any processes and services with the same name.

The other options taken directly from the PowerShell help are as follows:

• AllInLeft. This is the default parameter, which displays all Left elements in the output present at least once, depending on how many elements apply in Right.
• AllInRight. This is similar to AllInLeft.
• OnlyIfInBoth. Places all elements from Left in the output, regardless of how many matches are found in Right.
• AllInBoth. Includes all entries in both Right and Left in output.

5. The last parameter is --Prefix. This is used to prefix each Right property in the join with whatever string you decide. In this case, I used _service.

Answer 2

Why not just do the normal PowerShell way, no additional stuff is needed. Just normal pipeline stuff, with calculated properties. Well, one could have used a hash table or PSCustomObject as well.

Clear-Host
Get-PhysicalDisk | 
ForEach-Object {
    ($RecordData = $PSItem) | 
    Get-StorageReliabilityCounter | 
    Select-Object -Property @{
                                Name       = 'DeviceId'
                                Expression = {$RecordData.DeviceId}
                            }, 
                            @{
                                Name       = 'FriendlyName'
                                Expression = {$RecordData.FriendlyName}
                            }, 
                            @{
                                Name       = 'Model'
                                Expression = {$RecordData.Model}
                            }, 
                            @{
                                Name       = 'SerialNumber'
                                Expression = {$RecordData.SerialNumber}
                            }, 
                            @{
                                Name       = 'Usage'
                                Expression = {$RecordData.Usage}
                            }, 
                            @{
                                Name       = 'HealthStatus'
                                Expression = {$RecordData.HealthStatus}
                            }, 
                            @{
                                Name       = 'CanPool'
                                Expression = {$RecordData.CanPool}
                            }, 
                            @{
                                Name       = 'CannotPoolReason'
                                Expression = {$RecordData.CannotPoolReason}
                            },
                            Number, PowerOnHours, ReadErrorsTotal, 
                            Temperature, ReadLatencyMax, WriteLatencyMax
}
# Results
<#
...

DeviceId         : 1
FriendlyName     : Samsung SSD 950 PRO 512GB
Model            : Samsung SSD 950 PRO 512GB
SerialNumber     : 0000_0000_0000_0000
Usage            : Auto-Select
HealthStatus     : Healthy
CanPool          : False
CannotPoolReason : Insufficient Capacity
Number           : 
PowerOnHours     : 
ReadErrorsTotal  : 
Temperature      : 34
ReadLatencyMax   : 252
WriteLatencyMax  : 70
...
#>

Clear-Host
Get-PhysicalDisk | 
ForEach-Object {
    ($RecordData = $PSItem) | 
    Get-StorageReliabilityCounter | 
    Select-Object -Property @{
                                Name       = 'DeviceId'
                                Expression = {$RecordData.DeviceId}
                            }, 
                            @{
                                Name       = 'FriendlyName'
                                Expression = {$RecordData.FriendlyName}
                            }, 
                            @{
                                Name       = 'Model'
                                Expression = {$RecordData.Model}
                            }, 
                            @{
                                Name       = 'SerialNumber'
                                Expression = {$RecordData.SerialNumber}
                            }, 
                            @{
                                Name       = 'Usage'
                                Expression = {$RecordData.Usage}
                            }, 
                            @{
                                Name       = 'HealthStatus'
                                Expression = {$RecordData.HealthStatus}
                            }, 
                            @{
                                Name       = 'CanPool'
                                Expression = {$RecordData.CanPool}
                            }, 
                            @{
                                Name       = 'CannotPoolReason'
                                Expression = {$RecordData.CannotPoolReason}
                            },
                            Number, PowerOnHours, ReadErrorsTotal, 
                            Temperature, ReadLatencyMax, WriteLatencyMax
} | 
Format-Table -AutoSize
# Results
<#
DeviceId FriendlyName              Model                     SerialNumber         Usage       HealthStatus CanPool CannotPoolReason      Number PowerOnHours
-------- ------------              -----                     ------------         -----       ------------ ------- ----------------      ------ ------------
...
1        Samsung SSD 950 PRO 512GB Samsung SSD 950 PRO 512GB 0000_0000_0000_0000. Auto-Select Healthy        False Insufficient Capacity                    
...                    
#>

Answer 3

Easily joining two object lists in PowerShell is a decennia old requirement (see e.g.: https://devblogs.microsoft.com/powershell/join-object/). Personally, I think that this should be an intuitive idiomatic PowerShell syntax. Unfortunately there is (still) no native PowerShell command that can do this which resulted in a sprawl of custom made Join-Object implementations.
Anyways, this is my implementation of a Join-Object Module
see also: In Powershell, what's the best way to join two tables into one?

Install-Module -Name JoinModule

# $Disks = Get-PhysicalDisk | Select-Object DeviceId, FriendlyName, Model, SerialNumber, Usage, HealthStatus, CanPool, CannotPoolReason
$Disk = Read-HtmlTable https://superuser.com/q/1759239/1085094 -Table 0 # Install-Script -Name Read-HtmlTable

# Intialy, there was no DeviceID in the disk list where you might simply add it:
# $Disk = 0..13 |Join $Disk -Name DeviceId

# $Storage = Get-PhysicalDisk | Get-StorageReliabilityCounter | Select-Object Number, DeviceId, PowerOnHours, ReadErrorsTotal, Temperature, ReadLatencyMax, WriteLatencyMax
$Storage = Read-HtmlTable https://superuser.com/q/1759239/1085094 -Table 1

$Disk |Join $Storage -on DeviceId |Format-Table *

DeviceId Model                     SerialNumber         FriendlyName              Usage       HealthStatus CanPool CannotPoolReason      PowerOnHours ReadErrorsTotal Temperature ReadLatencyMax WriteLatencyMax
-------- -----                     ------------         ------------              -----       ------------ ------- ----------------      ------------ --------------- ----------- -------------- ---------------
       0 WDC WD80EDAZ-11T          VGJKAJEG             ATA WDC WD80EDAZ-11T      Auto-Select Healthy      False   In a Pool             28385        0               27          1
       1 WDC WD40EFRX-68N          WD-WCC7K6NE2J5J      ATA WDC WD40EFRX-68N      Auto-Select Healthy      True                          35341        0               28          197            168
       2 WDC WD40EFRX-68N          WD-WCC7K5RYNVZD      ATA WDC WD40EFRX-68N      Hot Spare   Healthy      False   In a Pool             33553        0               28          365            422
       3 WDC WD80EDAZ-11T          VGGDU8GG             ATA WDC WD80EDAZ-11T      Auto-Select Healthy      False   In a Pool             41524        0               27          177            193
       4 WDC WD80EDAZ-11T          VGKE7HJG             ATA WDC WD80EDAZ-11T      Auto-Select Healthy      False   In a Pool             40340        0               28          155            283
       5 Samsung SSD 970 EVO 500GB 0025_385A_9150_212F. Samsung SSD 970 EVO 500GB Auto-Select Healthy      True                          25781        0               31          699            208
       6 WDC WD40EFRX-68N          WD-WCC7K5KP57ZJ      ATA WDC WD40EFRX-68N      Hot Spare   Healthy      False   In a Pool             25774        0               29          712            226
       7 WDC WD40EFRX-68N          WD-WCC7K1URKJV5      ATA WDC WD40EFRX-68N      Hot Spare   Healthy      False   In a Pool             25746        0               28          4
       8 WDC WD40EFRX-68N          WD-WCC7K1TZUVDD      ATA WDC WD40EFRX-68N      Auto-Select Healthy      False   In a Pool             17383        0               29          190            163
       9 Elements 25A3             2SGDZYXJ             WD Elements 25A3          Auto-Select Healthy      False   Insufficient Capacity 14000        0               36          234            368
      10 WDC WD40EFRX-68N          WD-WCC7K6HJV122      ATA WDC WD40EFRX-68N      Auto-Select Healthy      False   In a Pool             14000        0               34          239            418
      11 WDC WD40EFRX-68N          WD-WCC7K1PHSSE7      ATA WDC WD40EFRX-68N      Auto-Select Healthy      False   In a Pool             13743        0               38          242            148
      12 WDC WD40EFRX-68N          WD-WCC7K5XL728J      ATA WDC WD40EFRX-68N      Auto-Select Healthy      False   In a Pool                                          36          489            287
      13 WDC WD40EFRX-68N          WD-WCC7K3DNAYHT      ATA WDC WD40EFRX-68N      Auto-Select Healthy      False   In a Pool             1486         0               31          19376          0

_{Please give a 👍 if you support the proposal to Add a Join-Object cmdlet to the standard PowerShell equipment (#14994)}