Search This Blog

Monday, October 8, 2018

Powershell One Liners: Get status of SQL Instances on All SQL Servers

Powershell One Liners: Get status of SQL Instances on All SQL Servers
To be more precise, I am using the WMI to check the status of the SQL Server services i.e. the windows service for the database instances. I am not though testing whether a successful connection could be made to the SQL instances.

If you have a single server that you want to check status of the SQL Server database services:

$ComputerName = "Server1"
Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerName | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status







# If you have list of servers in servers.txt, each sever name in its own line

$ComputerNames = get-content servers.txt
$ComputerNames = $ComputerNames | sort -Unique | Where-Object {$_ -ne ""}

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerNames | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status








# To export the output into an excel/csv, just add "export-csv <filename.csv>" at the end..

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerNames | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status | export-csv SQLInstancesStatus.csv



PS: Or, to make this a true one-liner, embed the get-content cmdlet inside rounded brackets


Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName (get-content servers.txt) | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status