Search code examples
excelpowershellforeachselect-object

Display Data on Two Columns Within Excel


I am trying to display data within an Excel document where Column A displays the server name and column B displays the .NET version. I'm running into an issue exporting to a .csv because it says that the file path does not exist. I would like some guidance on how I can resolve that issue and how I can display data on the two columns within Excel.

$Servers =
(
"test"
)

foreach ($Server in $Servers)
{

Invoke-Command -ComputerName $Server -ScriptBlock {
Write-Output "$(hostname)"
Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse | Get-ItemProperty -Name Version,Release -EA 0 | where { $_.PSChildName -match '^(?!S)\p{L}'} | select PSChildName, Version, Release | Select -ExpandProperty Version | Sort-Object Version | Export-Csv -Path C:\Users\User\Desktop\example.csv

}

Solution

  • The main issue is that you're using Export-Csv on the remote hosts since it is inside the Invoke-Command script block, and the likeable error is because the path you are using as export doesn't exist on those hosts.

    It's also worth noting that Invoke-Command can run in parallel, -ComputerName as well as -Session can take an array, this removes the need for the foreach loop as well as it is much faster / efficient.

    Invoke-Command -ComputerName $servers -ScriptBlock {
        Write-Host "Working on $($env:COMPUTERNAME)..."
        Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse |
            Get-ItemProperty -Name Version, Release -EA 0 |
            ForEach-Object {
                if($_.PSChildName -notmatch '^(?!S)\p{L}') {
                    return # skip this
                }
            
                [pscustomobject]@{
                    HostName = $env:COMPUTERNAME
                    Version  = $_.Version
                }
            } | Sort-Object Version
    } -HideComputerName | Select-Object * -ExcludeProperty RunspaceID |
        Export-Csv -Path C:\Users\User\Desktop\example.csv -NoTypeInformation