Duplicate ownerships on assets

[JaKu]

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

[Esben_D]

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