********* UPDATE 05/08/2019 *********
It’s been a while (4 years!) since using this script, and have found a few flaws in its logic and execution, especially when it comes to SQL 2000 instances (yes, they’re still very much out there).
The script below has now been updated and tested and should work correctly on servers that have multiple SQL instances installed.
****************************************
Original Post May 1st 2015
When you need to perform an audit of SQL Server installations (by edition) across a huge number of servers, this script should save you a whole lot of time.
Assuming your servers are online and not being blocked by the Windows Firewall, run the below script, and your output should appear in the CSV specified at the start of the script in the $outfile variable.
You might want to drop the latter output commands for hosts that are not online or do not have any SQL registry keys found, to reduce the noise in your output file, but I’ll leave them included here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
# Define output file $outfile = "c:\temp\sqlversioncheck.csv"; # Get all server names from the saved txt file $adservers = get-adcomputer -filter {operatingsystem -like "*server*"} -properties lastlogondate, operatingsystem | where {$_.lastlogondate -gt (get-date).adddays(-60)}; # Loop through each server foreach ($adserver in $adservers) { $out = $null; $server = $adserver.name; # Check if computer is online if (test-connection -computername $server -count 1 -ea 0) { try { # Define SQL instance registry keys $type = [Microsoft.Win32.RegistryHive]::LocalMachine; $regconnection = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server); $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"; $openinstancekey = $regconnection.opensubkey($instancekey); # If registry keys don't exist, try legacy keys if ($openinstancekey -eq $null) { $sqlserverkey = "SOFTWARE\Microsoft\Microsoft SQL Server"; $opensqlkey = $regconnection.opensubkey($sqlserverkey); $instances = $opensqlkey.getvalue("InstalledInstances"); # Extract "full" instance names for non-legacy instances. } else { [System.Collections.ArrayList]$instances = @(); foreach ($instance in $openinstancekey.getvaluenames()) { $instancename = $openinstancekey.getvalue($instance); [void]$instances.add($instancename); } } if ($instances) { # Loop through each instance found foreach ($instance in $instances) { # Define and open SQL registry keys $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instance; $openinstancekey = $regconnection.opensubkey($instancekey); if ($openinstancekey) { # Get instance name $instancename = $openinstancekey.getvalue("") # Check for legacy intance name information if ($instancename -eq $null) { $instancename = $instance } # Define and open SQL setup registry keys $instancesetupkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instance + "\Setup"; $openinstancesetupkey = $regconnection.opensubkey($instancesetupkey); # Get Edition $edition = $openinstancesetupkey.getvalue("Edition") # Check for legacy edition information if ($edition -eq $null) { $edition = "N/A"; } # Get Version $version = $openinstancesetupkey.getvalue("Version"); # Check for legacy version information if ($version -eq $null) { $currentversionkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instance + "\MSSQLServer\CurrentVersion"; $opencurrentversionkey = $regconnection.opensubkey($currentversionkey); $version = $opencurrentversionkey.getvalue("CSDVersion"); } switch -wildcard ($version) { "14*" {$versionname = "SQL Server 2017";} "13*" {$versionname = "SQL Server 2016";} "12*" {$versionname = "SQL Server 2014";} "11*" {$versionname = "SQL Server 2012";} "10.5*" {$versionname = "SQL Server 2008 R2";} "10.4*" {$versionname = "SQL Server 2008";} "10.3*" {$versionname = "SQL Server 2008";} "10.2*" {$versionname = "SQL Server 2008";} "10.1*" {$versionname = "SQL Server 2008";} "10.0*" {$versionname = "SQL Server 2008";} "9*" {$versionname = "SQL Server 2005";} "8*" {$versionname = "SQL Server 2000";} default {$versionname = $version;} } # Output results $out = $server + "^" + $instancename + "^" + $edition + "^" + $versionname; $out >> $outfile; } } } else { $out = $server + "^Could not find/open SQL registry keys"; $out >> $outfile; } } catch { $out = $server + "^Could not find/open SQL registry keys"; $out >> $outfile; } } else { $out = $server + "^Not online"; $out >> $outfile; } } |
All things being equal, you should get something like this (which you can bring into Excel if you need to):
And here’s a handy chart for converting those version numbers into something more meaningful: http://sqlserverbuilds.blogspot.com.au/
Make sure there is no space after the computer name in “servers.txt”, that will result in a failure to test the connection to the host (ask me how I know…)
Painful, but a good lesson to learn, I suppose. I might start using the Trim method when getting server names from untrusted sources (ie; not straight straight from Active Directory) just in case there are any pesky white spaces before or after.
Nice ! Thanks for this very helpful script and all informations.
ge
need service pack details also…
If you check the link I provided at the end of the article, it shows you which version numbers are for each Service Pack. You just need to re-write the switch section of the script to include the extra numbers to delineate the Service Pack information.
Thanks..very good script and works great. Is there any script like this to find out all the databases and their sizes as well using list of servers
How can I include an account string with permissions to query the registry?
You have two choices (since you can’t pass credentials in the script as it’s written):
use “run-as” for your PowerShell window with an account that has the necessary permissions on the remote computer (this is the easiest method and how I’d do it)
OR
switch to using WMI to query the registry instead of:
Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server)(this would require a lot of changes to the script)Is there a way to modify it so that if you have multiple instances on the same machine it reads them all?
The script has now been corrected and updated. Please give it a go now, and let me know how you go.
This script runs against AD to collect server names.
I already have a list of all my database servers. How can I run this script against a list of database servers names list in a text file?
So long as the text file has one server name per line, then remove this line:
$adservers = get-adcomputer...And replace it with these two lines:
$filename = "c:\temp\sqlserverlist.txt$adservers = get-content $filename;
Also, replace this line:
$server = $adserver.name;With:
$server = $adserver;