PowerShell – List all my SQL instances on a list of computers, non-Registry method

Wow, I found so many nice posts on how to do this using  registry queries, but seeing as I have a list of over 100 servers on geographic disparate areas, the scripts were running for ages. So I though why not just use Get-WmiObject. So here is the juicy bits

$inputfile = "$scriptlocation$serverlistfile";
$servers = get-content $inputfile;

#Create container
$SQLservers = @() ;

foreach($server in $servers)
{
 if($server.length -gt 4)
 {
 $Computer = $server -replace '(.*?)\..+','$1';
 Write-host ("Checking {0}" -f $Computer);
 $sqlservices = (Get-Service -computer $server| Where-Object {$_.Name -like 'MSSQL$*'});
 if($sqlservices.count -gt 0)
 {
 $sqlservices = Get-WmiObject -class Win32_Service -computerName $server -filter "name Like '%mssql$%' or name Like 'MSSQLSERVER'" | Select *
 foreach($sqlservice in $sqlservices)
 {
 $Servername = $sqlservice.PSComputerName;
 $Instance =$sqlservice.Name.Replace('MSSQL$', '');
 #$Version = $sqlservice.PathName.Replace('"C:\Program Files\Microsoft SQL Server\MSSQL','');
 $test = $sqlservice.PathName;
 $regme = [regex]::match("$test","MSSQL");
 $splitme = $test.length - $regme.Index;
 $secondbit = $test.Substring($regme.Index,$splitme);
 $regmeagain = [regex]::match("$secondbit","\.");
 $splitmeagain = $regmeagain.Index * 1;
 $Version = $test.Substring($regme.Index,$splitmeagain).Replace("MSSQL","").Replace("_",".");
 $RegPATH = $sqlservice.__PATH;
 #$DisplayName = $sqlservice.DisplayName;

$obj = New-Object System.Object;
 $obj | Add-Member -MemberType NoteProperty -Name Servername -Value $Servername;
 $obj | Add-Member -MemberType NoteProperty -Name Instance -Value $Instance;
 $obj | Add-Member -MemberType NoteProperty -Name Version -Value $Version;
 $obj | Add-Member -MemberType NoteProperty -Name RegPATH -Value $RegPATH;
 $SQLservers += $obj;
 }
 }
}
$SQLservers;

#powershell #sqlinstances