Need Report for Posible Hub

[pharoz]

Hello,

I need a report that will only show interfaces on switchports from asset type "routers" and "switches" with multiple assets. This is to quickly check for possible hubs at those locations.

It would ideally have the following column:

1. Device Name
2. Interface Name\Description
3. Assets Associated to the device

Thanks.

[Hemoco · Accepted solution]

If a port has more than X number of assets connected to it, we will no longer display the (MAC addresses of the) assets, but will simply mark the port as an "uplink". The one exception is if the connected asset is a VMware host, in which case we will list all guests regardless of how many there are.

We modified our report to not only list ports with more than one connected asset, but "uplinks" as well.

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex,
tblSNMPInfo.ifName,
tblSNMPInfo.IfDescription,
tblSNMPAssetMac.AssetMacAddress,
tblAssets1.AssetName As ConnectedAssetName,
tblAssets1.Domain As ConnectedAssetDomain,
tblAssets1.IPAddress As ConnectedAssetIP,
tblSNMPInfo.Uplink
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetMacAddress.AssetID
Left Join (Select Distinct Top 1000000 tblSNMPAssetMac.AssetID,
tblSNMPAssetMac.IfIndex,
Count(tblSNMPAssetMac.SNMPMacID) As Count
From tblSNMPAssetMac
Group By tblSNMPAssetMac.AssetID,
tblSNMPAssetMac.IfIndex) SubQuery1 On SubQuery1.AssetID =
tblSNMPAssetMac.AssetID And SubQuery1.IfIndex = tblSNMPAssetMac.IfIndex
Where ((tsysAssetTypes.AssetTypename = 'switch') Or
(tsysAssetTypes.AssetTypename = 'router')) And (tblSNMPInfo.Uplink = 'true'
Or SubQuery1.Count > 1)
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex

[Carl527]

I'm running this report looking for ports with multiple MAC addresses. The report doesn't show what I expect. From a particular switch, I have a port with about 10 MAC addresses listed but the report doesn't even list this port. The switch is listed and other ports but not the one with multiple MAC addresses. When I open the details of the switch in Lansweeper, in the Asset column lists "Uplink" and not MAC addresses. How does Lansweeper determine Uplink? And is there a method to get this report to show these ports that is isn't. Could this be an issue with the way the switch is responding to polls?

Thanks,

[Hemoco]

Please use the modified report below instead. It only lists interfaces with more than one connected asset. We edited our previous post to correct the copy/paste issue as well.

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex,
tblSNMPInfo.ifName,
tblSNMPInfo.IfDescription,
tblSNMPAssetMac.AssetMacAddress,
tblAssets1.AssetName As ConnectedAssetName,
tblAssets1.Domain As ConnectedAssetDomain,
tblAssets1.IPAddress As ConnectedAssetIP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetMacAddress.AssetID
Inner Join (Select Distinct Top 1000000 tblSNMPAssetMac.AssetID,
tblSNMPAssetMac.IfIndex,
Count(tblSNMPAssetMac.SNMPMacID) As Count
From tblSNMPAssetMac
Group By tblSNMPAssetMac.AssetID,
tblSNMPAssetMac.IfIndex
Having Count(tblSNMPAssetMac.SNMPMacID) > 1) SubQuery1 On SubQuery1.AssetID =
tblSNMPAssetMac.AssetID And SubQuery1.IfIndex = tblSNMPAssetMac.IfIndex
Where (tsysAssetTypes.AssetTypename = 'switch') Or
(tsysAssetTypes.AssetTypename = 'router')
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex

[pharoz]

Thanks. I think you double-pasted the report. I think it's supposed to be the following:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
tblSNMPInfo.IfDescription,
tblAssets1.AssetName As [connected asset],
tblSNMPAssetMac.LastSeen
From tblAssets
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPAssetMac On tblSNMPInfo.IfIndex = tblSNMPAssetMac.IfIndex
And tblSNMPInfo.AssetID = tblSNMPAssetMac.AssetID
Inner Join tblAssets tblAssets1 On tblSNMPAssetMac.AssetMacAddress =
tblAssets1.Mac
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex


The report gives the correct columns, but it doesn't filter out only interfaces with multiple assets. It shows interfaces with ANY asset. Would that be possible? Sorry, I'm not that great with query languages...

[Hemoco]

Please use the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
tblSNMPInfo.IfDescription,
tblAssets1.AssetName As [connected asset],
tblSNMPAssetMac.LastSeen
From tblAssets
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPAssetMac On tblSNMPInfo.IfIndex = tblSNMPAssetMac.IfIndex
And tblSNMPInfo.AssetID = tblSNMPAssetMac.AssetID
Inner Join tblAssets tblAssets1 On tblSNMPAssetMac.AssetMacAddress =
tblAssets1.Mac
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex