→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
khulster
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
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.