Computers Without User Relations

[y_aure]

Hi,

I would like to create a report that displays every Windows Computer without user relation please.

Could anyone help me, please ?

Regards.

[y_aure]

Thank you, it works.
My knowledge in SQL language is basic for now.
I am still learning but in the meantime I need to make a few things work.

[Esben_D · Accepted solution]

The table Lansweeper stores user relations in is the tblAssetUserRelations. We created the report by using the default SQL in the Lansweeper report builder and adding this table. Adding the AsserUserRelations.RelationID field to the report with the criteria IS NULL and the criteria ='Windows' for AssetTypename.
Lastly we selected all rows from tblAssets by right clicking the line connecting tblAssets and tblAssetUserRelation and selecting 'Select all rows from tblAssets'.

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: https://www.w3schools.com/sql/
  
• 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: https://www.lansweeper.com/Forum/yaf_postst9870_Lansweeper-database-dictionary.aspx#post38296

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 tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1 And
  tblAssetUserRelations.RelationID Is Null