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

Action PowerShell - Top 10 Memory Hogs
by  iamBaphomet   Go to last post Go to first unread
Last post: Today at 3:00:53 PM(UTC)
Lansweeper Launch TeamViewer from inside LanSweeper
by  CyberCitizen  
Go to last post Go to first unread
Last post: 2/19/2020 12:16:12 AM(UTC)
Action Email user
by  morpios   Go to last post Go to first unread
Last post: 1/26/2020 10:55:44 AM(UTC)
Action Psexec service delete
by  steveb  
Go to last post Go to first unread
Last post: 1/25/2020 12:15:01 AM(UTC)
Lansweeper Uninstall Software - Mozilla Firefox
by  Carl_Allen   Go to last post Go to first unread
Last post: 1/22/2020 10:44:36 AM(UTC)
Lansweeper Installation Parameters
by  PeterG  
Go to last post Go to first unread
Last post: 1/8/2020 10:11:51 PM(UTC)
Lansweeper embedded questions
by  mkergan   Go to last post Go to first unread
Last post: 12/20/2019 5:19:46 PM(UTC)
Lansweeper Result: Deployment ended: The environment is incorrect.
by  mkergan  
Go to last post Go to first unread
Last post: 12/19/2019 6:31:44 PM(UTC)