Windows OS code report returns duplicates

[Sensitech]

I copied the Windows OS code report from this site some time back, and use it often because of the Windows 10 versions BS. However, the report returns some duplicates, with the data being the same in all the data fields displayed.

can anyone identify the reason in the report code?

Select Top 1000000 tblAssets.AssetName,
tsysOS.OSname,
tsysOS.Image As icon,
tblAssets.OScode,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Username,
tblAssetCustom.Warrantydate,
tblAssetCustom.Model,
tblADusers.email
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssetCustom.State = 1
Order By tsysOS.OSname

[RCorbeil]

Did you correct the JOIN on tblADUsers, so that it's joined on both domain and username? If not, try that first.

If you're curious and you haven't corrected the JOIN yet, try adding tblAssets.UserDomain and tblADUsers.UserDomain to your report to verify that you've got the same user ID associated with multiple domains in tblADUsers.

If you have corrected the JOIN and you're still getting duplicate results, I'd definitely look at adding the UserDomain fields to the report to see what's being retrieved. If there are legitimate duplicate entries in tblADUsers, you may need to add a filter to include or exclude specific UserDomain values.

[Sensitech]

OK RC, the email address field is definitely involved, so great catch there. Joining both name and email address removed the duplicates.

When I look at the duplicate data, there is a difference in the email address field for the two duplicate records; in other words, its the same user, but the email address is "different". SOME of these I can explain. However, in some instances this doesn't make sense and I think it is a data reporting or collecting issue.

User Carole has one AD account and uses one PC. Her PC is listed twice. One record has her email address, and the second record has a blank email address field.

In any case, we aren't making critical business decisions with this data, so I'll leave the recommended join in place, which removes the dupes.

[RCorbeil]

Never mind on the IP location. There's only one value stored in tblAssets so it should only match one entry in tsysIPLocations. I was thinking of when we had to compare tblAssets.IPNumeric to StartIP and EndIP to see whether the IP address matched a location.

That leaves the tblADUsers connection. If the same name is in two domains and each of those has a different email address, that could explain the duplication and Distinct not working for you.

[RCorbeil]

First, a correction: the primary key of tblADUsers is both UserDomain and UserName. You probably won't have the same username value in multiple domains, but strictly speaking you should join on both values, not just the name.

FROM
  ...
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
                       AND tblADUsers.Userdomain = tblAssets.Userdomain

To the duplication: do you happen to have overlapping ranges defined in your IP Locations? If so, a computer would show up twice in your results if its IP falls in both ranges. Using Distinct wouldn't filter that out since the descriptions of the IP ranges would differ.

[Sensitech]

sadly, adding the Distinct statement did not resolve the issue.

[Esben_D]

I don't see any issues with the report that would explain it. Easy fix is always to use the distinct statement: https://www.w3schools.com/sql/sql_distinct.asp