With this simple script, you will be able to get the server names on which the MS SQL Server service is installed.
First, an array is built with the computer names and the following properties :
only the “Server” operating systems
only physical servers (virtual cluster nodes are excluded)
only enabled computer accounts
The cmdlet Get-service is used to query all the servers
$list = Get-ADComputer -ldapfilter “(&(objectCategory=computer)(operatingSystem=*server*)(!(servicePrincipalName=MSClusterVirtualServer*))(!(userAccountControl:1.2.840.113556.1.4.803:=8192)))”
$list | % {
$hostname = $_.Name
try {
if (Get-Service -ComputerName $hostname | ? { ($_.name -cmatch ‘MSSQL’) -and ($_.DisplayName -cmatch ‘SQL Server’)}){
Write-Host $hostname “: SQL Server is running”
}
}
catch {
Write-Host “Unable to request the server “$hostname
}
}