Query SQL Server Version? (standar/developer/enterprise)

[GuillerminGo]

Hi:

First of all, thanks in advance, at least for taking the time to read this post.

I´m having some problems when I try to query Microsoft SQL server version in our servers. Lansweeper reports the year of the version, but doesn´t show anything about standar, enterprise or developer (just what I need)

I would like to know if it´s possible to get this information from lansweeper or not.

I would also want to know where from is lansweeper taking this info (the one listed under the "software" tab),maybe from the reg key for what is listed in the "add/remove programs" place under control panel?

Thanks

[Hemoco]

This feature was built into Lansweeper 5.0. You can update your installations using this installer.

[Technut27]

I'm playing with this report myself. I'm making progress but what I'm running into is a machine might have SQL 2005 Express and SQL 2008 R2 Developer/standard/etc... I'm scanning for the 2008 R2 registry key successfully but then on my report I get two results, 1 for 2005 Express and 1 for 2008 R2 Developer. I don't want to know about the free Express editions, just the full ones. You can see in the screen shot that the 2008 key is being found but then both versions of SQL are being listed out.

How can I edit my report so it displays the 2008 R2 install that has the registry key present and exclude the 2005 Express version? I'll apologize for my limited SQL skills now Thanks ahead of time.

For example the registry value I'm scanning for SQL 2008 & 2008 R2 is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup\Edition "Developer, Standard, etc..." so the idea would be that if that key is present the report would display Microsoft SQL Server 2008 or 2008 R2 depending on what is in tblsoftware, the registry value to identify the version, but not SQL 2005 UNLESS the corresponding registry key for that version was also found.


This is my current report:

Select Top 1000000 tblADComputers.Computername, tblComputers.ComputerUnique, tblADComputers.Description, tblSoftware.softwareName, tblRegistry.Value As 'Version', tblRegistry.Regkey 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 Inner Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Where tblRegistry.Valuename = 'Edition'
And tblRegistry.Value Not Like '%Express%' Group By tblADComputers.Computername, tblComputers.ComputerUnique, tblADComputers.Description, tblSoftware.softwareName, tblRegistry.Value, tblRegistry.Regkey

Having tblSoftware.softwareName Like '%Microsoft SQL Server%'

And tblSoftware.softwareName Not Like '%Inside%'
And tblSoftware.softwareName Not Like '%Sample%'
And tblSoftware.softwareName Not Like '%Mobile%'
And tblSoftware.softwareName Not Like '%Books%'
And tblSoftware.softwareName Not Like '%Framework%'
And tblSoftware.softwareName Not Like '%Application%'
And tblSoftware.softwareName Not Like '%Native%'
And tblSoftware.softwareName Not Like '%Compact%'
And tblSoftware.softwareName Not Like '%Browser%'
And tblSoftware.softwareName Not Like '%Management%'
And tblSoftware.softwareName Not Like '%Setup%'
And tblSoftware.softwareName Not Like '%Language%'
And tblSoftware.softwareName Not Like '%Publishing%'
And tblSoftware.softwareName Not Like '%CLR%'
And tblSoftware.softwareName Not Like '%Policies%'
And tblSoftware.softwareName Not Like '%VSS%'
And tblSoftware.softwareName Not Like '%Backward%'
And tblSoftware.softwareName Not Like '%Analysis%'

Order By tblADComputers.Computername

[cbtg2006]

I had to perform a full rescan on the hosts I wanted the information from, although I would imagine the information is now in the database as by default the registry is scanned every 24hrs.

You can confirm if LANSweeper has scanned the registry either by:
a) Viewing the client in the Web Console > Config > Scanned Info > Registry Keys
Or
b) Using SQL Management Tools > Query the lansweeperdb > SELECT * from tblRegistry

[GuillerminGo]

Hi again:

Thanks for the modified SQL query; I´m scanning registry with the reg-keys you posted, but I´m having some problems because my custom report based on the SQL query returns only two versions installed in the same host (it returns what I need, it´s just I know that I have more tha 100 hosts with SQL server installed, and forced their scan but the query still returns the same)

I used lansweeper documentation to configure the custom report, so I think my problem is that for some reason, my configuration is not being applied.

I added the reg-keys to our registry scanning, I made a custom report with the SQL query (stopped active scanning, using only domain scanning, stop/started lansweeper service )and rescan entire network , but I only have results for one host. I´m a missing some configuration?

I keep trying to get this working, I´ll post a feedback as soon as I fiund what´s going wrong.

Thank you very much.

[cbtg2006]

SQL query for Computer Name is as follows:

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

[GuillerminGo]

Hi:

@cbtg2006:

Thanks for this valuable information. I´m trying it right now. I´ll wait some time to get all this information.
I´m going to modify the query to display a list fo computers with their SQL version. I´ll post it here as soon as possible, maybe it can help someone else.

[GuillerminGo]

Hi again:

Just what I feared ====>

Thanks for the links to microsoft KB as well as for the quick response, I´ll give it a try.

[cbtg2006]

GuillerminGo wrote:
Hi again:

Just what I feared ====>

Thanks for the links to microsoft KB as well as for the quick response, I´ll give it a try.

I have just performed an inventory of our SQL products and needed to get version information... the good news is that it CAN be done. but it's not as simple as you might like.

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:

Select Top 1000000 tblSoftware.softwareName As Product, tblRegistry.Value As 'Version',
tblComputers.Domain, Count(tblSoftware.ComputerName) As Count 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 tblSoftware.softwareName, tblRegistry.Value, tblComputers.Domain Order By tblComputers.Domain Desc

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

[Hemoco]

Lansweeper lists the same software information that can be found under Add/Remove Programs (or Programs & Features) on the machine.

Microsoft has made it extremely difficult to recover edition info (Enterprise, Express etc.) for SQL Server. There are only a few ways to do it, which are described here. These methods cannot be replicated by Lansweeper and hence Lansweeper cannot scan edition info.