Report for unused ports in specific buildings

adransfield

I am trying to modify the "List of Unused Ports" report and create a very similar report that can combine switches from the same building. I want to be able to see how many unused/used ports there are in each building as opposed to grouping those switches together on my own. I am not very well versed in SQL and I tried to modify this report on my own but failed. If anyone could help or at least point me in the right direction I would appreciate it.

SELECT    SUBSTRING([N].[Caption], 1, 3) 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 dbo.Nodes AS NINNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))RIGHT JOIN (    SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount    FROM dbo.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 dbo.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 [Caption] ASC, [UnusedPorts] DESC, [ActivePortCount] DESC, [% PortsUsed] ASC

lynchnigel

HI

There has been many incarnations of this and this being one of them I use. Created as an Advanced SQL/SWQL query report, if you look below where it says group By that will be what you are looking for.

SELECT

    [N].[Caption] AS Caption,

   [N].[Vendor] AS Vendor

    , 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 dbo.Nodes AS N

INNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))

RIGHT JOIN (

    SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount

    FROM dbo.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 dbo.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