cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dijs06
Engaged Sweeper II
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!

1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
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