List all assets and whether they have specific software installed via subquery

[ApexIT]

I've hammered on this issue for the last week. 

What I want:

List of all assets, and whether or not they have 3 pieces of software installed and if so, what versions of said software. If the asset does not have said software, the corresponding row should be empty. The following report will get me very close to what I want, but it seems to be limited to Windows assets, some of which have none, some, or all of the software installed.

Select Top 1000000 tblAssets.AssetID As AssetID,
  tblAssets.AssetName As AssetName,
  tblAssets.Domain As Domain,
  tblAssetCustom.Location As Location,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress As IPAddress,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.Description As Description,
  tblAssetCustom.Building As Building,
  tblAssetCustom.Manufacturer As Manufacturer,
  tblAssetCustom.Model As Model,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  Crowdstrike.softwareName,
  Crowdstrike.softwareVersion,
  ScreenConnect.softwareName As softwareName1,
  ScreenConnect.softwareVersion As softwareVersion1,
  Lansweeper.softwareName As softwareName2,
  Lansweeper.softwareVersion As softwareVersion2
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
      tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'lsagent') Lansweeper On
      tblAssets.AssetID = Lansweeper.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%crowdstrike%') Crowdstrike On
      tblAssets.AssetID = Crowdstrike.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%screenconnect%') ScreenConnect On
      tblAssets.AssetID = ScreenConnect.AssetID
Where tblAssetCustom.State = 1
Order By AssetName

[ApexIT · Accepted solution]

Solved with the following report, and added last Windows Update date with time-color gradient:

Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Crowdstrike.softwareVersion As [Crowdstrike Version],
  Lansweeper.softwareVersion As [Lansweeper Version],
  ScreenConnect.softwareVersion As [ScreenConnect Version],
  Max(Convert(date,WindowsUpdate.InstalledOn)) As lastpatchdate,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  Case
    When Max(Convert(date,WindowsUpdate.InstalledOn)) >= DateAdd(day, -7,
      GetDate()) Then '#d4f4be'
    When Max(Convert(date,WindowsUpdate.InstalledOn)) < DateAdd(day, -7,
      GetDate()) And Max(Convert(date,WindowsUpdate.InstalledOn)) >=
      DateAdd(day, -30, GetDate()) Then '#f7f0ca'
    When Max(Convert(date,WindowsUpdate.InstalledOn)) < DateAdd(day, -31,
      GetDate()) And Max(Convert(date,WindowsUpdate.InstalledOn)) >=
      DateAdd(day, -90, GetDate()) Then '#f2d59f'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
      tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'lsagent') Lansweeper On
      tblAssets.AssetID = Lansweeper.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%crowdstrike%') Crowdstrike On
      tblAssets.AssetID = Crowdstrike.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%screenconnect%') ScreenConnect On
      tblAssets.AssetID = ScreenConnect.AssetID
  Left Join (Select tblQuickFixEngineering.InstalledOn,
      tblQuickFixEngineering.AssetID
    From tblQuickFixEngineering) WindowsUpdate On WindowsUpdate.AssetID =
      tblAssets.AssetID
Where tblAssets.IPAddress Is Not Null And tblAssets.IPAddress <> '' And
  tblAssetCustom.State = 1
Group By tsysAssetTypes.AssetTypeIcon10,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tsysAssetTypes.AssetTypename,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  Lansweeper.softwareName,
  Lansweeper.softwareVersion,
  Crowdstrike.softwareName,
  Crowdstrike.softwareVersion,
  ScreenConnect.softwareName,
  ScreenConnect.softwareVersion