Notification

Icon
Error

Dublicate entries (multiple lines with assetname)

Posted: Wednesday, January 22, 2020 10:10:28 AM(UTC)
wkorrubel

wkorrubel

Member Original PosterPosts: 1
0
Like
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 htblusers.company As [Owner (Company)],
htblusers.department As [Owner (Department)],
htblusers.name As [Owner (User)],
tblAssetUserRelations.Username As [Owner (AD Account ID)],
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetName As [Asset name],
Case
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)],
extension7.Value
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 =
tblAssets.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 =
tblAssets.AssetID
Inner Join htblusers On htblusers.username = tblAssetUserRelations.Username
And htblusers.userdomain = tblAssetUserRelations.Userdomain
Inner Join (Select tblAdProperty.AdObjectId,
tblAdProperty.Value
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.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]
wkorrubel attached the following image(s):
tempsnip.png

Active Discussions

Lansweeper Changes in the licensing model
by  BullGates   Go to last post Go to first unread
Last post: Yesterday at 7:30:32 PM(UTC)
Lansweeper About Memory Changes
by  fjca  
Go to last post Go to first unread
Last post: Yesterday at 5:51:17 PM(UTC)
Lansweeper Wildcard in file scanning to get all files in a directory?
by  RKCar   Go to last post Go to first unread
Last post: Yesterday at 4:03:43 PM(UTC)
Lansweeper Wishlist is dead, no responses from LS staff.
by  RobertB  
Go to last post Go to first unread
Last post: Yesterday at 3:08:12 PM(UTC)
Lansweeper Deploy installer packages not working any more
by  richv   Go to last post Go to first unread
Last post: Yesterday at 3:06:19 PM(UTC)
Lansweeper File Scanning
by  RKCar  
Go to last post Go to first unread
Last post: Yesterday at 2:22:54 PM(UTC)
Lansweeper HP Warranty scan - broken for some products
by  zaty   Go to last post Go to first unread
Last post: Yesterday at 1:52:38 PM(UTC)
Lansweeper How to stop deployments ?
by  Leandro Martins  
Go to last post Go to first unread
Last post: Yesterday at 1:47:22 PM(UTC)