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