Find those rogue SQL Servers in your Enterprise with PowerShell

Find those rogue SQL Servers in your Enterprise with PowerShell

  • Comments 1

I found a great way to check and see if a server has SQL Server installed on it. It uses “WMI”, or Windows Management Instrumentation and PowerShell. It simply interrogates the Services on a system to see if SQL Server is there, whether it is running or not.

All it is doing is using the gwmi (get-wmi) command-let and asks for all services that have MSSQL-something in the name, and then filters further out to only include the services that have the word “transaction” in the description field of the service. That shows the database engine only, which is usually what you care about. It stores all that information in a variable, which is actually a collection of items. From that collection, I simply loop through them, showing only the name and whether the service is running or not. You could save out this information to Microsoft Excel, a text file, an e-mail or whatever. I don’t show that here, but it wouldn’t be challenging to do that.

Here’s the complete script:

 

   1: # Find SQL Server Instances 
   2: $SQLServices = gwmi -query "select * from win32_service where Name LIKE 'MSSQL%' and Description LIKE '%transaction%'"
   3:  forEach ($SQLService in $SQLServices) {write-host  $SQLService.Name $SQLService.State} 

 

You can connect to another server and check using a “driver file” or list if you like, or in the new version of PowerShell (not yet out) you can use remoting. I just run this on each system, after I make sure that PowerShell is installed there.

 

As always, this warning applies to any script you find anywhere, including here.

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • A reader named "Ben" wrote me and said that he looked at my script in PowerShell that finds

Page 1 of 1 (1 items)