I work as a Network Admin at a manufacturing facility, where it is difficult to schedule the time to upgrade the IOS on Cisco switches serving the manufacturing floors. In order to upgrade the IOS, that means a network outage, and that means stopping production on tools served by the switches being upgraded. I spoke with manufacturing managers, and we came up with this agreement: All I have to do is tell them exactly which tools are connected to which switches, so they can temporarily take the attached tools off line while allowing other tools to continue work. Sounds like a reasonable compromise. But then the question become, "Which devices are connected to which switch ports?" We ran a simple free utility, but it failed to obtain ARP information for subnets protected by the Fab Firewalls. So, we needed something that some see through firewalls, to resolve MAC to IP, and then connect to DNS to resolve IP to hostnames.
Sounds like a job for UDT! [invoke image superhero with UDT suit here]
So, I downloaded a trial of UDT (which ends at the end of this month). There are many features of UDT which I think would be helpful in Network Management. The one I am specifically interested in is mapping endpoint devices to switch ports. Unfortunately, I was a little disappointed that such a widget to do exactly what I wanted was not available out of the box. So, I looked in Thwack! and fond some good resources. None of the ones I found was exactly what I wanted, so I wrote my own SQL script, borrowing heavily from other people's posted UDT reports, and from the views already defined in UDT database. As one person put it, we stand on the shoulders of giants.
What this SQL scrip does: Simply, it allows you to enter a device name, and then it will show you which ports are in use, which devices are connected, along with certain port information and who was the last user logged in. The port information is interpreted from the Cisco's OIDs and presented in text form (eg. port type 161 is represented as 802.3adLag). I have left in some fields, commented out using double dashes, because they could be useful to someone who wants to further develop this SQL code for a variation. I sent to report to show only access ports, which ignores L2 pots (no switchport) and Trunked ports since Trunks give a lot of unnecessary detail about which devices were seen across the trunk ports. The line that limits to access ports is line 78. There are good breakouts of Duplex, Admin Status, and Op Status in comments. I'm not entirely sure of the Duplex settings since I could not find this in OID. However, on our switches, the numeration it seems to be correct.
SELECT DISTINCT
-- [p].[NodeID]
[Switch]=[nd].[Caption]
,[Port Name]=[p].[Name]
-- ,[p].[PortID]
,[Port Type] = CASE [p].[PortType]
--snmp.cloudapps.cisco.com/.../BrowseOID.do
WHEN 6 then 'Ethernet'
WHEN 15 then 'FDDI'
WHEN 30 then 'DS3'
WHEN 31 then 'SIP'
WHEN 56 then 'fiberChannel'
WHEN 54 then 'Trunk'
WHEN 53 then 'Virtual'
WHEN 62 then 'fastEthernet'
WHEN 81 then 'ds0'
WHEN 117 then 'gigabitEthernet'
WHEN 131 then 'Tunnel'
WHEN 135 then 'L2VLAN'
WHEN 136 then 'L3IP-VLAN(SubInt)'
WHEN 160 then 'USB'
WHEN 161 then '802.3adLag'
ELSE CONVERT (varchar,[p].[PortType])
END
-- ,[p].[Speed]
--,[Duplex] = CASE [p].[Duplex]
-- WHEN 0 then 'Full'
-- WHEN 1 then 'Auto'
-- WHEN 2 then 'Half'
-- ELSE CONVERT (varchar,[p].[Duplex])
-- END
,[SwitchportMode] = CASE [p].[TrunkMode]
WHEN 0 then 'No Switchport'
WHEN 1 then 'Trunked'
WHEN 2 then 'Access'
ELSE CONVERT (varchar,[p].[TrunkMode])
END
,[p].[PortDescription]
--,[AdmStatus] = CASE [p].[AdministrativeStatus]
-- WHEN 1 then 'Enabled'
-- WHEN 2 then 'AdminDown'
-- WHEN 3 then 'Testing'
-- ELSE CONVERT (varchar,[p].[AdministrativeStatus])
-- END
--,[OpStatus] = CASE [p].[OperationalStatus]
-- WHEN 0 then 'NoResponse'
-- WHEN 1 then 'Up'
-- WHEN 2 then 'Down'
-- WHEN 3 then 'Testing'
-- WHEN 4 then 'Unknown'
-- WHEN 5 then 'Dormant'
-- WHEN 6 then 'notPresent'
-- WHEN 7 then 'lowerLayerDown'
-- ELSE CONVERT (varchar,[p].[OperationalStatus])
-- END
,[p2ec].[VlanID]
,[v].[VlanName]
-- ,[p2ec].[EndpointID]
,[dnsc].[DNSName] AS [Host Name]
,[ipc].[IPAddress] AS [Host IP]
,[e].[MACAddress]
-- ,[ipc].[IPAddressID]
-- ,[ipc].[RouterNodeID] AS [Seen on Router]
-- ,[ipc].[RouterPortID]
-- ,[dnsc].[DNSNameID]
,[e].[Vendor]
,[ipui].[UserName] AS [User Name]
-- ,[ipui].[LoginTime] AS [Last Logged on]
FROM [dbo].[UDT_Ports][p] with(nolock)
JOIN [dbo].[Nodesdata][nd] with(nolock) ON [nd].[NodeID]=[p].[NodeID]
--JOIN [dbo].[UDT_PortToEndpoint][p2e] with(nolock) ON [p].[PortID]=[p2e].[PortID]
JOIN [dbo].[UDT_PortToEndpointCurrent][p2ec]with(nolock) ON [p].PortID=[p2ec].[PortID]
JOIN [dbo].[UDT_Endpoint][e]with(nolock) ON [p2ec].[EndpointID] = [e].[EndpointID]
LEFT JOIN [dbo].[UDT_IPAddressCurrent][ipc] with(nolock) ON [e].[EndpointID] = [ipc].[EndpointID]
LEFT JOIN [dbo].[UDT_DNSNameCurrent][dnsc] with(nolock) ON [dnsc].[IPAddressID] = [ipc].[IPAddressID]
LEFT JOIN [dbo].[UDT_VLAN][v] with(nolock) ON ([p2ec].[VlanID]=[v].[VlanID] AND [p2ec].[PortID]=[v].[PortID] )
LEFT JOIN [DBO].[UDT_IPUserInfo][ipui] with(nolock) ON [e].[EndpointID]=[ipui].EndpointID
WHERE [p].[TrunkMode] = 2
-- AND NOT [e].[Vendor]='Cisco' -- enable this line to eliminate phones from the results
-- AND [nd].[Caption] LIKE 'GSOE%'
ORDER BY [nd].[Caption], [p].[Name]
Next Steps:
- It would be nice if this could be put into a widget format, similar to All Nodes, where we could browse in the left column to a specific switch, and then select it to see the port information report in the right column. Does anyone know how to do this?
- It would be nice to show switchport operational-status/type/mode/speed/duplex in graphic format, instead of text -- I think they could add some usability to the report and reduce space needed, make it look less cluttered.