Need some help for create Relations report.

[khulster]

Hi all.
In lansweeper i have list of domain comp with uniq name. Also, i have a list of all my monitors with uniq name in Asset monitors. All monitors have relation to comp.
How i can create report for recive this informatin:

Computer name -> Names of all monitors connected to this computers.

Thx.

[RCorbeil · Accepted solution]

It looks like you'll need to link through tblAssetRelations.

tblAssets is your source. Filter it for just the computers.
tblAssetRelations links assets to assets. Parent is the computer, child is the not-computer (monitor, in this case).
tblAssets1 is the child assets. (Call it what you like. tblAssets1 is just the report builder's default name.)

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.AssetUnique,
  tblAssets.Description,
  tblAssets1.AssetName As AssetName1,
  tblAssets1.Description As Description1
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblAssetRelations ON tblAssets.AssetID = tblAssetRelations.ParentAssetID
  INNER JOIN tblAssets tblAssets1 ON tblAssets1.AssetID = tblAssetRelations.ChildAssetID
WHERE
  tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1             --Windows
  AND tblAssets1.AssetUnique LIKE 'Mon:%'  --child asset is a monitor

Be aware that the report builder will make a snarly mess of relations when you add the second tblAssets. You'll need to manually clean that up to create the linkages you need.