Report for specific Registry Key Missing

[davidkbailey]

Hi all,

I have a report that shows machines with a specific registry added, which allows some legacy (but business-critical) software to run correctly. I would like to show machines without this registry key so I can target them after scanning to deploy the registry key fix

The report is below. Can anyone help?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.State,
  tblRegistry.regkey,
  tblRegistry.Valuename,
  tblRegistry.Value
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblAssetCustom.State = 1 And
  tblRegistry.regkey =
  'HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\General'

[Josha · Accepted solution]

What I do is find all asset ID's that have the registry key, then I use that as a subquery to show me all assets that aren't in that list.  I'm self-taught so forgive me if this isn't the best way to do it.  See below for what I mean.

You have to make sure you're only looking at Windows assets as you're going to get printers/monitors/etc. that will never have a registry object in the database. 

Also, you want to add a registry value name to your statement to further narrow things down.  If you ever scan different values in the same registry key, your report is going to be off since the registry key will match but the value name won't.  It's better to be as precise as possible with the logic IMO.

For example:  

tblRegistry.regkey = 'HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\General' and tblRegistry.Valuename = 'LastAutoSavePurgeTime'

Select Top 1000000 
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.State
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 and tsysAssetTypes.AssetTypename = 'Windows' and 
tblAssets.AssetID not in (SELECT Distinct AssetID FROM tblRegistry WHERE Regkey = 'HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\General')