Defining single reg key in report

[Liamsy11]

I need to limit the report to only show the following registry key.

HKLM\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion\Public-Opstate\ Value: LatestVirusDefsDate

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
TsysLastscan.Lasttime As LastRegistryScan,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago. Scanned data may not be accurate.'
End As Comment,
Case
When tblRegistry.Valuename Is Not Null And tblRegistry.Valuename <> '' Then
'Yes'
Else 'No'
End As ValuenameFound,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblRegistry On tblRegistry.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where tblState.Statename = 'Active' And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName,
tblRegistry.Regkey,
tblRegistry.Valuename

[RCorbeil · Accepted solution]

SELECT
  ...
FROM
  ...
    Left Join tblRegistry On tblRegistry.AssetID = tblAssets.AssetID
      AND tblRegistry.RegKey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion\Public-Opstate\'
      AND tblRegistry.ValueName = 'LatestVirusDefsDate'
  ...

That should eliminate the need for DISTINCT.