Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper II
I am trying for a while now to get this to work, but still getting dublicates.
How can i stop getting duplicates in the report?

Thanks in advance.

Select Top 1000000 As [Owner (Company)],
htblusers.department As [Owner (Department)], As [Owner (User)],
tblAssetUserRelations.Username As [Owner (AD Account ID)],
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetName As [Asset name],
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model As Model,
tblAssetCustom.Serialnumber As [Serial number],
tblAssetCustom.Comments As [Asset comments],
tblADusers.Displayname As [Last logon (User)],
tblADusers.Username As [Last logon (AD Account ID)],
tblADusers.EmployeeNumber As [Last logon (Employee number)],
tblADusers.Company As [Last logon (Company)],
tblADusers.Department As [Last logon (Department)],
From tblAssets
Left 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 =
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tblAssetUserRelations On tblAssetUserRelations.AssetID =
Inner Join htblusers On htblusers.username = tblAssetUserRelations.Username
And htblusers.userdomain = tblAssetUserRelations.Userdomain
Inner Join (Select tblAdProperty.AdObjectId,
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
Where tsysAdPropertyType.Name = 'ExtensionAttribute7') extension7 On
extension7.AdObjectId = tblADusers.ADObjectID
Where (tblAssetCustom.Manufacturer Is Null Or tblAssetCustom.Manufacturer = ''
Or (tblAssetCustom.Manufacturer Not Like '%VMWare%' And
tblAssetCustom.Manufacturer Not Like '%Xen%')) And
(tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
(tblAssetCustom.Model Not Like '%Virtual%' And tblAssetCustom.Model Not Like
'%PowerEdge%' And tblAssetCustom.Model Not Like '%Proliant%')) And
(tblState.Statename = 'Active' Or tblState.Statename = 'Non-active' Or
tblState.Statename = 'Non-active (checked)' Or tblState.Statename =
'Non-active (checked - scan errors/active)') And
tsysAssetTypes.AssetTypename In ('Windows', 'Windows CE') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> ''
Order By [Owner (Company)],
[Owner (Department)],
[Owner (User)],
[Asset name]