UDT Report for Trunk Links

kbates0430

Is there a way to create a report or an SWQL query to show all the Trunk interfaces that UDT is monitoring so I can find a quick way to unmonitor those interfaces?

While I could probably create a searchable Custom Property, I'd like to use what Solarwinds already collects, but it makes it extremely difficult to search with the canned reports. I'm not clear as to how the table relationships work for the Interfaces Table with the attributes for Port Interface Mapping.

I'm trying to make a query that will help reconcile Access ports that are NOT monitored that SHOULD BE monitored and a Trunk port that IS monitored that SHOULD NOT be. This helps keep UDT clean and therefore it does it's device tracking correctly so it won't see a Requested MAC address on more than one monitored port.

micheal100

Hey @kbates0430 ,

I just did some playing in the lab, these should get you started.

1) All UDT-monitored trunk ports (candidates to unmonitor)

This uses Orion.UDT.Port.TrunkMode (1 = trunk, 2 = not trunk) and only looks at real Ethernet ports

SELECT
n.Caption AS [Node],
p.Name AS [Port],
p.PortDescription,
p.Speed,
p.Duplex,
CASE p.TrunkMode
WHEN 1 THEN 'Trunk'
WHEN 2 THEN 'Access'
ELSE 'Unknown'
END AS [UDT Trunk Mode],
p.OperationalStatus
FROM Orion.Nodes n
JOIN Orion.UDT.Port p
ON n.NodeID = p.NodeID
WHERE
p.PortType = 6 -- ethernetCsmacd
AND p.OperationalStatus = 1 -- up (optional; drop if you want all)
AND p.TrunkMode = 1 -- trunk
AND p.Flag <> 1 -- exclude deleted/hidden ports
ORDER BY n.Caption, p.Name

Use this list as your “trunk ports UDT is monitoring” set – those are the ones you likely want to unmonitor in UDT.

2) Access ports that are NOT monitored by UDT (candidates to add)

Here we use NodePortInterfaceMap.PortType (1 = access, 2 = trunk) to identify access mode at the NPM interface level , then left-join to UDT.Port via NodeID + ifIndex/PortIndex to find access ports with no UDT entry.

SELECT
n.Caption AS [Node],
i.Caption AS [Interface],
i.InterfaceTypeName,
i.Status AS [NPM Status],
m.PortType AS [PortMode_Code],
'Access' AS [PortMode_Interpretation]
FROM Orion.NPM.Interfaces i
JOIN Orion.Nodes n
ON n.NodeID = i.NodeID
JOIN Orion.NodePortInterfaceMap m
ON m.NodeID = i.NodeID
AND m.IfIndex = i.InterfaceIndex
LEFT JOIN Orion.UDT.Port p
ON p.NodeID = i.NodeID
AND p.PortIndex = i.InterfaceIndex
AND p.Flag <> 1 -- ignore deleted/hidden UDT ports
WHERE
m.PortType = 1 -- 1 = Access mode
AND p.PortID IS NULL -- no UDT port = NOT monitored by UDT
AND i.InterfaceTypeName = 'ethernetCsmacd' -- real Ethernet; optional
ORDER BY n.Caption, i.Caption