Report showing systems without Reg Key

[TheGuide]

I am trying to get a report that shows me all the workstations that do not have a specific reg key on them. On the report I am looking for the following fields:
tblAssets.AssetName,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model

I want to see only the systems that do not have the following reg key (key is present if built with MDT):
Key: HKEY_LOCAL_MACHINE\Software\Microsoft\Deployment 4

Value Name: Task Sequence Name

As a bonus, I would like to be able to filter out specific PC naming schemes based on the first 3 characters of the assetName such as "SGS%" or "SGV%". This would be a nice to have, not a must have.

I've tried various pieces of code. Either I get empty lists or lists that show systems that do have the key on them. Here is the last code I tried to use:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblAssets Inner Join tblRegistry On tblAssets.AssetID =
tblRegistry.AssetID
Where tblRegistry.Regkey Like '%SoftwareMicrosoftDeployment 4' And
tblRegistry.Valuename = 'Task Sequence Name') And tblAssets.Lastseen <> ''
And tsysAssetTypes.AssetTypename = 'windows' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Any help is appreciated.

[RCorbeil]

Poor interpretation on my part. I read "filter out" as a desire to isolate but removal makes more sense.

Glad to hear you got it working.

Cheers.

[TheGuide · Accepted solution]

Results were similar to what I received before.

1) I am trying to remove "SGS%" or "SGV%" from the results, not isolate to them. Poor wording on my part. But I think I figured this out. Needed the And command instead of the Or command for multiple selections.

2) I still had a bunch of systems on the report that shouldn't be there. In fact, the list is pretty much the entire windows workstations list. As if it's not actually filtering out the ones that have the reg key. Although I think I just fixed this by adding "\" inline in the appropriate spots for the reg key: %Software\Microsoft\Deployment 4

Ultimately, here is what I got to work.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblRegistry On tblRegistry.AssetID = tblAssets.AssetID And
tblRegistry.Regkey Like '%Software\Microsoft\Deployment 4' And
tblRegistry.Valuename = 'Task Sequence Name'
Where tblAssets.AssetName Not Like 'SGS%' And tblAssets.AssetName Not Like
'SGV%' And tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
tblRegistry.AssetID Is Null And tblAssets.Assettype = -1
Order By tblAssets.Domain,
tblAssets.AssetName

Thanks for the help!!

[RCorbeil]

Try this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
FROM
  tblAssets
  INNER JOIN tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  LEFT JOIN tblRegistry ON tblRegistry.AssetID = tblAssets.AssetID
                           AND tblRegistry.Regkey Like '%SoftwareMicrosoftDeployment 4'
                           AND tblRegistry.Valuename = 'Task Sequence Name'
WHERE
  tblAssetCustom.State = 1
  AND tblRegistry.AssetID IS NULL  --key not found
  AND tblAssets.AssetType = -1     --Windows
  And tblAssets.Lastseen <> ''
  AND ( tblAssets.AssetName LIKE 'SGS%'
        OR tblAssets.AssetName LIKE 'SGV%'
      )
ORDER BY
  tblAssets.Domain,
  tblAssets.AssetName

Doing a LEFT join against tblRegistry ensures that you retain all assets in your result set, regardless of whether or not the key was found. In the WHERE clause, you then filter the result set for only those assets with no corresponding tblRegistry result.