… and yet another way to find SQL Server Instances with PowerShell …

I got another great comment today on the last two posts:

Good article today on searching out SQL servers with PS. The combination of your and Ben’s methods work best though. In Ben’s example, you have to pull back all the Win32_Service objects and then are filtering locally. The more efficient way is like your script, to have the WMI adapter perform the filtering locally and then return only the result objects.

Also, if you have the Quest AD cmdlets loaded, a sweep of AD becomes really quick. In my example below, searching all systems in AD with “sql” in the computer name.

1: get-qadcomputer *sql* | foreach

{gwmi win32_service -computername [body] $_.name

-Filter “(name like ‘%sql%’) AND (description like ‘%transaction%’)”

-ErrorAction SilentlyContinue | select SystemName,Name,Caption

You could also output that to a variable for later working of the objects, etc, etc. Or replace the get-qadcomputer with an input object with a list of systems. Lots of options.

Awesome blog!