Custom Report - Windows SERVERS Missing Security Agents (was Report Listing Duplicate Assets)

[LP]

I customized a report that lists Windows hosts that are missing standard agents that should have been installed. I wanted the report to list one host with all of the software agents on one line. The following report is almost there except that it lists hosts multiple times. One host may be listed twice whereas another may be listed 5 or 6 times and I'm not sure why. Can someone please look at the report code and see if you can find my mistake?

*** UPDATE 10/15/2023 ***

I was able to get the report to produce the desired results. If interested, the below code works for my needs. You would need to customize the software you want to report on.

Update Code:  Software: Windows SERVERS Missing Security Agents

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.OScode + '.' + tblAssets.BuildNumber As [Build Number],
  tblAssets.Version As [OS Version],
  Case
    When tblComputersystem.Domainrole = 4 Then 'Backup Domain Controller'
    When tblComputersystem.Domainrole = 5 Then 'Primary Domain Controller'
    When tblComputersystem.Domainrole = 3 Then 'Member Server'
  End As [Domain Role],
  tblAssets.Username,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  Case
    When soft01.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As [Arctic Wolf],
  Case
    When soft02.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As [Sentinel One],
  Case
    When soft03.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As SYSMON,
  Case
    When soft04.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As LAPS,
  Case
    When soft05.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As PingID,
  Case
    When soft06.AssetID Is Null Then '*** MISSING ***'
    Else 'Installed'
  End As [Patch MGR+],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model As Model,
  tblADComputers.OU,
  tblAssets.LastActiveScan,
  tblAssets.Lasttried,
  tblAssets.Lastseen,
  tblAssets.Firstseen
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Arctic Wolf%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Sentinel%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
  Left Join (Select tblAssets.AssetID
    From tblAssets
      Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
    Where tblFileVersions.FilePathfull Like '%sysmon64.exe') As soft03
    On soft03.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like
      '%Local Administrator Password Solution%') As soft04 On
      soft04.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%PingID for Windows version%') As
  soft05 On soft05.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%ManageEngine UEMS - Agent%') As
  soft06 On soft06.AssetID = tblAssets.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.Lastseen <> ' ' And ((soft01.AssetID Is Null) Or
    (soft02.AssetID Is Null) Or (soft03.AssetID Is Null) Or
    (soft04.AssetID Is Null) Or (soft05.AssetID Is Null) Or
    (soft06.AssetID Is Null)) And tblComputersystem.Domainrole >= 3 And
  tblAssetCustom.State = 1
Order By [Domain Role],
  tblAssets.AssetName

[ErikT]

Hi There, 

Any selected data that exists multiple times for the same asset will be returned on a separate row, which is expected as this is how SQL works by default. While it is technically possible to consolidate all these duplicates to just one entry per asset, this falls in the category of advanced SQL techniques.

This article might set you on your way: 
Rolling up multiple rows into a single row and column for SQL Server (mssqltips.com)