Combine arrays on common values

Welcome Forums General PowerShell Q&A Combine arrays on common values

This topic contains 12 replies, has 4 voices, and was last updated by

js
 
Participant
1 week, 3 days ago.

  • Author
    Posts
  • #127822

    Participant
    Points: 123
    Rank: Participant

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

     

  • #127831
    js

    Participant
    Points: 318
    Helping Hand
    Rank: Contributor

    Google how to join 2 objects on a common property.

  • #127834

    Participant
    Points: 123
    Rank: Participant

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

  • #127837
    js

    Participant
    Points: 318
    Helping Hand
    Rank: Contributor

    What's the common property? See the comment from James Tye for making a hash table to join two objects. https://blogs.msdn.microsoft.com/powershell/2012/07/13/join-object/

  • #127840

    Participant
    Points: 316
    Helping Hand
    Rank: Contributor

    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?

  • #127843

    Participant
    Points: 123
    Rank: Participant

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

     

  • #127846

    Participant
    Points: 123
    Rank: Participant

    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.

     

  • #127848

    Participant
    Points: 290
    Helping Hand
    Rank: Contributor

    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
  • #127849
    js

    Participant
    Points: 318
    Helping Hand
    Rank: Contributor

    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
    
  • #127852

    Participant
    Points: 123
    Rank: Participant

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

  • #127855

    Participant
    Points: 316
    Helping Hand
    Rank: Contributor

    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  
    
  • #127881

    Participant
    Points: 123
    Rank: Participant

    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

     

  • #127893
    js

    Participant
    Points: 318
    Helping Hand
    Rank: Contributor

    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
    

You must be logged in to reply to this topic.