0 Replies Latest reply on Jan 12, 2018 9:01 AM by extonjse

    Historic Port Data

    extonjse

      I am using the below script that was supplied on this form.  It works Great!  However, the script is a snap shot of the network as it stands at the time of running the script.  Is there a way to report on port usage for the past 30 days?  For example some ports on a switch are active for a limited time at night once a week.  These ports are in turn missed when the script is run for a 30 day usage report.

       

      SELECT
         UPPER ([N].[Caption]) AS Caption
          , ([N].[City])
          , ([N].[Country])
          , ([N].[Refresh])
           ,([N].[Device_type])
          , ISNULL([FEPortsTotal].[PortCount], 0) AS FEPortCount
          , ISNULL([FEPortsActive].[PortCount], 0) AS FEActivePortCount
          , (ISNULL([FEPortsTotal].[PortCount], 0) - ISNULL([FEPortsActive].[PortCount],0)) AS FEUnusedPorts
          , CASE
              WHEN ((ISNULL([FEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([FEPortsActive].[PortCount], 0) = 0)) THEN 0
              ELSE ((ISNULL([FEPortsActive].[PortCount], 0) * 100) / ISNULL([FEPortsTotal].[PortCount], 0))
          END AS [% FEPortsUsed]

          , ISNULL([GEPortsTotal].[PortCount], 0) AS GEPortCount
          , ISNULL([GEPortsActive].[PortCount], 0) AS GEActivePortCount
          , (ISNULL([GEPortsTotal].[PortCount], 0) - ISNULL([GEPortsActive].[PortCount],0)) AS GEUnusedPorts
          , CASE
              WHEN ((ISNULL([GEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([GEPortsActive].[PortCount], 0) = 0)) THEN 0
              ELSE ((ISNULL([GEPortsActive].[PortCount], 0) * 100) / ISNULL([GEPortsTotal].[PortCount], 0))
          END AS [% GEPortsUsed]

          , ISNULL([TEPortsTotal].[PortCount], 0) AS TenPortCount
          , ISNULL([TEPortsActive].[PortCount], 0) AS TenActivePortCount
          , (ISNULL([TEPortsTotal].[PortCount], 0) - ISNULL([TEPortsActive].[PortCount],0)) AS TenUnusedPorts
          , CASE
              WHEN ((ISNULL([TEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([TEPortsActive].[PortCount], 0) = 0)) THEN 0
              ELSE ((ISNULL([TEPortsActive].[PortCount], 0) * 100) / ISNULL([TEPortsTotal].[PortCount], 0))
          END AS [% TenPortsUsed]

       

      FROM dbo.Nodes AS N

       

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

       

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Fa%')
          GROUP BY [P].[NodeID]
      ) AS FEPortsTotal ON ([FEPortsTotal].[NodeID] = [NC].[NodeID])

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Fa%')
          GROUP BY [P].[NodeID], [P].[OperationalStatus]
          HAVING ([P].[OperationalStatus] = 1)
      ) AS FEPortsActive ON ([FEPortsActive].[NodeID] = [NC].[NodeID])

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Gi%')
          GROUP BY [P].[NodeID]
      ) AS GEPortsTotal ON ([GEPortsTotal].[NodeID] = [NC].[NodeID])

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Gi%')
          GROUP BY [P].[NodeID], [P].[OperationalStatus]
          HAVING ([P].[OperationalStatus] = 1)
      ) AS GEPortsActive ON ([GEPortsActive].[NodeID] = [NC].[NodeID])

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Te%')
          GROUP BY [P].[NodeID]
      ) AS TEPortsTotal ON ([TEPortsTotal].[NodeID] = [NC].[NodeID])

      LEFT JOIN (
          SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
          FROM dbo.UDT_Port AS P
          WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Te%')
          GROUP BY [P].[NodeID], [P].[OperationalStatus]
          HAVING ([P].[OperationalStatus] = 1)
      ) AS TEPortsActive ON ([TEPortsActive].[NodeID] = [NC].[NodeID])

       

      WHERE (ISNULL([FEPortsTotal].[PortCount], 0) > 0)
        OR (ISNULL([GEPortsTotal].[PortCount], 0) > 0)
        OR (ISNULL([TEPortsTotal].[PortCount], 0) > 0)

      ORDER BY Caption