Get SQL Edition and Version From Remote Servers

Hello! Pretty new to PowerShell and trying to figure out how to work with remote registry entries. My goal is to get the Edition and Version values from:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceValue\Setup

I know I can get the Instance Value from:

HKLM\Software\Microsoft SQL Server\Instance Names\SQL

The name of the instance I’m searching for is called WEBACCESS.

I’ve been able to generate the following to give me the SQL Instance Values:

computers = gc "C:\Merge\Powershell\Servers.txt"
clear-host

foreach ($Computer in $Computers)
{
$computerSystem = get-wmiobject Win32_ComputerSystem -Computer $Computer

$ComputerSQLReg = 

    $machinename = $computer
    $key = "Software\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL"
    $valuename = "WEBACCESS"

    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $machinename)
    $regkey = $reg.opensubkey($key)
    $p = $regkey.getvalue($valuename)

        write-host "System Information for: " $computerSystem.Name -BackgroundColor DarkCyan
        "-------------------------------------------------------"
        "SQL Server Instance: " + $p
        
        ""
        "-------------------------------------------------------"
}

Now I’m having trouble taking the $p value and using that in the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceValue\Setup registry key to get the edition and version.

I found this code to do this on a local server:

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $inst)
{
   $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition
   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version
}

I tried to use the knowledge I gained in getting the first part of this and combine what I’m seeing in this second script to see if I can get the edition and version. I’ve built the following script based on what I’m seeing, but now all 3 values are coming up blank. Any chance anyone else can find what I’m doing wrong here?

computers = gc "C:\Merge\Powershell\Servers.txt"
clear-host

foreach ($Computer in $Computers)
{
$computerSystem = get-wmiobject Win32_ComputerSystem -Computer $Computer

$ComputerSQLReg = 

    $machinename = $computer
    $key = "Software\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL"
    $valuename = "WEBACCESS"

    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $machinename)
    $regkey = $reg.opensubkey($key)

    foreach ($valuename in $p)
    {
        $p = $regkey.getvalue($valuename)
	    $KeyEd = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\$p\\Setup"
	    $valueEd = "Edition"
	    $valueVer = "Version"
	    $regkeysetup = $reg.opensubkey($keyed)
	    $e = $regkeysetup.getvalue($ValueEd)
	    $v = $regkeysetup.getvalue($valueVer)
    }

        write-host "System Information for: " $computerSystem.Name -BackgroundColor DarkCyan
        "-------------------------------------------------------"
        "SQL Server Instance: " + $p
        "SQL Express or Standard: " + $e
        "SQL Version Number: " + $v
        
        ""
        "-------------------------------------------------------"
}

My goal here is to add this to a script I’ve already got working that shows things like hostname, IP, CPU, OS, cores, RAM, etc, but cracking the remote registry code is kicking my butt…

There’s only two reliable ways to do this - WMI, which is what you’re doing, or Remoting. Remoting is easier - you take the same thing that works locally and send it via Invoke-Command. Is that an option?

Have you review the following, vs going it from scratch. (Well except for learning reasons)

Using PowerShell to discover information about your Microsoft SQL Servers
powershellmagazine.com/2014/07/21/using-powershell-to-discover-information-about-your-microsoft-sql-servers

Get-SQLInstance
This function looks up SQL Instance information via the registry on local and
remote systems. Information looked up is Version, Edition type, whether the SQL
Instance is part of a cluster and the other nodes in the cluster and the full
name that can be used in another script to
gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

Get SQL Server Version Function
gallery.technet.microsoft.com/scriptcenter/5f763a6e-23bf-46be-a837-13e5005acb72

Inventory SQL Server Services Version and Edition
www.mssqltips.com/sqlservertip/3549/inventory-sql-server-services-version-and-edition

Inventory SQL Server Versions on Multiple Computers
itprotoday.com/management-mobility/inventory-sql-server-versions-multiple-computers

Check for SQL Server(s) Version with Get-MSSQLVersion function
maxtblog.com/2011/04/check-for-sql-servers-version-with-get-mssqlversion-function