Get SQL Edition and Version From Remote Servers

This topic contains 2 replies, has 3 voices, and was last updated by  postanote 1 week, 4 days ago.

  • Author
    Posts
  • #68503

    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

    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?

  • #84002

    postanote
    Participant

    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'

You must be logged in to reply to this topic.