→ 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: 
chadsharp
Engaged Sweeper
I have to audit all of our computers for the presence or absence of a specific piece of software.
Getting the machines that have the software is easy by scanning for the registry value but how do I search for machines that do not have that value? Here is what I have so far but it seems to pull all(2100+) computers on the domain?

Select Top 1000000 tblAssets.FQDN,
tsysOS.Image As icon,
tblRegistry.Regkey,
tblRegistry.Value,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblRegistry.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblRegistry.Valuename
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 != 'HKEY_LOCAL_MACHINE\SOFTWARE\HITACHI SOFT\HIBUN-AE'
And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try this:
SELECT TOP 1000000
tblAssets.FQDN,
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain
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 = 'HKEY_LOCAL_MACHINE\SOFTWARE\HITACHI SOFT\HIBUN-AE'
)
ORDER BY
tblAssets.AssetUnique

View solution in original post

2 REPLIES 2
chadsharp
Engaged Sweeper
Thank you for the response this is just what I needed.
RCorbeil
Honored Sweeper II
Try this:
SELECT TOP 1000000
tblAssets.FQDN,
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain
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 = 'HKEY_LOCAL_MACHINE\SOFTWARE\HITACHI SOFT\HIBUN-AE'
)
ORDER BY
tblAssets.AssetUnique

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now