Need SQL Report for multiply SQL Server Installations for each asset

[tenupmoc]

Hi,

for an audit, i need a SQL Report, that shows all SQL Server Version Installation on each assets.

For example:
There is an asset with three SQL Server Installations
1. SQL Server 2017 Standard
2. SQL Server 2014 Express
3. SQL Server 2016 Developer

There are only one SQL DB Instance for SQL Server 2016 Developer installed on that asset.

If i did a standard SQL Server report for Editions, it shows only the SQL Server 2016 Developer installation for that asset. The two other installations are missing.

If i did a report for SQL Softwareinstallation by Softwarename, all three versions will be reported, but all Versions have the same edition entry "Developer Edition".

So i need a report, which report all installed SQL Versions for each asset and with the right version?

any ideas

[brandon_jones · Accepted solution]

Try this. Please let me now if you need something removed or added.

Select Top (1000000) Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
  icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As [SQL SP],
  tblSqlServers.serviceName As Service,
  tblSqlServers.lastChanged,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
      tsysIPLocations.LocationID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition

[tenupmoc]

wonderful 🙂