Need assistance creating a report Asset-Relations

[Chris1]

Hi,

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

Many Thanks!

[SUndie]

Grammatic pedantry, but
"tsysAssetRelationTypes.Name As RelationType"
should be
"tsysAssetRelationTypes.ReverseName As RelationType"

Name -> ReverseName

in the User Relationship query

[Chris1]

Perfect! Many Thanks!

[MikeMc]

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]

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]

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]

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]

Worked like a charm!!

Many Thanks!!

[MikeMc]

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