One of my friend requested me to give a mutli server script to pull SQL Service related details. Here is a quick PowerShell script to find Name, ServerName, Status, StartupMode, Service Account and other details of SQL Services across all listed SQL Instances. The output is shown in three different formats.

• AutoSize
• HTML
• CSV

Copy and Paste the below code PowerShell-ISE and execute 

*******************************************************************************

#A PowerShell array holds a list of data items

$Result = @()

#Loop through all SQL Instances listed under F:\PowerSQL\List.txt

foreach($server in Get-Content F:\PowerSQL\List.txt)

{

#List only sql related services, gwmi is an alias of Get-WmiObject

$Services=gwmi win32_service -computername $server | where {$_.Name -like ‘*SQL*’}

#Test for unsuccesful connection

if(!(Test-Connection -Cn $server -BufferSize 16 -Count 1 -ea 0 -quiet))

{“Problem still exists in connecting to $server”}

ELSE {

$services | ForEach {

If ($_)

{ $Result += New-Object PSObject -Property @{

‘Service Display Name’ = $_.Displayname

‘Service Name’ = $_.Name

‘Start Mode’ = $_.Startmode

‘Service Account Name’ = $_.Startname

‘State’ = $_.State

‘Status’= $_.Status

‘System Name’ = $_.Systemname

}

}

}

}

}
$Result |Format-Table -AutoSize

*******************************************************************************

Output:

OR

HTML Format – Change the last line of the code

$Result | ConvertTo-HTML | Out-File F:\PowerSQL\service.htm

OR

CSV File -  Change the last line of the code

$Result |Export-CSV F:\PowerSQL\service.csv