Show Report Results That Meet A Series of Requirements

[alidbeck]

I'm looking for some help limiting the results of the report I have created. My report is showing the status of whether or not certain applications are installed for each workstation in our environment. What I am looking to accomplish, is to limit the results to only the machines where at least one application is marked as "not installed". So if all applications are installed, then that workstation would not display on the report.

Here is the current code I am using.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like
'%ManageEngine Desktop Central - Agent%') Then 'Not Installed'
Else 'Installed'
End As [Desktop Central Agent],
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like
'%Duo Authentication for Windows Logon x64%') Then 'Not Installed'
Else 'Installed'
End As [Duo Agent],
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Nessus Agent (x64)%')
Then 'Not Installed'
Else 'Installed'
End As [Nessus Agent],
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%DefenseStorm%') Then
'Not Installed'
Else 'Installed'
End As [DefenseStorm Agent],
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%lsagent%') Then 'Not Installed'
Else 'Installed'
End As [LanSweeper Agent],
Case
When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sophos Endpoint Agent%') Then
'Not Installed'
Else 'Installed'
End As [Sophos Endpoint Agent]
From tblAssets
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName

Assuming you can follow what I am after, any tips on this would be much appreciated.
THanks.

[Alig]

Hi,
So I updated this report a bit to include other required fields for our purposes, but now I'm getting duplicates in the report. can someone help me in what I'm doing wrong:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain As Domain,
  tsysAssetTypes.AssetTypename,
  Case
    When Soft01.AssetID Is Not Null Then 'Installed'
    Else 'Not Installed'
  End As [Desktop Central Agent],
  Case
    When Soft02.AssetID Is Not Null Then 'Installed'
    Else 'Not Installed'
  End As [CrowdStrike Sensor],
  Case
    When Soft03.AssetID Is Not Null Then 'Installed'
    Else 'Not Installed'
  End As Zscaler,
  Case
    When Soft04.AssetID Is Not Null Then 'Installed'
    Else 'Not Installed'
  End As [TeamViewer Host],
  tsysOS.OSname As [OS Name],
  tblAssets.Firstseen As [Created Date],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt]
From tblAssets
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like '%ManageEngine Desktop Central - Agent%')
  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 '%CrowdStrike Windows Sensor%') As
  Soft02 On Soft02.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Zscaler%') 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 '%TeamViewer%Host%') As Soft04 On
      Soft04.AssetID = tblAssets.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join lansweeperdb.dbo.tsysAssetTypes On tsysAssetTypes.AssetType =
      tblAssets.Assettype
Where (Soft01.AssetID Is Null Or Soft02.AssetID Is Null Or
    Soft03.AssetID Is Null Or Soft04.AssetID Is Null)
Order By tblAssets.AssetName

[alidbeck]

This is exactly what I needed, thank you so much for your help!

[RCorbeil]

You want to reference the software twice -- once to display the installed status, once on the filter -- so I would suggest that you take a slightly different tack: set up your connections once, then reference those connections.

SELECT TOP 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  CASE
    WHEN Soft01.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [Desktop Central Agent],
  CASE
    WHEN Soft02.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [Duo Agent],
  CASE
    WHEN Soft03.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [Nessus Agent],
  CASE
    WHEN Soft04.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [DefenseStorm Agent],
  CASE
    WHEN Soft05.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [LanSweeper Agent],
  CASE
    WHEN Soft06.AssetID IS NOT NULL
	THEN 'Installed'
	ELSE 'Not Installed'
  END AS [Sophos Endpoint Agent]
FROM
  tblAssets
  LEFT JOIN (SELECT tblSoftware.AssetID
             FROM tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE
               tblSoftwareUni.softwareName LIKE '%ManageEngine Desktop Central - Agent%') 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 '%Duo Authentication for Windows Logon x64%') AS Soft02 ON Soft02.AssetID = tblAssets.AssetID
  LEFT JOIN (SELECT tblSoftware.AssetID
             FROM tblSoftware
               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
             WHERE
               tblSoftwareUni.softwareName LIKE '%Nessus Agent (x64)%') 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 '%DefenseStorm%') 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 '%lsagent%') 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 '%Sophos Endpoint Agent%') AS Soft06 ON Soft06.AssetID = tblAssets.AssetID
WHERE
  tblAssets.Assettype = -1
  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)
ORDER BY
  tblAssets.AssetName

The LEFT JOINs ensure you don't filter out any of the assets because software isn't installed.

Where each piece of software is installed, you will have an AssetID (e.g. Soft01.AssetID). If that software is not installed, when you try to reference the AssetID, you'll be looking at NULL. You use that to determine whether to display installed/not installed. You also use that to create your filter in the WHERE clause: only include an asset if at least one of the software joins has no AssetID.