Please help me write a report

[Triaitsupport]

Hello team,

I have just purchased Lansweeper and thus far, i love its capabilities. I would like to run a report altho I am finding it to be quite difficult as I am not overly familiar with SQL yet.

What Im looking for is a report that will list

All Assets by host name | Owners of asset (AD Name)| Manufacture of asset | Model Number |The Svc tag or serial #|



What I've done thus far is but im returning no results. Please help me. Thank you for your time!

Edit: reworked syntax. Looks like TBLasset.assetname is displaying last seen user not owner. on tblAssetUserRelations I did the link from type to tsysAssetRelationType.RelationTypeID but im not getting Owner in report.

Select Top 1000000 tblAssets.AssetName,
tblADusers.Name,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate,
tblAssets.Mac
From tblAssets
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID And
tblADusers.Username = tblAssetUserRelations.Username And
tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Left Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID And tblAssetUserRelations.Username =
tsysAssetRelationTypes.ReverseName
Full Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID And
tblAssets.AssetID = tblAssetCustom.AssetID
Where tblADusers.Name != 'NULL'
Order By tblADusers.Name

[Daniel_B · Accepted solution]

SQL is quite easy to understand, but certainly you need some time to get into it. We used a default SQL editor as report editor as this provides the most powerful solution and many companies have somebody who knows SQL.

In your case you need to join table tblAssetUserRelations through the AssetID to tblAssets (as you already did) and then join tblAssetUserRelations to tblADusers through columns username and userdomain. All other joins can be removed. Please find the modified report below which only considers "Owned by" relations (tblAssetUserRelations.Type = 1).

Select Top 1000000 tblAssets.AssetName,
  tblAssets.AssetID,
  tblADusers.Name As Owner,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate,
  tblAssets.Mac
From tblAssets
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwnerRelations On tblAssets.AssetID =
    tOwnerRelations.AssetID
  Left Join tblADusers On tblADusers.Username = tOwnerRelations.Username And
    tblADusers.Userdomain = tOwnerRelations.Userdomain
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID And
    tblAssets.AssetID = tblAssetCustom.AssetID
Order By Owner