LANSweeper : Query SQL Server Version

LANSweeper : Query SQL Server Version

SQL version information is stored in the registry, its location varies depending on server architecture (x86/x64) and SQL Instance name.

This is how I achieved this….

Added the following Registry paths to be scanned by LANSweeper (you may not need all of these, could well need to add some of your own):

RegKey ValueName
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2005\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SHAREPOINT\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SOPHOS\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SHAREPOINT\Setup Edition
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup Edition

Next, scan all of your machines.

Finally use the following code to return the information for your domain, by version:

{code lang:sql title:”LANSweeper Query SQL Version” lines:false hidden:false}Select Top 1000000 tblComputers.ComputerName, tblComputers.ComputerUnique As Machine , tblSoftware.softwareName As Product, tblRegistry.Value As ‘Version’,
tblComputers.Domain

From tblSoftware
Inner Join tblComputers On tblSoftware.ComputerName = tblComputers.Computername
Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername
Inner Join tblRegistry On tblComputers.Computername = tblRegistry.Computername

Where tblSoftware.softwareName Like ‘Microsoft SQL Server%’ And tblRegistry.Valuename = ‘Edition’
And tblSoftware.softwareName Not Like ‘%Native Client%’ And tblSoftware.softwareName Not Like ‘%Policies%’
And tblSoftware.softwareName Not Like ‘%Setup%’ And tblSoftware.softwareName Not Like ‘%Browser%’
And tblSoftware.softwareName Not Like ‘%VSS%’ And tblSoftware.softwareName Not Like ‘%Books%’
And tblSoftware.softwareName Not Like ‘%Compatibility%’ And tblSoftware.softwareName Not Like ‘%Analysis%’
And tblSoftware.softwareName Not Like ‘%Management Objects%’ And tblSoftware.softwareName Not Like ‘%Design Tools%’
And tblSoftware.softwareName Not Like ‘%Studio%’ And tblSoftware.softwareName Not Like ‘%Query Tools%’
And tblSoftware.softwareName Not Like ‘%Best Practice%’ And tblSoftware.softwareName Not Like ‘%CLR%’
And tblSoftware.softwareName Not Like ‘%Advisor%’ And tblSoftware.softwareName Not Like ‘%Reporting%’
And tblSoftware.softwareName Not Like ‘%Data Mining%’ And tblSoftware.softwareName Not Like ‘%Wizard%’
And tblSoftware.softwareName Not Like ‘%AdventureWorks%’ And tblSoftware.softwareName Not Like ‘%Transact-SQL%’
And tblSoftware.softwareName Not Like ‘%Refresh 3 Samples%’ And tblSoftware.softwareName Not Like ‘%Developer Tools%’

Group By tblComputers.ComputerUnique, tblComputers.ComputerName, tblSoftware.softwareName, tblRegistry.Value, tblComputers.Domain
Order By tblSoftware.softwareName{/code}

Modify the code if you want to return the version per computer or anything else for that matter… 🙂