This powershell script will scan the registry of servers remotely.

$infile = ".\" + $args[0] + ".csv"
$outfile = ".\" + $args[0] + "_out.csv"

$ErrorActionPreference = "SilentlyContinue"
"Server, Instance, Version, Edition" | Out-File $outfile
foreach ($Server in import-csv $infile)
{
  $server
  $reg = $null
  $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Server.Name)

  $regkey = $null
  $regkey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
  IF ($regkey){
  foreach ($regInstance in $regkey.GetValueNames())
  {
    $regInstanceData = $regKey.GetValue($regInstance)
    $versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Setup")
    $version = $versionKey.GetValue('PatchLevel')
    $edition = $versionKey.GetValue('Edition')
    $VersionInfo = $Server.Name + ',' + $regInstance + ',' + $version + ',' + $edition
    $versionInfo | Out-File $outfile -Append
  }}

  $regkey = $null
  $regkey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server")
  IF ($regkey){
  foreach ($regInstance in $regkey.GetValue('InstalledInstances'))
  {
    IF ($regInstance -eq "MSSQLServer")
    {
      $versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\MSSQLServer\\Setup")
    }
    ELSE
    {
      $versionKey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstance\\Setup")
    }
    IF ($versionkey)
    {
      $version = $versionKey.GetValue('PatchLevel')
      $edition = $versionKey.GetValue('Edition')
      IF ($version)
      {
        $VersionInfo = $Server.Name + ',' + $regInstance + ',' + $version + ',' + $edition
        $versionInfo | Out-File $outfile -Append
      }
    }
  }}

}

What you need is a list of servers.
It should have a csv extension and the first column should say Name.

Then simply run the script giving the filename without the csv extension as a parameter :

The result will be written to a file with the same name as the input file but with _out.csv :

Do you have a lot of servers, and you want to know how many you need to patch ? :

This relies on the Remote Registry service running on the server you are scanning and you also need to have sufficient rights (local admin). I’m using it myself to scan the boxes that I manage since I’m a local admin on each one. But to get an overall scan done of the entire network I’ve asked a domain admin to run it for me and send me the resulting out_csv.

(I’ve created a list of servers for him to scan using this method)

The script is tested starting with SQL Server 2000 and al the way up to 2008 R2
(2000 stores the info in the registry in a different location from later editions of SQL Server, which is why the script is as long as it is.
However, it did find a frightening amount of SQL Server 2000 servers at my customers site  )

Getting a list of all servers from your active directory is simple, and looks something like this :

I wanted to get the list of servers (dc, sql1 & sql2).
In Powershell this can be done by splitting the line 3 times.

This looks like this :

@(dsquery computer -limit 0
      | %{$_.split("`"")[1]}
      | %{$_.split(",")[0]}
      | %{$_.split("=")[1]})

@ is used to call the dsquery process.
% is short for for-each

• So, split on “  (the backtick is the escape character) and taking the second value from the resulting array is the first step and removes the quotes.
• After that, split on , will break the line up in key/value pairs where only the first value from the array will be used (CN=…)
• Finally split on the = and take the second value in the resulting array. This leaves you with a list of servers….

Want to filter out stuff ?

?{$_ -like '*OU=Servers*'}

use this after the first pipe symbol an pipe the results into the for-each statements.
The ? means where-object or where.
This example shows you only the OU’s named “Servers”.