Report to generate all asset with no Relations

[harbot]

Hi There;

I would like to request if you can guide me on how to generate a report that will list down all active assets with no No user relations yet.
Appreciate your help

[harbot]

Appreciate the help

[Daniel_B · Accepted solution]

You can do this by

• adding table tblAssetUserRelations to your report
  
• changing the join between tblAssets and tblAssetUserRelations. Set it to "Select all rows from tblAssets".
  
• use a filter criterion on column tblAssetUserRelations.RelationID in order to list assets not having a number filled in to this column (which technically means, they don't have any entry in tblAssetUserRelations).

Please find an example below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAssetUserRelations
    On tblAssets.AssetID = tblAssetUserRelations.AssetID
Where tblAssetCustom.State = 1 And Coalesce(tblAssetUserRelations.RelationID,
  0) = 0
Order By tblAssets.AssetName