→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dawnlowery
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

2 REPLIES 2
dawnlowery
Engaged Sweeper
Thank you! That works perfectly!
RCorbeil
Honored Sweeper II
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