Report which shown both SQL Server version and other specific software intalled

[Sergey_Uvarov]

Hello. I need help with some specific report.
I need to create a custom report, which would be displayed in parallel columns version and edition of the installed Microsoft SQL Server and the version of another specific software that is also installed on the computer. All that I have previously tried to create, displays only one type of software, but I need to line where the basic information on the computer and the versions of these 2 applications will be. Thank you in advance for your help!

[RCorbeil]

FROM
  ...
  INNER JOIN tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
  ...
WHERE
  tblassetcustom.State = 1
  AND tblComputerSystem.DomainRole > 1

DomainRole 0 and 1 are standalone and member workstations, respectively. DomainRole 2, 3, 4, 5 are servers.

If you want to display the role name,

FROM
  ...
  INNER JOIN tblDomainRoles ON tblDomainRoles.DomainRole = tblComputerSystem.DomainRole

and add tblDomainRoles.DomainRoleName to your report.

[Sergey_Uvarov]

Big thanks for the report. One small question - what parameter must be added so that the result is shown only server based on Windows Server?

[RCorbeil]

Select Top 1000000
  tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblSqlServers.displayVersion As [SQL Server version],
  tblSqlServers.skuName As [SQL SKU name],
  tblSqlServers.fileVersion As [SQL file version],
  s1.SoftwarePublisher AS [soft1 Publisher],
  s1.SoftwareName AS [soft1 Name],
  s1.SoftwareVersion AS [soft1 Version],
  s2.SoftwarePublisher AS [soft2 Publisher],
  s2.SoftwareName AS [soft2 Name],
  s2.SoftwareVersion AS [soft2 Version],
  tblassets.Lastseen,
  tblassets.Lasttried
From
  tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
  Left Join (SELECT
               tblSoftware.AssetID,
               tblSoftwareUni.SoftwarePublisher,
               tblSoftwareUni.SoftwareName,
               tblSoftware.SoftwareVersion
             FROM tblSoftware
               Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
             WHERE
               tblSoftwareUni.SoftwareName LIKE '%software name 1%'
            ) AS s1 ON s1.AssetID = tblAssets.AssetID
  Left Join (SELECT
               tblSoftware.AssetID,
               tblSoftwareUni.SoftwarePublisher,
               tblSoftwareUni.SoftwareName,
               tblSoftware.SoftwareVersion
             FROM tblSoftware
               Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
             WHERE
               tblSoftwareUni.SoftwareName LIKE '%software name 2%'
            ) AS s2 ON s2.AssetID = tblAssets.AssetID
Where
  tblassetcustom.State = 1
Order by
  tblAssets.AssetName,
  tblSqlServers.displayVersion,
  tblSqlServers.skuName