Combine arrays on common values

PowerShell Help
1

I have two arrays, 1 is the results of a query on Win32_OperatingSystem the other the results of a query on Microsoft.SqlServer.Management.Smo.Server for Sql Server Instances hosted by the Host.

So, my arrays contain various pieces of information, both including the Host server name. How can I combine both arrays into 1, matching on Host name ?

Array containing Windows Host information is:



$SystemInfo = @()


$snapshot |


ForEach-Object{


$_.SystemInfo|


ForEach-Object{


$SystemInfo+=[PSCustomObject]@{


ComputerName=$_.CSName


LastBootUpTime=$_.LastBootUpTime


OperatingSystem=$_.Caption


ServicePackMajorVersion=$_.ServicePackMajorVersion


ServicePackMinorVersion=$_.ServicePackMinorVersion


OSVersion=$_.Version


OSCodeSet=$_.CodeSet


OSLanguage=$_.OSLanguage


InstallDate=$_.InstallDate


CurrentTimeZone=$_.CurrentTimeZone


LocalDateTime=$_.LocalDateTime


}


}


} | ?{$_.ComputerName} | sort-object { $_.ComputerName } -Descending
Array containing Sql Server Instance information is: 


$SqlInstances = @()


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null


foreach ($SqlServer in $SqlServers | ? {$_})


{


$server=New-Object-TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "$SqlServer"


$SqlInstances+=$server|ForEach-Object{


[PSCustomObject]@{


"SqlServer"=($server.Name).Split('\')[0]


"Instance"=$(if($_.InstanceName){$_.InstanceName}else{"Default"})


"HostName"=$_.Information.ComputerNamePhysicalNetBIOS


"SQL Edition"=$_.Information.Edition


"Sql Version"=$_.Information.Version.Major|Get-SqlVersion


"Service Pack"=$_.Information.ProductLevel


"Sql Server Service Account"=$_.ServiceAccount


"Sql Agent Service Account"=$_.JobServer.ServiceAccount


}


}


}
 
2

Google how to join 2 objects on a common property.

3

yes, did that…been going through all I found in googleland over the last 2 days.

4

What’s the common property? See the comment from James Tye for making a hash table to join two objects. Join-Object - PowerShell Team

5

There are multiple ways to do things. The first question is what is the expected output? If there are one to many instances, should there be one row with each hostname with instances nested or multiple rows with the same host and one instance?

6

In $SystemInfo “ComputerName=$.CSName" equates to "HostName=$.Information.ComputerNamePhysicalNetBIOS” in $SqlInstances.

 

7

Rob, Because I’m posting the results to Confluence I’m wanting to keep it simple and have a 1 to 1 match. So if a Windows Host has 2 Sql Server Intances, it’ll result in two rows.

 

8

Well, first you’re going to need to make sure they have the same property name for that hostname field.

$SqlInstances = $SqlInstances | Select-Object -Property *, @{ Name = 'ComputerName'; Expression = 'HostName' }

Then you can just concatenate the arrays and use Group-Object to group them appropriately:

$SqlInstances + $SystemInfo | Group-Object -Property ComputerName
9

Here’s a really simple and inefficient example. EDITED

$a = [pscustomobject]@{name='joe';address='here'},
     [pscustomobject]@{name='john';address='there'}
$b = [pscustomobject]@{name='joe';phone='1'},
     [pscustomobject]@{name='john';phone='2'}

$a | foreach {
  $record = $_
  $other = $b | where name -eq $record.name

  [pscustomobject]@{name=$_.name
                 address=$_.address
                   phone=$other.phone}
}

name address phone
---- ------- -----
joe  here    1
john there   2
10

Joel, I appreciate the response but that doesn’t work, just created a lump that wasn’t correlated.

11

A basic example:

$obj1 = @()

$obj1 += [pscustomobject]@{
    hostname = 'Server1'
    someproperty = 'somevalue1'
}

$obj1 += [pscustomobject]@{
    hostname = 'Server2'
    someproperty = 'somevalue1'
}

$obj2 = @()

$obj2 += [pscustomobject]@{
    hostname = 'Server2'
    anotherproperty = 'anothervalue2'
}

$obj2 += [pscustomobject]@{
    hostname = 'Server1'
    anotherproperty = 'anothervalue1'
}
$obj2 += [pscustomobject]@{
    hostname = 'Server1'
    anotherproperty = 'anothervalue3'
}

$combined = foreach ($obj in $obj1) {
    #Lookup hostname in obj2 for current row in obj1
    $other = $obj2 | Where{$_.HostName -eq $obj.HostName}

    #Loop thru all object in other query
    foreach ($subObj in @($other)) {
        #Create a new object
        $obj | 
        Select hostname,
               someproperty, 
               @{Name='anotherProperty';Expression={$subObj.anotherproperty}}
    }              
}

$combined

Output:

hostname someproperty anotherProperty
-------- ------------ ---------------
Server1  somevalue1   anothervalue1  
Server1  somevalue1   anothervalue3  
Server2  somevalue1   anothervalue2
12

Thanks Rob,

Your solution produced the desired results. Much approciated.



$combined = foreach($obj in $SystemInfo ) {




$other=$SqlInstances|?{$_.Host-eq$obj.Host}


foreach($subobjin$other){


$obj| Select @{Name='SqlServer';Expression={$subObj.SqlServer}},


@{Name='Instance';Expression={$subObj.Instance}},


@{Name='SQL Edition';Expression={$subObj.'SQL Edition'}},


@{Name='Sql Version';Expression={$subObj.'Sql Version'}},


@{Name='Service Pack';Expression={$subObj.'Service Pack'}},


@{Name='Sql Server Service Account';Expression={$subObj.'Sql Server Service Account'}},


@{Name='Sql Agent Service Account';Expression={$subObj.'Sql Agent Service Account'}},


Host , LastBootUpTime , OperatingSystem , OSVersion, InstallDate


}


}


$combined
 
13

Here’s a way to join 2 objects using a hash. It should save time in large datasets.

$a = [pscustomobject]@{name='joe';address='here'},
     [pscustomobject]@{name='john';address='there'}
$b = [pscustomobject]@{name='john';phone='2'},
     [pscustomobject]@{name='joe';phone='1'}

$hashB=@{}
foreach ($record in $b) {
  $hashB[$record.name] = $record
}

$a | foreach {
  $record = $_
  $other = $hashB[$record.name]

  [pscustomobject]@{name=$_.name
                 address=$_.address
                   phone=$other.phone}
}

name address phone
---- ------- -----
joe  here    1
john there   2