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?

[jasonlalanne]

Thanks! this is great.  

[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

[jasonlalanne]

How would I add the ESXi server these SQL servers are running on?  

[Mister_Nobody]

try this

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  SubString(tblAssets.Description, 1, 25) As description,
  tblVmwareGuest.Name As VMName,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
  tblVmwareGuest.guestfullname) As OS,
  tblAssets.IPAddress,
  tblVmwareGuest.MacAddress,
  tblVmwareInfo.HostName,
  tblVmwareInfo.version As ESXi_Version,
  tblVmwareGuest.CpuCount As VMCoreCount,
  tblVmwareGuest.Memory As VMVemory,
  Cast(tblVmwareGuest.UnsharedStorage As bigint) / 1024 / 1024 As VMUsedSize,
  Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
      1024 As Numeric)) From tblLinuxHardDisks
    Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
  Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
      1024 As Numeric)) From tblDiskdrives
    Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
  tblVmwareGuest.BootTime,
  tblVmwareGuest.Version,
  tblAssets.Firstseen,
  tblVmwareGuest.lastchanged,
  tblSqlServers.displayVersion,
  tblSqlServers.serviceName,
  tblSqlServers.fileVersion,
  tblSqlServers.version As version1
From tblVmwareGuest
  Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
  Inner Join tblSqlServers On tblVmwareGuest.AssetID = tblSqlServers.AssetID
  Left Join tblAssets On tblAssets.AssetID = tblVmwareGuest.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Order By tblAssets.AssetName