Custom View for Missing Software

[mikesecond]

I want to build a report that shows me all PCs missing a specific software - similar to no anti-virus installed report.

How do I create a custom table or view that would allow me to define the software title to be searched for?

[mikesecond]

Perfect, thank you

[RCorbeil · Accepted solution]

Try this as a starting point:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
  tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1
  AND tblAssets.AssetID NOT IN ( SELECT
                                   tblSoftware.AssetID
                                 FROM
                                   tblSoftware
                                   INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
                                 WHERE
                                   tblSoftwareUni.softwareName LIKE '%your_software_title%'
                               )