Some queries for getting aggregated view of port usage across Catalyst and Nexus switches.
Note: Custom Properties 'Site', 'City' and 'Network' are used in our infrastructure to help dynamically group devices.
Latest Edit: Updated to fix date selection criteria and add SUM instead of COUNT.
Change as required.
# ----- Ports used right now ----- #
SELECT n.Caption, count(p.OperationalStatus) as PortsUp
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%' OR n.MachineType LIKE '%2960%' OR n.MachineType LIKE '%3560%'
OR n.MachineType LIKE '%3650%' OR n.MachineType LIKE '%3850%' OR n.MachineType LIKE '%3750%' OR n.MachineType LIKE '%2950%' )
AND ( c.Site = 'AH' OR c.Site = 'AD' OR c.Site = 'MC' OR c.Site = 'AAOC' )
AND p.OperationalStatus = 1
GROUP BY n.Caption
# ----- Max ports used over 8 days per device ----- #
SELECT n.Caption, MAX(p.AvgPortCount) as AvgPortCount, MAX(p.AvgActivePortCount) as AvgActivePortCount
FROM Orion.Nodes as n
INNER JOIN Orion.NodesCustomProperties as c ON n.NodeID = c.NodeID
INNER JOIN Orion.UDT.PortUsage as p ON n.NodeID = p.NodeID
WHERE ( n.MachineType LIKE '%Catalyst%' OR n.MachineType LIKE '%Nexus%' OR n.MachineType LIKE '%2960%' OR n.MachineType LIKE '%3560%'
OR n.MachineType LIKE '%3650%' OR n.MachineType LIKE '%3850%' OR n.MachineType LIKE '%3750%' OR n.MachineType LIKE '%2950%' )
AND ( c.Site = 'AH' OR c.Site = 'AD' OR c.Site = 'MC' OR c.Site = 'AAOC' )
GROUP BY n.Caption
# ----- SUM of ports used from last days records, per site/network ----- #
SELECT Site, Network, TotalPortCount, TotalActivePortCount
FROM (SELECT DISTINCT DAY(p.DateTime) as DateTime, c.Site, c.Network, SUM(p.AvgPortCount) as TotalPortCount, SUM(p.AvgActivePortCount) as TotalActivePortCount
FROM Orion.Nodes as n
INNER JOIN Orion.NodesCustomProperties as c ON n.NodeID = c.NodeID
INNER JOIN Orion.UDT.PortUsage as p ON n.NodeID = p.NodeID
WHERE ( n.MachineType LIKE '%Catalyst%' OR n.MachineType LIKE '%Nexus%' OR n.MachineType LIKE '%2960%' OR n.MachineType LIKE '%3560%'
OR n.MachineType LIKE '%3650%' OR n.MachineType LIKE '%3850%' OR n.MachineType LIKE '%3750%' OR n.MachineType LIKE '%2950%' )
AND ( c.Site = 'AH' OR c.Site = 'AD' OR c.Site = 'MC' OR c.Site = 'AAOC' )
AND p.DateTime > GetUtcDate()-1
GROUP BY c.Site, c.Network)
ORDER BY Site
# ----- Debug ----- #
SELECT n.Caption, c.Site, c.Network, p.AvgPortCount, p.AvgActivePortCount
FROM Orion.Nodes as n
INNER JOIN Orion.NodesCustomProperties as c ON n.NodeID = c.NodeID
INNER JOIN Orion.UDT.PortUsage 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 n.Caption LIKE 'cc%' AND c.Site = 'AD'
