UDT Report - List all used and unused gigabit ports

Deltona

Hi!

I need a report that lists all used and unused gigabit and 10 gig ports in UDT, per node.

The following could maybe be used in conjunction with the UDT usage report.

SELECT Interfaces.Caption AS Interface_Caption, Interfaces.InterfaceSpeed AS Interface_Speed, Nodes.Caption AS Node_Name, Interfaces.InterfaceName FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)WHERE (Interfaces.InterfaceName LIKE '%Giga%') OR (Interfaces.InterfaceSpeed = '10000000000')

Thanks in advance.

michalB

Perhaps this will get you started. It's probably not the best way to achieve this, but it's at least easy to read

Select Ethernet.NodeId, Nodes.Caption, NumberOfPorts, AvailablePorts, NumberOfGbitPorts, AvailableGbitPorts, NumberOf10GbitPorts, Available10GbitPorts from

(

Select NodeID, COUNT(PortId) as NumberOfPorts from UDT_Port

group by NodeID

) Ethernet

full join

(

select NodeId, COUNT(PortId) as AvailablePorts from UDT_Port

where UDT_Port.OperationalStatus  <> 1

group by NodeID

)EthernetAvailable

on Ethernet.NodeID = EthernetAvailable.NodeID

full join

(

select NodeID, COUNT(PortId) as NumberOfGbitPorts from UDT_Port

where UDT_Port.Speed = 1000000000

group by NodeID

) GBitPorts

on Ethernet.NodeID = GBitPorts.NodeID

full join

(

select NodeID, COUNT(PortId) as AvailableGbitPorts from UDT_Port

where UDT_Port.Speed = 1000000000 and UDT_Port.OperationalStatus <> 1

group by NodeID

) GBitPortsAvailable

on Ethernet.NodeID = GBitPortsAvailable.NodeID

full join

(

select NodeID, COUNT(PortId) as NumberOf10GbitPorts from UDT_Port

where UDT_Port.Speed = 10000000000

group by NodeID

) TenGBitPorts

on Ethernet.NodeID = TenGBitPorts.NodeID

full join

(

select NodeID, COUNT(PortId) as Available10GbitPorts from UDT_Port

where UDT_Port.Speed = 10000000000 and UDT_Port.OperationalStatus <> 1

group by NodeID

) TenGBitPortsAvailable

on Ethernet.NodeID = TenGBitPortsAvailable.NodeID

join Nodes on Nodes.NodeID = Ethernet.NodeID