Like previous posts query makes use of Custom Properties (City, Site, Network).
Returns results for Catalyst and Nexus.
Modify as needed.
# ----- Up and Trunk ----- #
# ---- Looks for IANA port Type 6 (ethernetCsmacd) as defined in https://www.iana.org/assignments/ianaiftype-mib/ianaiftype-mib. ----- #
SELECT n.Caption, p.Name, p.PortDescription, p.Speed, p.Duplex, p.TrunkMode
FROM Orion.Nodes as n
INNER JOIN Orion.NodesCustomProperties as c ON n.NodeID = c.NodeID
INNER JOIN Orion.UDT.Port as p ON n.NodeID = p.NodeID
WHERE ( n.MachineType LIKE '%Catalyst%' OR n.MachineType LIKE '%Nexus%' )
AND ( c.Site = 'AH' OR c.Site = 'AD' OR c.Site = 'MC' OR c.Site = 'AAOC' )
AND p.PortType = 6 AND p.OperationalStatus = 1 AND p.TrunkMode = 1
# ----- Up and not Trunk ----- #
# ---- Looks for IANA port Type 6 (ethernetCsmacd) as defined in https://www.iana.org/assignments/ianaiftype-mib/ianaiftype-mib. ----- #
SELECT n.Caption, p.Name, p.PortDescription, p.Speed, p.Duplex, p.TrunkMode
FROM Orion.Nodes as n
INNER JOIN Orion.NodesCustomProperties as c ON n.NodeID = c.NodeID
INNER JOIN Orion.UDT.Port as p ON n.NodeID = p.NodeID
WHERE ( n.MachineType LIKE '%Catalyst%' OR n.MachineType LIKE '%Nexus%' )
AND ( c.Site = 'AH' OR c.Site = 'AD' OR c.Site = 'MC' OR c.Site = 'AAOC' )
AND p.PortType = 6 AND p.OperationalStatus = 1 AND p.TrunkMode = 2