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;