Author Posts

April 11, 2017 at 3:37 pm

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

April 14, 2017 at 1:26 pm

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?

November 12, 2017 at 1:53 am

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'