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 Report on clients with no LSAgent
by  RobertB   Go to last post Go to first unread
Last post: Today at 6:55:32 PM(UTC)
Lansweeper Report for one asset - a Router - need 5 fields only in report
by  milty  
Go to last post Go to first unread
Last post: Yesterday at 4:51:48 PM(UTC)
Lansweeper Lansweeper report to match computer name
by  RC62N   Go to last post Go to first unread
Last post: 2/19/2020 5:02:27 PM(UTC)
Lansweeper Distinct user logins per PC
by  Bruce Garoutte   Go to last post Go to first unread
Last post: 2/17/2020 6:36:29 PM(UTC)
Lansweeper Software version report issue
by  RC62N  
Go to last post Go to first unread
Last post: 2/17/2020 4:07:20 PM(UTC)
Lansweeper Patch Tuesday - Exclude Win 2008 & Win7
by  doone128   Go to last post Go to first unread
Last post: 2/17/2020 1:34:36 PM(UTC)
Lansweeper Performance statistics combined into one report
by  djs1789  
Go to last post Go to first unread
Last post: 2/15/2020 1:50:32 AM(UTC)