Inventory SQL Server Versions on Multiple Computers

I've got a text file that lists all of the SQL Server computer names in my environment, listing one name per line. It's called sqlservers.txt. My goal is to find out which version(s) of SQL Server each of those computers is running. I'm fortunate in that I have PowerShell v2 installed on each of those computers, and I've got Remoting enabled (I ran Enable-PSRemoting on them manually). We're all in the same domain, and I'm a local admin on each.

All I have to do is run this:

Invoke-Command -scriptblock { dir -path 'c:program filesmicrosoft sql server' -recurse -include 'sqlservr.exe' | select -expand versioninfo } -computer (get-content sqlservers.txt)

I'll get back a list that includes computer names, product version (like 10.0.2531.0), file version, and complete file name and path. Easy, and it runs fast too.

Of course, if you don't have PowerShell v2 installed and remoting enabled on all of those machines, this won't work. Another technique, then, would be to use WMI. This is a bit more painful because it'll take a while to run, but you can do this:

Get-WmiObject -class CIM_Datafile -filter "filename LIKE '%sqlservr%'" -computer (get-content sqlservers.txt) | select __SERVER,version

This only returns the file version, which will look something like "2007.100.2531.0." This should actually work on any computer all the way back to Windows NT 4.0, believe it or not, provided you're a local admin and that WMI is accessible from the network (meaning it's not blocked by a firewall or something). It will, however, take a LONG time to run because it's basically scanning every file on the computer.

A final approach, which also uses WMI, is to use the Win32_Product class. That's available by default on Server 2008 and later, and can be installed on Win2003. It's also a bit slow, and your mileage may vary in terms of its accuracy in reporting installed software products. An upside I noticed in my testing is that it can more easily access all the various SQL Server components when you do something like this:

Get-WmiObject -class Win32_product -computer (get-content sqlservers.txt) | where { $_.name -like '*sql server*' }

There are other techniques I can think of, such as digging the information out of the registry, but these were three quick and easy ways that could be adapted to many other software products, too.