Hi,
My wish here is to report total monitored ports and utilisation, the ultimate would be usage in the last 30 days but for now I need to get a report out with total capacity which is all monitored ports. Someone kindly shared this script but it is looking at available ports not Ports monitored which is what my requirement is, I am new to Solarwinds and limited SWQL knowledge I think my table is PortsMonitored but am not sure how to rewrite the query to reflect this?
SELECT [N].[Caption] AS Caption , ISNULL([PortsTotal].[PortCount], 0) AS PortCount , ISNULL([PortsActive].[PortCount], 0) AS ActivePortCount , (ISNULL([PortsTotal].[PortCount], 0) - ISNULL([PortsActive].[PortCount],0)) AS UnusedPorts , CASE WHEN ((ISNULL([PortsTotal].[PortCount], 0) = 0) OR (ISNULL([PortsActive].[PortCount], 0) = 0)) THEN 0 ELSE ((ISNULL([PortsActive].[PortCount], 0) * 100) / ISNULL([PortsTotal].[PortCount], 0)) END AS [% PortsUsed] FROM orion.Nodes AS N INNER JOIN orion.UDT.NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2)) RIGHT JOIN ( SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount FROM orion.UDT.Port AS P WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) GROUP BY [P].[NodeID] ) AS PortsTotal ON ([PortsTotal].[NodeID] = [NC].[NodeID]) RIGHT JOIN ( SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount FROM orion.UDT.Port AS P WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1 ) GROUP BY [P].[NodeID], [P].[OperationalStatus] HAVING ([P].[OperationalStatus] = 1) ) AS PortsActive ON ([PortsActive].[NodeID] = [NC].[NodeID]) WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0) ORDER BY [% PortsUsed] DESC, [ActivePortCount] DESC, [UnusedPorts] ASC
Any help greatly appreciated
Steve
