→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tschaller
Engaged Sweeper
Hey 🙂

I'm currently stuck at creating a report to list all assets which are not owned by a user or ad-group.
We have a storage room for such kinds of devices, but I want to be able to crosscheck it simply by checking it in lansweeper too.

I created a report listing all devices with their relation, but can't quite figure out how to hide the assets which are currently owned by a person.

I also see that some assets are duplicated because of the user-asset relationship history which makes tracking a bit more difficult.

Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssets.AssetName,
tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
tsysAssetRelationTypes.Name As RelationType,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
Else '/user.aspx?username=' + tblAssetUserRelations.Username +
'&userdomain=' + tblAssetUserRelations.Userdomain
End As hyperlink_RelationUser,
tblADusers.email,
tblAssetUserRelations.Comments As RelationComments,
tsysIPLocations.IPLocation,
tblAssetUserRelations.StartDate As RelationStartDate,
tblAssetUserRelations.EndDate As RelationEndDate,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
When tblADusers.Displayname Is Null Or
tblADusers.Displayname = '' Then tblAssetUserRelations.Userdomain + '\' +
tblAssetUserRelations.Username
Else tblADusers.Displayname
End As hyperlink_name_RelationUser,
Case
When tblAssetUserRelations.EndDate < GetDate() Then '#dddddd'
Else '#ffffff'
End As backgroundcolor
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Order By Case
When tblAssetUserRelations.EndDate Is Null Then 1
Else 0
End Desc,
RelationEndDate Desc,
RelationStartDate Desc,
tblAssets.AssetName,
tblAssets.Domain
0 REPLIES 0