Report for used by

[Gartski]

Hi I'm after writing a report that uses the used by option but I am unable to find this in any of the SQL tables. Some help would be appreciated

[Susan_A · Accepted solution]

I'm assuming you are referring to "used by" user relations, which are stored in the tblAssetUserRelations database table. A sample report can be seen below. You can add this report to your Lansweeper installation by following these instructions. 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, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.Name As Relation,
  tblAssetUserRelations.Username,
  tblAssetUserRelations.Userdomain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetUserRelations On tblAssets.AssetID =
    tblAssetUserRelations.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name = 'used by' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName