How to Get-ItemProperty for registry items on remote servers

Question

I am attempting to pull SQL server instance details for a multitude of SQL servers, but am not able to get it working. Seems to come down to how parameter 'Path' gets it's value from my $path variable.:

#Set server list manually with array

$serverList = @("Server1","Server2","Server3") #actual server names redacted for security purposes

#create results array

$resultsTable = @()

#iterate through servers in server list

foreach ($server in $serverList){

#get SQL Server instance

$inst = Invoke-Command -ComputerName $server {(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').MSSQLSERVER}

$inst

foreach ($p in $inst) {

    #create path for instance

    $path = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\',$p,'\Setup' -join ""

    $path

    #get SQL Server patch level

    $patch = Invoke-Command -ComputerName $server {(Get-ItemProperty $path).PatchLevel}

    #get SQL Server Edition for instance

    $edition = Invoke-Command -ComputerName $server {(Get-ItemProperty $path).Edition}

    #get SQL Server Version for instance

    $version = Invoke-Command -ComputerName $server {(Get-ItemProperty $path).Version}

    #create joined table from results

    ForEach-Object {

        $object = 

            [PSCustomObject]@{

                "Server:" = $server;

                "SQL Server Instance:" = $inst;

                "SQL Server Edition:" = $edition;

                "SQL Server Version:" = $version;

            }

        #add object to results table

        $resultsTable += $object

    }

#output results to console

$object

}

}

This results in the following errors:

MSSQL13.MSSQLSERVER (<-- the output of $inst)

HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup (<--Output of $path, which looks correct)

Cannot bind argument to parameter 'Path' because it is null.

+ CategoryInfo          : InvalidData: (:) [Get-ItemProperty], ParameterBindingValidationException

+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.GetItemPropertyCommand

+ PSComputerName        : Server1

Cannot bind argument to parameter 'Path' because it is null.

+ CategoryInfo          : InvalidData: (:) [Get-ItemProperty], ParameterBindingValidationException

+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.GetItemPropertyCommand

+ PSComputerName        : Server1

Cannot bind argument to parameter 'Path' because it is null.

+ CategoryInfo          : InvalidData: (:) [Get-ItemProperty], ParameterBindingValidationException

+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.GetItemPropertyCommand

+ PSComputerName        : Server1

Obviously $path is not null as I define it right before "Invoke-Command". I have also attempted writing out the path within the "Get-ItemProperty" blocks, using the $p variable to input the value in-line. That results in the same error. If I manually enter "MSSQL13-MSSQLSERVER" in the path, it works fine, but does not allow for dynamic capture of any other installed instances found. The goal is to get all currently installed instances for a given remote server, along with their Edition, Version, and any other pertinent data for that instance.

Any idea why I'm getting this error? It appears to be an issue of "Invoke-Command" since I have tested locally on one of the SQL servers in Powershell and it appears to get the values fine.

Answer 1

You're trying to pass a local variable to a script block that will run on a remote machine. That local variable will be resolved when the script block runs -- but at that time the variable won't be present on the remote machine.

I don't have a SQL server to test with, but give this a try:

#Set server list manually with array
$serverList = @("Server1", "Server2", "Server3") #actual server names redacted for security purposes
#create results array
$resultsTable = @()
#iterate through servers in server list
foreach ($server in $serverList) {
    #get SQL Server instances
    $inst = Invoke-Command -ComputerName $server { (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').MSSQLSERVER }
    foreach ($p in $inst) {
        #create path for instance
        $path = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\', $p, '\Setup' -join ""
        $resultsTable += Invoke-Command -ComputerName $server {
                            [PSCustomObject]@{
                                'Server:'                 = $Using:server
                                'SQL Server Instance:'    = $Using:p
                                'SQL Server PatchLevel:'  = (Get-ItemProperty $Using:path).PatchLevel
                                'SQL Server Edition:'     = (Get-ItemProperty $Using:path).Edition
                                'SQL Server Version:'     = (Get-ItemProperty $Using:path).Version
                            }
                         }
    }
}
#output results to console
$resultsTable