Get SQL Edition and Version From Remote Servers

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 2 months, 1 week ago.

  • Author
    Posts
  • #68503
    Profile photo of Chris Cass
    Chris Cass
    Participant

    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...

  • #68724
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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?

You must be logged in to reply to this topic.