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

I am needing some assistance creating a report to show all workstations assets with any associated relations.

Many Thanks!
8 REPLIES 8
SUndie
Engaged Sweeper II
Grammatic pedantry, but
"tsysAssetRelationTypes.Name As RelationType"
should be
"tsysAssetRelationTypes.ReverseName As RelationType"

Name -> ReverseName

in the User Relationship query
Chris1
Engaged Sweeper III
Perfect! Many Thanks!
MikeMc
Champion Sweeper II
I am assuming you are using the original asset relationship query without issue, correct? I updated that query with the username, office, and department of the last user of the parent asset.

Select Top 1000000 a1.AssetID,
a1.AssetName,
a1.Username,
tblADUsers.Office,
tblADUsers.Department,
tsysAssetRelationTypes.Name As RelationType,
a2.AssetName As ChildAssetName,
tblAssetRelations.StartDate,
tblAssetRelations.Lastchanged
From tblAssetRelations
Inner Join tblAssets a1 On a1.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets a2 On a2.AssetID = tblAssetRelations.ChildAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblADUsers On tblADUsers.Userdomain = a1.Userdomain AND tblADUsers.Username = a1.Username
Order By a1.AssetName,
ChildAssetName
Chris1
Engaged Sweeper III
My bad, I did try that when you last posted but forgot to post back that it did not function.
Getting "This report has no results!"
Chris1
Engaged Sweeper III
User came back to me and asked if she could also get the below listed added to this report? I have tried and failed..

Username (example: nryan)
Office (example: GA073)
Department (example: 218769 ICT Field Services)
MikeMc
Champion Sweeper II
Chris@BCD wrote:
User came back to me and asked if she could also get the below listed added to this report? I have tried and failed..

Username (example: nryan)
Office (example: GA073)
Department (example: 218769 ICT Field Services)

I assume you mean for the user relationship report? If so, try this query:

Select Top 1000000 tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblADusers.Displayname,
tblADUsers.Office,
tblADUsers.Department,
tsysAssetRelationTypes.Name As RelationType,
tblAssetUserRelations.AssetID,
tblAssets.AssetName,
tblAssetUserRelations.StartDate,
tblAssetUserRelations.Lastchanged
From tblAssetUserRelations
Inner Join tblADusers
On tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Order By tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Username

Chris1
Engaged Sweeper III
Worked like a charm!!

Many Thanks!!
MikeMc
Champion Sweeper II
These should get you started.

Asset Relationships:
Select Top 1000000 a1.AssetID,
a1.AssetName,
tsysAssetRelationTypes.Name As RelationType,
a2.AssetName As ChildAssetName,
tblAssetRelations.StartDate,
tblAssetRelations.Lastchanged
From tblAssetRelations
Inner Join tblAssets a1 On a1.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets a2 On a2.AssetID = tblAssetRelations.ChildAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Order By a1.AssetName,
ChildAssetName

User Relationships:
Select Top 1000000 tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblADusers.Displayname,
tsysAssetRelationTypes.Name As RelationType,
tblAssetUserRelations.AssetID,
tblAssets.AssetName,
tblAssetUserRelations.StartDate,
tblAssetUserRelations.Lastchanged
From tblAssetUserRelations
Inner Join tblADusers
On tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Order By tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Username