AssetRelation and UserRelation in one Report

[Arber]

Hello, I'm looking for a simple Report to see which headsets are used in a specific OU that shows this:

AssetName
Relation: Type
Relation: Asset/User
OU (of "Asset/User")
Statename (of "Asset/User")


Here are some examples:

Assetrelation

Headset0001
Used By
Computer123
OU=***********, OU=Computer, DC=********, DC=local
Active


Userrelation

Headset0002
Owned By
employee001
OU=***********, OU=Computer, DC=********, DC=local
Active


My problem is that i cant see user relations. My Code only shows me asset relations.

[Bruce_B · Accepted solution]

The report below will show the user and asset relations to your headset assets in one single report. We made this report under the assumption that the computers are the parent asset, while the headsets are the child asset in the asset relation. If the name of your headset asset type differs from our filter, you will need to adjust the highlighted text

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName As [Child Asset],
  tsysAssetTypes.AssetTypename,
  tsysAssetRelationTypes.Name As [Asset relation type],
  tblAssets1.AssetName As [Parent Asset],
  tblADComputers.OU As [Parent Asset OU],
  tsysAssetRelationTypes1.Name As [User relation type],
  tblAssetUserRelations.Username,
  tblADusers.OU As [User OU]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations
    On tblAssets.AssetID = tblAssetRelations.ChildAssetID
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ParentAssetID
  Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
    tsysAssetRelationTypes.RelationTypeID
  Inner Join tblAssetUserRelations On tblAssets.AssetID =
    tblAssetUserRelations.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
    And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
  Inner Join tsysAssetRelationTypes tsysAssetRelationTypes1
    On tblAssetUserRelations.Type = tsysAssetRelationTypes1.RelationTypeID
  Inner Join tblADComputers On tblAssets1.AssetID = tblADComputers.AssetID
Where tsysAssetTypes.AssetTypename = 'Headset'
Order By [Child Asset],
  tblAssetUserRelations.Username