Get SQL Edition and Version From Remote Servers

Welcome Forums General PowerShell Q&A Get SQL Edition and Version From Remote Servers

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
1 year, 1 month ago.

  • Author
    Posts
  • #68503

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    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

    Participant
    Points: 319
    Helping Hand
    Rank: Contributor

    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'

The topic ‘Get SQL Edition and Version From Remote Servers’ is closed to new replies.