Relation report with both AssetNames included

[harringg]

ParentAsset = 2960-CISCO-111-A
Relationship = Connected To
Relationship Comment = 2960-1-04-A
ChildAsset = Patch Panel 111-01-A
Custom8 (ChildAsset)
Custom9 (ChildAsset)
Custom10 (ChildAsset)

Everything works and reports the desired data, but I'd like the AssetName of the ParentAsset, in this case a Switch. I'm using the comments field to indicate which switch port I'm connected to, but would like the Switch's AssetName listed as well.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Custom9 As [PP Label],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name,
  tblAssetRelations.Comments As [Switch Port],
  tblAssetCustom.Custom8 As [Outlet Room],
  tblAssetCustom.Custom10 As [Outlet Number]
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 tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
Where tblAssets.AssetName Like '%111%' And tblAssetCustom.State = 1

Current results
AssetName Patch Panel 111-01-A
PP Label 120-W-1-A
Name Connected To
Switch Port 2960-1-04-A
Outlet Room ROOM 120
Outlet Number 120-W-1-A

Would like
AssetName Patch Panel 111-01-A
PP Label 120-W-1-A
Name Connected To
Switch Name 2960-CISCO-111-A
Switch Port 2960-1-04-A
Outlet Room ROOM 120
Outlet Number 120-W-1-A

[marda]

How can the second report be modified to include all assets?

[Nick_VDB]

The first report should give back the asset that has a relation with the initial asset. The second we added will give back your assets and when it has a connection with a switch the switch name will be given back and any network interface related information.

Instructions for adding this report to your Lansweeper installation can be found here. 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.
  
• 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.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Custom9 As [PP Label],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name,
  tblAssetRelations.Comments As [Switch Port],
  tblAssetCustom.Custom8 As [Outlet Room],
  tblAssetCustom.Custom10 As [Outlet Number],
  tblAssets1.AssetName As [Asset In Relation With]
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 tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssetRelations.ChildAssetID =
    tblAssets1.AssetID
Where tblAssets.AssetName Like '%111%' And tblAssetCustom.State = 1

Select Distinct Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  SubQuery.NetworkInterface,
  SubQuery.IPAddress,
  SubQuery.MACaddress,
  SubQuery.Switch,
  SubQuery.Port,
  SubQuery.Description,
  SubQuery.Name,
  tblAssets.Lasttried,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
    tblNetwork.Description As NetworkInterface,
    tblNetwork.IPAddress,
    tblNetwork.MACaddress,
    tblAssets1.AssetName As Switch,
    tblSNMPAssetMac.IfIndex As Port,
    tblSNMPInfo.IfDescription As Description,
    tblSNMPInfo.ifName As Name
  From tblAssetMacAddress
    Inner Join tblSNMPAssetMac On tblSNMPAssetMac.AssetMacAddress =
      tblAssetMacAddress.Mac
    Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblSNMPAssetMac.AssetID
    Inner Join tblNetwork On tblNetwork.AssetID = tblAssetMacAddress.AssetID And
      tblNetwork.MACaddress = tblSNMPAssetMac.AssetMacAddress
    Inner Join tblSNMPInfo On tblSNMPInfo.IfIndex = tblSNMPAssetMac.IfIndex And
      tblSNMPInfo.AssetID = tblSNMPAssetMac.AssetID
  Where tblNetwork.IPEnabled = 1) SubQuery On SubQuery.AssetID =
    tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName