Checking SQL Services with PowerShell

From time to time every SQL DBA needs to know if their SQL Database servers are functioning properly. With PowerShell’s Get-Service command-let it is easy.

$servers ="server01, server02, server03" # Creates an array of SQL servers
$servicestatus = @{} # Creates a blank array
foreach ($server in $servers)
{
Get-Service -name *sql* -Computername $server -ErrorAction SilentlyContinue |
Sort-Object -Property DisplayName |
foreach{
$k = $server + " -" + $_.DisplayName
$v = $_.Status
$servicestatus[$k] = $v
Get-Service $_.Name |
Select-Object -ExpandProperty ServicesDependedOn |
foreach {
$kd = $k + " has dependency on " + $_.DisplayName
$s = $_.Status
$serviceStatus[$kd] = $s
}}}

#See all Results
$servicestatus | Format-Table -AutoSize

#See Running Status of each service
$servicestatus.GetEnumerator() |
Sort-Object Value |
Format-Table -AutoSize

#Show only stopped services
$servicestatus.GetEnumerator() |
where-object{$_.Value -eq "Stopped"} |
Format-Table -AutoSize

If you wanted you could incorporate the Send-SimpleMail function found here https://www.thecodeasylum.com/send-mail-with-powershell/

$body = $servicestatus.GetEnumerator() |
where-object{$_.Value -eq "Stopped" |
Format-Table -Autosize | Out-String
Send-SimpleMail -subject "Stopped Services" -body $body