Filter duplicate assets

[SG89]

Hi all, I've recently purchased Lansweeper and it seems pretty good.

I've created a new report which lists our company mobile assets, along with serial number etc. I have custom asset relations configured so I can use it as a "check out" and "check in" system and therefore see the check in/check out history for each device.

The issue I'm having is that each asset is showing multiple times if it has more than one relation attached

Here's the SQL

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As [Serial/IMEI No.],
  tblAssetCustom.Custom2 As [Phone No.],
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiry],
  tsysAssetRelationTypes.Name As Relationships,
  tblAssetUserRelations.Username As [User],
  tblAssetUserRelations.Comments As Comments
From tblAssets
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAssetUserRelations
    On tblAssets.AssetID = tblAssetUserRelations.AssetID
  Left Join tblAssetRelations
    On tblAssets.AssetID = tblAssetRelations.ParentAssetID And
    tblAssetUserRelations.RelationID = tblAssetRelations.RelationID
  Left Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
    tsysAssetRelationTypes.RelationTypeID
Where tsysAssetTypes.AssetTypename = 'Mobile Phone' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

I've tried SELECT DISTINCT but even the duplicate row is not distinct as the Relationships and User are different

Could anyone help?

Thanks in advance

[SG89]

Worked a treat, thanks!

[Bruce_B · Accepted solution]

If you want the report to show relation history, this report will return a row for every user relation entry. This is expected behavior within SQL as these return distinct entries. If you want to limit your report to one row per asset you could use a report that shows the last user relationship status for the asset. We've modified your report to do just that and pasted it below. Instructions for adding this report to your Lansweeper installation can be found here. It's theoretically possible to merge rows regarding the same asset using the "stuff" function in SQL, but this function does not work in all versions of SQL. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.

• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As [Serial/IMEI No.],
  tblAssetCustom.Custom2 As [Phone No.],
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiry],
  tsysAssetRelationTypes.Name As Relationships,
  tblAssetUserRelations.Username As [User],
  tblAssetUserRelations.Comments As Comments,
  tblAssetUserRelations.Lastchanged
From tblAssets
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAssetUserRelations
    On tblAssets.AssetID = tblAssetUserRelations.AssetID
  Left Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
    tsysAssetRelationTypes.RelationTypeID
  Inner Join (Select Top 1000000 tblAssetUserRelations.AssetID,
    Max(tblAssetUserRelations.Lastchanged) As Max
  From tblAssetUserRelations
  Group By tblAssetUserRelations.AssetID) SubQuery On SubQuery.AssetID =
    tblAssetUserRelations.AssetID And
    SubQuery.Max = tblAssetUserRelations.Lastchanged
Where tsysAssetTypes.AssetTypename = 'Mobile Phone' And tblAssetCustom.State = 1
Order By tblAssets.AssetName