How to Remove Duplicate Assets from SQL Report

[Glen]

Hello I have a query that reports on all SQL server paid/licensed editions. When I run the report I get duplicated assets on the report. Here is the following script. I also added "Distinct" hoping it will remove the duplicates for both OS and Assets. Did not work. Any help is appreciated. Thank you.

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssetCustom.Department,
tblAssets.AssetName,
tblSqlServers.displayVersion As Edition,
tblSqlServers.skuName As License,
tblSqlServers.lastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.Model,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As [virtual check]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblSqlServers.skuName Not Like '%express edition%' And
tblSqlServers.skuName Not Like '%developer edition%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
Edition

[Glen]

Thank you it worked!

[endyk]

Glen-

Most likely, the problem you are having is with the lastChanged field. With this field included in the query, you will usually have duplicate rows. Remove this field and run your query again and you should get a distinct list.

If you need the lastChanged field in your report, replace the field in the select query with this:
max(tblSqlServers.lastChanged) over (Partition by tblAssets.AssetID) as lastChanged,

This basically returns the "max" lastChanged field for each Asset.

Let me know if you need further help.

Endy