→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JaKu
Engaged Sweeper
Hi,

We have report that lists all our assets and their owners and assets without owner. But we would need a report that lists only users who has ownership on two or more asset, Any ideas how it's done?

Select Top 1000000 tblAssets.AssetName,
tblAssets.AssetID,
tblADusers.Name As Owner,
tblADusers.Company,
tblADusers.Department As Unit,
tblADusers.Description As Team,
tblADusers.EmployeeID As CostCenter,
tblADusers.City,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen
From tblAssets
Left Join (Select tblAssetUserRelations.AssetID,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain
From tblAssetUserRelations
Where tblAssetUserRelations.Type = 1) tOwnerRelations On tblAssets.AssetID =
tOwnerRelations.AssetID
Left Join tblADusers On tblADusers.Username = tOwnerRelations.Username And
tblADusers.Userdomain = tOwnerRelations.Userdomain
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID And
tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.Model Not Like '%Virtual%' And
tblAssets.Assettype Like '-1' And tblAssetCustom.State = 1
Order By Owner
1 REPLY 1
Esben_D
Lansweeper Employee
Lansweeper Employee
I used the built-in asset to user relation report and edited it to only display users with 2 or more "Owned by" relations. The count is done on tblAssetUserRelations.RelationID. Hopefully this helps.

Select Top 1000000 tblAssetUserRelations.Username As RelationUser,
Count(tblAssetUserRelations.RelationID) As RelationCount
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Where tblState.Statename = 'Active' And tsysAssetRelationTypes.Name = 'Owned By'
Group By tblAssetUserRelations.Username,
tsysAssetRelationTypes.Name
Having Count(tblAssetUserRelations.RelationID) > 1
Order By RelationCount Desc