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?

Reddy Lutonadio · Answer 1 · 2022-12-23T17:43:59.270

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)

We have --Left and --Right. These are simply the objects we want to join, which are $processes and $services as defined above.

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."

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.

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.

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.

postanote · Accepted Answer · 2022-12-24T23:25:45.550

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

...

#>

iRon · Answer 3 · 2023-05-31T14:05:04.673

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

score 0 · Answer 4 · answered Jul 25 '24 at 12:49

I ended up using a version of @postanote's answer.

# Collect reliability data
$ReliabilityData = Get-PhysicalDisk | Get-StorageReliabilityCounter | Select-Object DeviceId, PowerOnHours, ReadErrorsTotal, WriteErrorsTotal, Temperature, TemperatureMax
Collect physical disk data
$DiskData = Get-PhysicalDisk | Select-Object DeviceId, Model, SerialNumber, Size, Usage, OperationalStatus
Combine the data
$JoinedData = @()
foreach ($disk in $ReliabilityData) {
    $matchingDisk = $DiskData | Where-Object {$_.DeviceId -eq $disk.DeviceId}
    $obj = New-Object -TypeName PSObject -Property @{
        DeviceId = $disk.DeviceId
        PowerOnHours = $disk.PowerOnHours
        ReadErrorsTotal = $disk.ReadErrorsTotal
        WriteErrorsTotal = $disk.WriteErrorsTotal
        Temperature = $disk.Temperature
        TemperatureMax = $disk.TemperatureMax
        Model = $matchingDisk.Model
        SerialNumber = $matchingDisk.SerialNumber
        Size = $matchingDisk.Size
        Usage = $matchingDisk.Usage
        OperationalStatus = $matchingDisk.OperationalStatus
    }
    $JoinedData += $obj
}
Sort and display the results in a table
$JoinedData | Select-Object DeviceId, Model, SerialNumber, Size, PowerOnHours, ReadErrorsTotal, Temperature, Usage, OperationalStatus | Sort-Object { [int]$_.DeviceId } | Format-Table -AutoSize

Returning:

DeviceId	Model	SerialNumber	Size	PowerOnHours	ReadErrorsTotal	Temperature	Usage	OperationalStatus
0	WDC WD40EFRX-68N	WD-WCC7K6NE2J5J	4000787030016	33819	0	25	Auto-Select	OK
1	WDC WD40EFRX-68N	WD-WCC7K1TZUVDD	4000762036224	49196	3	27	Auto-Select	OK
2	WDC WD40EFRX-68N	WD-WCC7K6HJV122	4000762036224	47408	0	27	Auto-Select	OK
3	WDC WD40EFRX-68N	WD-WCC7K5KP57ZJ	4000762036224	55379	0	26	Hot Spare	OK
4	WDC WD40EFRX-68N	WD-WCC7K1URKJV5	4000762036224	54196	0	26	Hot Spare	OK
5	WDC WD40EFRX-68N	WD-WCC7K3DNAYHT	4000762036224	39636	0	29	Auto-Select	OK
6	WDC WD40EFRX-68N	WD-WCC7K1PHSSE7	4000762036224	39629	0	27	Auto-Select	OK
7	WDC WD40EFRX-68N	WD-WCC7K5RYNVZD	4000762036224	39525	0	26	Hot Spare	OK
8	WDC WD40EFRX-68N	WD-WCC7K5XL728J	4000762036224	31238	0	27	Auto-Select	OK
9	WDC WD80EDAZ-11T	VGGDU8GG	8001524072448	27870	0	34	Auto-Select	OK
10	WDC WD80EDAZ-11T	VGJKAJEG	8001524072448	27870	0	32	Auto-Select	OK
11	WDC WD80EDAZ-11T	VGKE7HJG	8001524072448	27613	0	36	Auto-Select	OK
12	Samsung SSD 870 EVO 500GB	S62ANZ0R910911N	500107862016			0	Auto-Select	OK

How to perform "INNER JOIN" of powershell objects?

4 Answers4

Results

$Disks = Get-PhysicalDisk | Select-Object DeviceId, FriendlyName, Model, SerialNumber, Usage, HealthStatus, CanPool, CannotPoolReason

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

Collect physical disk data

Combine the data

Sort and display the results in a table