PowerShell – Identify Service Account & Other Details of SQL Services – Multi Server Script

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:

Image

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

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL. Bookmark the permalink.

5 Responses to PowerShell – Identify Service Account & Other Details of SQL Services – Multi Server Script

  1. MM says:

    Doesn’t appear to be working for SQL Server 2012. Works for lower versions

  2. Asif Kaleem says:

    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.

  3. RK says:

    Hi Prashanth,
    This is script is working for me, need small modification, output has to come through email.

    Please add email steps into script.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s