Hello friends,

This is second post of “DBA with PowerShell” series. In this series we try to fetch some useful information of SQL Instance environment

Last post “DBA with PowerShell : Get perfmon counters using PowerShell” was about fetching critical information like CPU, Memory, IO utilization and insert it  into SQL Database so we can generate meaningful reports. In this post, we will see how to fetch list of services and its status .It is very helpful in case when we need to monitor any specific service status across multiple servers.

You need to pass the machine name or just simply put “.” for local machine’s services status.

DECLARE @MachineName VARCHAR(128) = '.'  --# Put Machine Name e.g. Mac01 OR put "." for local machine
, @QueryStr VARCHAR(4000)

DECLARE @Service AS TABLE (QutPut VARCHAR(MAX))

-- Build Query to get service info
SET @QueryStr = 'PowerShell.exe -c "$a = 1; Get-WmiObject -ComputerName '+@MachineName+'  Win32_Service | Where-Object {$_.Name -like ''*''} | SELECT SystemName, (@{Name=''Service Name''; Exp=''Name''}), (@{Name=''LogOn As''; Exp=''StartName''}), State| ForEach-Object {New-Object psObject -Property @{''SrNo''=$a;''SystemName''= [String]$a + ''^''+ $_.SystemName; ''Service Name'' = [String]$a + ''^''+$_.''Service Name''; ''LogOn As'' = [String]$a + ''^''+ $_.''LogOn As''; ''State'' = [String]$a + ''^''+ $_.State};$a ++}| Sort-Object SrNo |Format-List -Property  SystemName, ''Service Name'', ''LogOn As'', State "'

-- Insert data into a table variable
INSERT INTO @Service
EXEC xp_cmdshell @QueryStr

-- Deleteing unwanted data
DELETE @Service WHERE QutPut IS NULL

-- Converting into presentable format
SELECT [Tag],[SystemName], [Service Name] ,[LogOn As],[State]
FROM (
SELECT LTRIM(RTRIM(SUBSTRING(QutPut,1,CHARINDEX(':',QutPut)-1))) Head
, LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX('^',QutPut)+1,len(QutPut)))) Val
, SUBSTRING(LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX(':',QutPut)+1,len(QutPut)))),1,
CHARINDEX('^',LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX(':',QutPut)+1,len(QutPut)))))-1) Tag
FROM @Service
)Pvt
PIVOT
(
MAX(Val) FOR Head IN ([SystemName], [Service Name] ,[LogOn As],[State])
) Dt

Cheers!