PowerShell list combinator - optimize please

Question

so I have this idea of a script which would combine multiple data exports from different sources when each of them contains only part of the information. The result should be longer CSV with first column values taken from list1 and second column would be values taken from the other CSVs. An example source CSVs:

list1.csv
server
hostname1
hostname2
hostname3
hostname4
hostname5
hostname6
hostname7

ADscan.csv
server;OS
hostname2;Microsoft Windows Server 2012 R2 Datacenter
hostname3;Microsoft Windows Server 2008 R2 Standard 

export2.csv
server;OS
hostname1;w2k12
hostname2;w2k12
hostname3;w2k8
hostname4;w2k8
hostname5;w2k16

export3.csv
server;OS
hostname2.suffix;windows server 2012
hostname3.suffix;windows server 2008
hostname6.suffix;windows server 2008

The script should simply take each of the values from list1, look into other lists and assign .os property in case it is found. If not, look in the next list.

$list1=Import-Csv "list1.csv" -delimiter ";"
write-host "importing"($list1.count)"lines"
$list2=Import-Csv "ADscan.csv" -delimiter ";"
write-host "importing"($list2.count)"lines"
$list3=Import-Csv "export3.csv" -delimiter ";"
write-host "importing"($list3.count)"lines"
$list4=Import-Csv "export2.csv" -delimiter ";"
write-host "importing"($list4.count)"lines"
$list4.count

$result=@()

foreach($srv in $list1.server) {
    $srv
    $obj= [PSCustomObject]@{
        server=$srv
        OS=""
    }  
    $obj.os=($list2|where {$_.server -eq $srv}|select os -first 1).os
    if($obj.os -ne ""){$result+=$obj}
    if(!$obj.os){                              #this list contains .suffixes
        $obj.os=($list3|where {$_.server -like $srv+".*"}|select os -first 1).os
        if($obj.os){$result+=$obj}
    }
    if(!$obj.os){
        $obj.os=($list4|where {$_.server -eq $srv}|select os -first 1).os
        if($obj.os){$result+=$obj}
    }
}
"RESULT"
$result|export-csv "result.csv" -NoTypeInformation -delimiter ";"

the resulting table should contain all values combined while line with hostname7 will have empty OS value as it's not in any list. It works, but is quite slow and not really usable when you have lists with thousands of lines. Any idea how to make this better and faster?

The better place to post an optimization question is [code review](https://codereview.stackexchange.com) — , Sep 05 '19 at 20:39
Possible duplicate of [Powershell Merge 2 lists Performance](https://stackoverflow.com/questions/46091947/powershell-merge-2-lists-performance) — iRon, Sep 05 '19 at 20:43

iRon · Answer 1 · 2019-11-24T12:09:14.150

Apart from the recommendations in duplicate Powershell Merge 2 lists Performance, you should focus to on the contents of the inner loops with will run something like list.count * export3.count times.
In other words: it is better to prepare the export3 list outside the Where-Object clause by removing the .suffix so you can avoid the expensive -like operator: $_.server -eg $srv + '.*' rather than: $_.server -like $srv + ".*"

_{(btw: using single quotes is faster then double quotes)}

Using the Join-Object cmdlet from the PowerShell Gallery (which uses the pipeline for the left object and a hash table for the right object), if you want to merge the OScolumns to a single column (the last right OSvalue will rule), the syntax will be as follows:

Import-Csv .\List1.csv -Delimiter ';' |
Merge (Import-Csv .\ADscan.csv -Delimiter ';') -On Server |
Merge (Import-Csv .\export2.csv -Delimiter ';') -On Server |
Merge (Import-Csv .\export3.csv -Delimiter ';' |
    Select-Object @{n='Server';e={$_.Server.Split('.', 2)[0]}}, OS) -On Server

Result:

Server    OS
------    --
hostname1 w2k12
hostname2 windows server 2012
hostname3 windows server 2008
hostname4 w2k8
hostname5 w2k16
hostname6 windows server 2008
hostname7

If you want to like to unify the the OS Columns you might use the LeftJoin -Unify ...command:

Import-Csv .\List1.csv -Delimiter ';' |
LeftJoin (Import-Csv .\ADscan.csv -Delimiter ';') -On Server |
LeftJoin (Import-Csv .\export2.csv -Delimiter ';') -On Server -Unify '',Export2 |
LeftJoin (Import-Csv .\export3.csv -Delimiter ';' |
    Select-Object @{n='Server';e={$_.Server.Split('.', 2)[0]}}, OS) -On Server -Unify '',Export3

Result:

Server    OS                                          Export3OS           Export2OS
------    --                                          ---------           ---------
hostname1                                                                 w2k12
hostname2 Microsoft Windows Server 2012 R2 Datacenter windows server 2012 w2k12
hostname3 Microsoft Windows Server 2008 R2 Standard   windows server 2008 w2k8
hostname4                                                                 w2k8
hostname5                                                                 w2k16
hostname6                                             windows server 2008
hostname7

update 2019-11-24
The -Unify parameter has been deprecated and mainly replaced by the -Discern parameter (some specific unify features however, are accommodated by the -Property parameter).

LeftJoin is unrecognized in PS5. Which PowerShell version has it? — McVitas, Sep 22 '19 at 16:53
@McVitas, you need to download and [dot-source](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_scripts?view=powershell-6#script-scope-and-dot-sourcing) the [Join-Object](https://stackoverflow.com/a/45483110/1701026) cmdlet from the [PowerShell Gallery](https://www.powershellgallery.com/packages/Join/). — iRon, Sep 22 '19 at 17:01

js2010 · Answer 2 · 2019-09-09T15:34:24.843

The usual approach in merging objects is to turn the other list(s) into a hash first.

$list2 | foreach { $hash2 = @{} } { $hash2[$_.server] = $_ }
$list3 | foreach { $hash3 = @{} } { $hash3[$_.server] = $_ }
$list4 | foreach { $hash4 = @{} } { $hash4[$_.server -replace '\..*'] = $_ } # delete server past the dot

foreach($server in $list1.server) {
  [void](
    ($os = $hash2[$server].os) -or  # pick the one that's not empty
    ($os = $hash3[$server].os) -or
    ($os = $hash4[$server].os))
  [pscustomobject]@{
    Server = $server
    Os = $os
  }
}

Output:

Server    Os
------    --
hostname1 w2k12
hostname2 Microsoft Windows Server 2012 R2 Datacenter
hostname3 Microsoft Windows Server 2008 R2 Standard 
hostname4 w2k8
hostname5 w2k16
hostname6 windows server 2008
hostname7

PowerShell list combinator - optimize please

2 Answers2