→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Arber
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Bruce_B
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now