Switch/Router Port Status Overview

Get an Overview of Port Statuses

Getting an overview of the ports on your Switches or Routers is key to do any future management or resource planning, that's why the report below will provide you with an overview of all Ethernet ports on your switches or routers including detailed information about both the switch and the ports like the operational and admin status of a port, the MTU, speed, connected asset and more.

To help you get an overview and key metrics on your switches and routers, there are additional resources you can use to create a full dashboard dedicated to managing your switch. You can read more about the switch dashboard in the Pro Tips #4 blog post.

Switch/Router Port Status Overview Query

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblSNMPInfo.ifAlias,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
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',
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',
tblSNMPAssetMac.LastSeen As 'Last Detected',
tblSNMPAssetMac.Lastchanged As 'Last Changed',
tblAssets1.Firstseen As 'Asset First Seen',
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As SwitchportMAC,
tblSNMPAssetMac.AssetMacAddress As AssetMAC,
Case
When tblSNMPAssetMac.AssetMacAddress Is Null Then 'No MAC detected'
When tsysMacVendor.Vendor Is Null Then 'MAC detected but Vendor not found'
Else tsysMacVendor.Vendor
End As AssetVendor,
tsysAssetTypes_1.AssetTypeIcon16 As icon2,
tblAssets1.AssetName As deviceassetname,
tblAssets1.Username,
tblAssets1.Userdomain,
tblSNMPInfo.Vlan,
Case
When tblSNMPInfo.IfOperstatus = 1 And tblSNMPInfo.IfAdminstatus = 1 Then
'#d4f4be'
When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 1 Then
'#ffff00'
When tblSNMPInfo.IfOperstatus = 2 And tblSNMPInfo.IfAdminstatus = 2 Then
'#ffadad'
Else '#00ffff'
End As backgroundcolor
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 tsysMacVendor On
tsysMacVendor.MAC = SubString(tblSNMPAssetMac.AssetMacAddress, 1, 2) +
SubString(tblSNMPAssetMac.AssetMacAddress, 4, 2) +
SubString(tblSNMPAssetMac.AssetMacAddress, 7, 2)
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')
Order By tblAssets.AssetName,
[If]

Audit and Take Action in 3 Easy Steps

1. Download & Install Lansweeper

3. Run the Audit & Take Action

Download Lansweeper to Run this Audit

Harness the Power of Reporting