UDT Ports - Switchport Up and Trunk Type

Version 1

    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