Reports Showing the same Asset multiple times

[Michaelzip0]

I have multiple reports that are showing me duplicate Workstations however they are the same one in the database when I click on either of the machines they are the same one exactly I have a duplicate report and these workstations don't show on that list. 

Below is what is built:

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Case
When tsysOS.osname Is Not Null Then tsysOS.osname
When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
When tblMacOSInfo.SystemVersion Is Not Null Then tblMacOSInfo.SystemVersion
When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion)
Else ''
End As OS,
tblAssets.SP,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot,
tblAssets.Uptime,
tblADComputers.OU
From tblAssets
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tsysAssetTypes.AssetTypename = 'Windows' And
tsysIPLocations.IPLocation Not Like '%Servers%' And Case
When tsysOS.osname Is Not Null Then tsysOS.osname
When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
When tblMacOSInfo.SystemVersion Is Not Null Then tblMacOSInfo.SystemVersion
When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion)
Else ''
End = 'Win 10' And tblAssets.Uptime Is Not Null And tblAssets.Uptime > 604800
And tblADComputers.OU Like '%IT Office%' And tblState.Statename = 'Active'
Order By tblAssets.Uptime Desc,
tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName

[rom]

Sorry to disagree, but select DISTINCT is a band-aid to an incorrect query, as there's a reason why duplicates are returned.  I can't recreate your query currently to fix it, but when dupes happen, its usually a join to a table that has multiple entries for what you are joining on (i.e. a 'one to many' relationship).  In this case, I comment out the fields and joins of the other tables, and start with just the primary table, and then uncomment one at a time until the dupe happens.  then, i select * from that table and see why there are multiple entries for an assetID or otherwise a key that you are joining on.  once you find the table and see the data, you can choose to select the latest/top record of that table, or rank it, etc.    This may be way too much work for what you are currently tasked with accomplishing -  but this is the way I recommend to help in future queries.

EDIT:  To do this kinda thing quickly and easier - use SQL Server Management Studio (SSMS) to mess with it, and then paste it into a report in the GUI.

[Obi_1_Cinobi]

Hello there!

No disagreement here. We fully realize that there is a better way to accomplish this when you are well-versed in SQL, but sometimes a band-aid will do.

[Obi_1_Cinobi · Accepted solution]

Hello there!

If the report returns exact duplicates, the DISTINCT keyword would be a solution: https://www.w3schools.com/sql/sql_distinct.asp