SQL Database Enumeration Script Update Part I

So, this SQL Instance Database enumeration script has been taking up all my free time.  This process is like running down a hallway with low hanging pipes.  I think I have the issues resolved and, PING, I hit one of the pipes.  Here is what I have to pull severs and instances:

foreach($Name in $names) {

$result = Test-Connection -ComputerName $Name.name -Quiet

if ($result) {

$objReg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Name.name)

$objRegKey= $objReg.OpenSubKey("SOFTWARE\\MICROSOFT\\MICROSOFT SQL SERVER" )

if ($objRegKey.GetValue("InstalledInstances")) {

$instances = $objRegKey.GetValue("InstalledInstances")

if ($instances.Count -gt 0) {

foreach($i in $instances) {

if ($i -match 'MSSQLSERVER') {

$output = $Name.name

} Else {

$output = $Name.name + '\' + $i




}
} #$output | out-file -Append server_instance.csv

#$output | out-file -append server_instance.csv

} else { $output = $Name.name } $output | out-file -Append server_instance.csv


}

}

}
This line $result = Test-Connection -ComputerName $Name.name -Quiet checks if the server is online.  This verification shortens the number of servers in the output file.  If $result is true, the next level is initiated.

This line  $objReg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Name.name)     adds registry information for the remote server to the $objReg variable.

This line  $objRegKey= $objReg.OpenSubKey("SOFTWARE\\MICROSOFT\\MICROSOFT SQL SERVER" )   adds the registry key for SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER in the $objRegKey variable

This line if ($objRegKey.GetValue("InstalledInstances")) checks if the INSTALLEDINSTANCES key is present.  If it is present then the  $instances = $objRegKey.GetValue("InstalledInstances")  code loads the instances into the $instances variable.   

The next line if ($instances.Count -gt 0) checks if there are instances for the server.  I found that there will be no instances listed if the server is using the default MSSQLSERVER instance.  In this case the database script (which I will post later) will only need the server name and not server name\instance (i.e., sqlprd\mssqlserver). 

If there are instances in $instances, then the instance is checked for MSSQLSERVER.  If true, then only the server name is put in the $output variable.  Else, the server name and instance are put in the $output variable in the servername\instance format.

At the end the contents of the $output variable is written to the csv file.

This script works...for the most part.  I have found that one of my SQL server doesn't have all the instances listed.  I'm trying to figure this out today.

So, feel free to use this script.  NOTE: it is not perfect but is significantly better than what I had a week ago.