Switch/Routers Ports Charts

View Data in Multiple Switch Charts

Getting an overview of the ports and other data of your Switches or Routers is key to do any future management or resource planning. To help with this we’ve created the following charts which you can find below. All of these charts are part of a switch dashboard use case of which you can read more in the Pro Tips #4 blog post.

• Operational Status Switch Ports
• Admin Status Switch Ports
• Port Speed
• Ports Used by Assets

Chart: Operational Status Ports Query

Chart: Admin Status Ports Query

Select Top 1000000 Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
When tblSNMPInfo.IfAdminstatus = 3 Then 'Testing'
Else 'Other'
End As 'Admin Status',
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
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 Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
When tblSNMPInfo.IfAdminstatus = 3 Then 'Testing'
Else 'Other'
End

Chart: Port Speed Query

Select Top 1000000 
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
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 Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
group by Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000)

Select Top 1000000 
case when tblSNMPAssetMac.AssetMacAddress IS NULL then 'No' else 'Yes' end as [Port Used],
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
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 Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router')
group by case when tblSNMPAssetMac.AssetMacAddress IS NULL then 'No' else 'Yes' end

Select Top 1000000 Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As ' Operational Status',
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
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 Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End