Report for not owned assets

[tschaller]

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