Find servers with MS SQL Server installed with Powershell

Here is the script that will find servers in your domain with Microsoft SQL Server installed.

Script can handle unresponsive servers with pings via WMI calls, which is useful if ICMP blocked for some reason. Also - I'm using Powershell Remoting, so please be sure it is enabled on your servers.

$pcs = Get-ADComputer -filter *;

$sql_servers = @();

$pcs | %{
    $ping = $(Get-WmiObject -Class Win32_PingStatus -Filter "Address='$($_.name)' AND Timeout=1000").statuscode;
    if ( $ping -eq 0 )
    {
        $service = Invoke-Command -ComputerName $_.name -ScriptBlock { Get-Service -Name "MSSQLSERVER" -ErrorAction SilentlyContinue };
        $sql_servers += New-Object PSObject -Property @{
            name = $_.name;
            status = $service;
        };
    }
    else
    {
        $sql_servers += New-Object PSObject -Property @{
            name = $_.name;
            status = "NON-RESPONDING";
        };
    }
}

$sql_servers;

Here is the output: