Custom report that will show all assets relation to a user.

[dijs06]

Good day everyone!

I’m trying to generate a report which will give me a list of all assets (laptop, desktop, monitors, HDDs) that is link or assigned to or Used by or Controlled by (not the Last User) the user. In this case It will be easier for us to determine and track what assets are assigned to a user. So that when that user resigned, this report will serve as a checklist of all Assets that a user must return to us before leaving.


Hope anyone can help me on this! Thank you in advance!

[Daniel_B · Accepted solution]

The easiest would be that you search for the username in the web console search bar and open the user page of the user. It will list the assets to which the user has a relation.

If you want to do it in a report, you could create a report with parameters, like the following example:

Select Top 1000000 tblADusers.Userdomain,
  tblADusers.Username,
  tsysAssetRelationTypes.Name As Relation,
  tblAssetUserRelations.StartDate As since,
  tblAssets.AssetID,
  tblAssets.AssetName
From tblADusers
  Inner Join tblAssetUserRelations On tblADusers.Username =
    tblAssetUserRelations.Username And tblADusers.Userdomain =
    tblAssetUserRelations.Userdomain
  Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
Where tblADusers.Username = @username And tsysAssetRelationTypes.Name In
  ('used by', 'controlled by')
Order By tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetName

After saving the report you will receive an error message. But you can go to the address bar of the browser and add something like the following at the end of the URL: &@username=exampleuser

[dijs06]

Thank you Daniel!

The report is basically to print or to show all assets that are "used by or controlled by" a user. The report script should be flexible enough in which I can identify the username and when I run the report it will pull out or show all assets that are attached or related to that user. I'm just not sure if this can be done..



Thanks!

[Daniel_B]

Not sure which relations exactly you would like to list. The following report lists all AD users and the assets to which they have a "Used by" or "Controlled by" relation.

Select Top 1000000 tblADusers.Userdomain,
  tblADusers.Username,
  tsysAssetRelationTypes.Name As Relation,
  tblAssetUserRelations.StartDate As since,
  tblAssets.AssetID,
  tblAssets.AssetName
From tblADusers
  Inner Join tblAssetUserRelations On tblADusers.Username =
    tblAssetUserRelations.Username And tblADusers.Userdomain =
    tblAssetUserRelations.Userdomain
  Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name In ('used by', 'controlled by')
Order By tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetName