Duplicate Assests Displayed in Report

[mrfite]

Good Day,

I have created the query below to find all members of the Local Administrators group on all Scanned Windows Systems (excluding Domain admins).

The issue with this report is that it duplicates all assests and renders the report useless.

What am I missing?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,

  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblUsersInGroup.Username,
  tblUsersInGroup.Groupname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblAssetCustom.AssetID
    And tblAssets1.AssetID = tblUsersInGroup.AssetID And tblAssets1.AssetID =
    tblUsers.AssetID And tsysAssetTypes.AssetType = tblAssets1.Assettype
Where tblAssets.Lastseen > GetDate() - 120 And tblUsersInGroup.Username <>
  'Domain Admins' And tblUsersInGroup.Groupname = 'Administrators' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName

Your help in this matter is appreciated.

[mrfite]

Thank you,

That was what I was missing.

[Nick_VDB · Accepted solution]

You can try using a distinct before your 'Select' statement. We added an example below in the report.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblUsersInGroup.Username,
  tblUsersInGroup.Groupname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblAssetCustom.AssetID
    And tblAssets1.AssetID = tblUsersInGroup.AssetID And tblAssets1.AssetID =
    tblUsers.AssetID And tsysAssetTypes.AssetType = tblAssets1.Assettype
Where tblAssets.Lastseen > GetDate() - 120 And tblUsersInGroup.Username <>
  'Domain Admins' And tblUsersInGroup.Groupname = 'Administrators' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName