There is a requirement from client that due to some ongoing issues on 10 servers client asked us to check sql services Status everyday if they are running or stopped as these servers are not yet added to Monitoring .
So, thought do this check using PowerShell that to retrieve the sql services status information. We can use Get- Service command to get required output but this works for individual server as I want to automate to retrieve information for all 10 servers in one go so, here we have used CIM commandlets to get our sql Services status report.
Also we need sql Services Start Time for all Servers. The output has the fields Hostname, Name, Caption, State, ProcessID, StartName(ServiceAccount) and StartTime.
Below is the PowerShell script to get the sql Services Running or Stopped State where you need to provide ComputerNames or ServerNames :
Invoke-Command -ComputerName DESKTOP-02JIB76 {
Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
@{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName
From above you can see it gives all the instances installed on the Machine with Running or Stopped State, along with sql Services Start Time
You can pass Multiple ComputerNames in Script by Separating with Coma(,) like
Invoke-Command -ComputerName DESKTOP-02JIB76,Node1,Node2 {
Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
@{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartNameIf you want to get information of only Running or only Stopped State Services
Invoke-Command -ComputerName DESKTOP-02JIB76 {
Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" -PipelineVariable Service |
Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
@{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartNameInvoke-Command -ComputerName DESKTOP-02JIB76 {
Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'Stopped'" -PipelineVariable Service |
Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
@{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName
