Custom Scanning Reports with Registry and Files values

[apap]

Close but my newbie sql is not quite there ....

I am trying to create a report that pulls from the Custom Scanning Tables, for both the files and registry information.

Criteria is that a registry key is not found ( tblRegistry ) and a file is installed on the server as noted in the tblFileVersions table.
tblRegistry.Valuename = 'Permission Required' & tblFileVersions.FilePathfull = 'C:\Windows\dwrcs\dwrcs.exe' is found

I believe my report is giving me the correct server output for this criteria but I get each server listed 2 or 3 times in my report and I have not been able to clean this up.
Any help appreciated.

What I have thus far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysIPLocations.IPLocation,
tblADusers.Displayname,
tblAssets.IPAddress,
tblAssets.Lastseen

From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetID Not In (Select tblRegistry.AssetID From tblRegistry
Where tblRegistry.Valuename = 'Permission Required')
And tblAssetCustom.State = 1 And (tblFileVersions.FilePathfull =
'C:\Windows\dwrcs\dwrcs.exe' And tblFileVersions.Found = 1)
Order By tblAssets.AssetID

[Susan_A · Accepted solution]

TblRegistry is joined to your main query, so you're seeing a line for every record in tblRegistry. Removing the table from the main query should remove some of the "duplicates". TblADusers is linked incorrectly to tblAssets as well. It should be linked on both the Username and Userdomain fields.

With the changes above, you will still see multiple lines for assets in multiple IP locations, one for each IP location.