cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
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

2 REPLIES 2
marda
Engaged Sweeper II
How can the second report be modified to include all assets?
Nick_VDB
Champion Sweeper III
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