Report for Missing Software Using Another Software As a Filter/Prereq

[lansweeper_DGM]

I'm hoping someone would be able to assist with writing a report that would be able to show devices that are missing a piece of software.  This is somewhat similiar to this post:
https://community.lansweeper.com/t5/reports-analytics/report-request-computers-missing-one-of-two-pi...

However, in our scenario I'd like to assume that 1 piece of software always installs properly and should be used as the prerequiste filter for determining if the second piece is missing or failed.  So using the example linked above the "ESET admin agent %" would be software we want to use to compile our base list of devices and then have the report output which machines in that list are missing the 'ESET AV client %'

Seems like it might be complicated because it may require a query inside another query?  Any help would be appreciated.  Thanks!

[DavidPK · Accepted solution]

We've created a sample report below. A SQL query that identifies devices with "ESET admin agent %" installed but missing "ESET AV client %":

In addition, we would like you to keep in mind that we can only provide limited support for building custom reports and a limited number of sample reports.
 

If you are interested in building or modifying reports, we do recommend:
• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial: https://www.w3schools.com/sql/
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here: https://www.lansweeper.com/knowledgebase/accessing-the-lansweeper-database-documentation/
Checking out our report library: https://www.lansweeper.com/report/

SELECT
   tblAssets.AssetID,
   tblAssets.AssetName,
   tsysAssetTypes.AssetTypeIcon10 AS icon,
   tblAssets.Lastseen
FROM
   tblAssets
   INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
   INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
   tblAssetCustom.State = 1
   AND tsysAssetTypes.AssetType = -1
   AND tblAssets.AssetID IN (
       -- Subquery to find AssetIDs with "ESET admin agent %"
       SELECT
           tblSoftware.AssetID
       FROM
           tblSoftware
           INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
       WHERE
           tblSoftwareUni.softwareName LIKE 'ESET admin agent %'
   )
   AND tblAssets.AssetID NOT IN (
       -- Subquery to find AssetIDs with "ESET AV client %"
       SELECT
           tblSoftware.AssetID
       FROM
           tblSoftware
           INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
       WHERE
           tblSoftwareUni.softwareName LIKE 'ESET AV client %'
   )
ORDER BY
   tblAssets.AssetName;

[lansweeper_DGM]

Thanks DavidPK, it work great!