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
Reblogged this on Sutoprise Avenue, A SutoCom Source.
LikeLike
Doesn’t appear to be working for SQL Server 2012. Works for lower versions
LikeLike
Thank you..Let me work on the script.
LikeLike
Thanks, works great and I am able to get the output in html format for all the SQL servers. Learn the command test-connection. I have not used it. Also want to explore PSObject. This is new to me. Used gwmi in several of my scripts. Thanks again.
LikeLike
Hi Prashanth,
This is script is working for me, need small modification, output has to come through email.
Please add email steps into script.
LikeLike