SQL server version info

[AZHockeyNut]

The built in shows the edition and sp level like sql express 2012 sp1 in its reports is there a report that returns also the number you would see when you query sql itself like 11.0.6248 since not all Dot releases are service packs?

[spgs]

But this report only reports correct Version, but does NOT report any Patch Level (which is rather important!)

So this is better code (from here 😞

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.version,
  tblSqlServers.spLevel As SP,
  tblSqlServers.fileVersion,
  tblSqlServers.serviceName As Service,
  tblSqlServers.lastChanged,
  tblSqlServers.dataPath,
  tblSqlServers.isWow64,
  tblSqlServers.language,
  tblSqlServers.installPath As installPath1,
  tblSqlServers.installPath
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition

Seb

[AZHockeyNut]

Thanks, however, it fails for me in Edge and IE with Invalid SELECT statement. Unexpected token "  Inner" at line 23, pos 1.: Unexpected token "  Inner" at line 23, column 1

When I run it direct in SQL Management studio(11.0.6248 sql express) the query works fine. Ideas?

[David_G]

We have added an edited version of the built-in report 'Software: All SQL server editions' that also shows the version number of the SQL instance.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As [SQL SP],
  tblSqlServers.version As Version,
  tblSqlServers.serviceName As Service,
  tblSqlServers.lastChanged,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition