How to run a query to display an application that is not installed

[dawnlowery]

I have a custom scan based upon a certain registry key being present. I'm not a programmer so how can I tweek my query to give me a list of machines that do not have the registry key?

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblRegistry.Regkey Like '%SOFTWARE\PGP Corporation\Common' And
tblRegistry.Valuename = 'ProductVersion' And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[dawnlowery]

Thank you! That works perfectly!

[RCorbeil · Accepted solution]

This should do what you're asking for.

SELECT TOP 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain
FROM
  tblAssets
  INNER JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE
  tblAssetCustom.State = 1
  AND NOT EXISTS ( SELECT tblRegistry.AssetID
                   FROM tblRegistry
                   WHERE tblRegistry.AssetID = tblAssets.AssetID
                     AND tblRegistry.Regkey LIKE '%SOFTWARE\PGP Corporation\Common'
                     AND tblRegistry.Valuename = 'ProductVersion'
                 )
ORDER BY
  tblAssets.AssetUnique