How to export two variables into same CSV as joined via PowerShell?

Question

I have a PowerShell script employing poshwsus module like below:

$FileOutput = "C:\WSUSReport\WSUSReport.csv"
$ProcessLog = "C:\WSUSReport\QueryLog2.txt"
$WSUSServers = "C:\WSUSReport\Computers.txt"
$WSUSPort = "8530"

import-module poshwsus 

ForEach ($Server in Get-Content $WSUSServers)
{
    & connect-poshwsusserver $Server -port $WSUSPort | out-file $ProcessLog -append
    $r1 = & Get-PoshWSUSClient | select @{name="Computer";expression={$_.FullDomainName}},@{name="LastUpdated";expression={if ([datetime]$_.LastReportedStatusTime -gt [datetime]"1/1/0001 12:00:00 AM") {$_.LastReportedStatusTime} else {$_.LastSyncTime}}}
    $r2 = & Get-PoshWSUSUpdateSummaryPerClient -UpdateScope (new-poshwsusupdatescope) -ComputerScope (new-poshwsuscomputerscope) | Select Computer,NeededCount,DownloadedCount,NotApplicableCount,NotInstalledCount,InstalledCount,FailedCount

}

What I need to do is to export CSV outpout including the results with the columns (like "inner join"):

Computer, NeededCount, DownloadedCount, NotApplicableCount, NotINstalledCount, InstalledCount, FailedCount, LastUpdated

I have tried to use the line below in foreach, but it didn't work as I expected.

$r1 + $r2 | export-csv -NoTypeInformation -append $FileOutput

I appreciate if you may help or advise.

EDIT --> The output I've got:

ComputerName LastUpdate
X                A
Y                B
X
Y

So no error, first two rows from $r2, last two rows from $r1, it is not joining the tables as I expected.

Thanks!

Are you getting an error message or an unexpected output? are you able to share that output (removing sensitive information if needed)? — Matthew, Oct 09 '19 at 20:34
If you do not want to reinvent the wheel and run in every pitfall related to joining objects in PowerShell (like: [Not all properties displayed](https://stackoverflow.com/q/44428189/1701026)) and (performance) issues, you might want to look in this [`Join-Object`](https://stackoverflow.com/a/45483110/1701026) cmdlet. I guess the syntax in your case is as simple as: `$r1 | Join $r2 -On Computer` — iRon, Oct 10 '19 at 18:15

score 0 · Accepted Answer · answered Oct 09 '19 at 20:46

I've found my guidance in this post: Inner Join in PowerShell (without SQL)

Modified my query accordingly like below, works like a charm.

$FileOutput = "C:\WSUSReport\WSUSReport.csv"
$ProcessLog = "C:\WSUSReport\QueryLog.txt"
$WSUSServers = "C:\WSUSReport\Computers.txt"
$WSUSPort = "8530"

import-module poshwsus 

function Join-Records($tab1, $tab2){
    $prop1 = $tab1 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t1
    $prop2 = $tab2 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t2
    $join = $prop1 | ? {$prop2 -Contains $_}
    $unique1 = $prop1 | ?{ $join -notcontains $_}
    $unique2 = $prop2 | ?{ $join -notcontains $_}


    if ($join) {
        $tab1 | % {
            $t1 = $_
            $tab2 | % {
                $t2 = $_
                foreach ($prop in $join) {
                    if (!$t1.$prop.Equals($t2.$prop)) { return; }
                }
                $result = @{}                
                $join | % { $result.Add($_,$t1.$_) }
                $unique1 | % { $result.Add($_,$t1.$_) }
                $unique2 | % { $result.Add($_,$t2.$_) }
                [PSCustomObject]$result
            }
        }
    }
}

ForEach ($Server in Get-Content $WSUSServers)
{
    & connect-poshwsusserver $Server -port $WSUSPort | out-file $ProcessLog -append
    $r1 = & Get-PoshWSUSClient | select @{name="Computer";expression={$_.FullDomainName}},@{name="LastUpdated";expression={if ([datetime]$_.LastReportedStatusTime -gt [datetime]"1/1/0001 12:00:00 AM") {$_.LastReportedStatusTime} else {$_.LastSyncTime}}}
    $r2 = & Get-PoshWSUSUpdateSummaryPerClient -UpdateScope (new-poshwsusupdatescope) -ComputerScope (new-poshwsuscomputerscope) | Select Computer,NeededCount,DownloadedCount,NotApplicableCount,NotInstalledCount,InstalledCount,FailedCount
    Join-Records $r1 $r2 | Select Computer,NeededCount,DownloadedCount,NotApplicableCount,NotInstalledCount,InstalledCount,FailedCount, LastUpdated | export-csv -NoTypeInformation -append $FileOutput

}

score 0 · Answer 2 · answered Oct 10 '19 at 12:11

I think this could be made simpler. Since Select-Object's -Property parameter accepts an array of values, you can create an array of the properties you want to display. The array can be constructed by comparing your two objects' properties and outputting a unique list of those properties.

$selectProperties = $r1.psobject.properties.name | Compare-Object $r2.psobject.properties.name -IncludeEqual -PassThru
$r1,$r2 | Select-Object -Property $selectProperties

Compare-Object by default will output only differences between a reference object and a difference object. Adding the -IncludeEqual switch displays different and equal comparisons. Adding the -PassThru parameter outputs the actual objects that are compared rather than the default PSCustomObject output.

How to export two variables into same CSV as joined via PowerShell?

2 Answers2