3 Replies Latest reply on May 14, 2018 10:50 AM by lynchnigel

    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 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 [Caption] ASC, [UnusedPorts] DESC, [ActivePortCount] DESC, [% PortsUsed] ASC
      
        • Re: Report for unused ports in specific buildings
          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