Creating a report for laptops missing specific regsitry key

[jaywill80]

Created a report to query on all assets that are missing a specified registry key - how can I scope this down to just query back on laptops in an OU that missing the registry value?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblAssets.AssetID
From tblAssets Inner Join tblRegistry On tblAssets.AssetID =
tblRegistry.AssetID
Where tblRegistry.Regkey Like '%SOFTWARE\gppreauth' And
tblRegistry.Valuename = 'Expires') And tsysAssetTypes.AssetTypename =
'windows' And tblAssets.Lastseen <> '' And tblAssetCustom.State = 1

[jaywill80]

Thanks works great!

[Esben_D]

To used your query and added some table to just filter on laptops and OU. For the laptops I added "tblPortableBattery.AssetID Is Not Null". Since only laptops will have a portable battery, this must have a value for the asset to be a laptop.

For the OU filter I added tblADComputers.OU. You will have to add the OU by replacing the highlighted code below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID Not In (Select tblAssets.AssetID
From tblAssets Inner Join tblRegistry On tblAssets.AssetID =
tblRegistry.AssetID
Where tblRegistry.Regkey Like '%SOFTWARE\gppreauth' And
tblRegistry.Valuename = 'Expires') And tsysAssetTypes.AssetTypename =
'windows' And tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
tblPortableBattery.AssetID Is Not Null And tblADComputers.OU = 'Your OU'