1 Reply Latest reply on Oct 6, 2015 9:51 PM by haydanb

    Convert BPS Output to Percentage of Interface Bandwidth

    haydanb

      Hi there,

       

      I'm using a slightly modified 95th percentile utilization rate report on specific interfaces.  The data output for the Interface is give in bps.  Is there a way to have this number converted to a percentage of the interface bandwidth as specified in solar.

       

      For example, one of my interfaces shows a receive bps of 606Mbps.  The Interface bandwidth is 1000Mbps.  Is there a way to use the SQL code to convert this and generate a percentage of 60.6% ?

       

      Here's the code

       

      =====================================

       

      SET NOCOUNT OFF

      SET ROWCOUNT 0

       

      DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

       

      SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

      SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

       

      SELECT    Interfaces.InterfaceId,

              Nodes.NodeID,

              Nodes.Caption AS NodeName,

              Nodes.VendorIcon AS Vendor_Icon,

              Interfaces.Caption AS Interface_Caption,

              Interfaces.InterfaceIcon AS Interface_Icon,

              Maxbps_In95,

              Maxbps_Out95,

              Maxbps_95

      FROM Nodes

      INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

      INNER JOIN (

          SELECT    InterfaceID,

                  dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,

                  dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,

                  dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95

          FROM InterfaceTraffic

          WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate AND InterfaceID in (34567,29355,30320)

          GROUP BY InterfaceID

      ) TrafficStat

      ON Interfaces.InterfaceID = TrafficStat.InterfaceID

      WHERE ${LIMITATION}

      ORDER BY NodeName, Interface_Caption

        • Re: Convert BPS Output to Percentage of Interface Bandwidth
          haydanb

          My DBA's figured it out... here is the correct code.

           

          =====================================

           

          SET NOCOUNT OFF

          SET ROWCOUNT 0

           

          DECLARE @StartDate DateTime

          DECLARE @EndDate DateTime

           

          SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

          SET @EndDate   = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

           

          SELECT Interfaces.InterfaceId            AS InterfaceID,

                        Nodes.NodeID                      AS NodeID,                       

                        Nodes.Caption                     AS NodeName, 

                        Interfaces.Caption                AS Interface_Caption,

                        Interfaces.InBandwidth            AS Interface_Bandwidth,

                       

                        -- RAW NUMBERS

                        Maxbps_In95 / 1000000.0           AS Maxbps_In95,

                        Maxbps_Out95 / 1000000.0   AS Maxbps_Out95,

                        Maxbps_95 / 1000000.0             AS Maxbps_95,

           

                        -- PERCENT CALCULATION

                        Maxbps_In95 / Interfaces.InBandwidth  * 100     AS ReceivePCT,

                        Maxbps_Out95 / Interfaces.InBandwidth * 100     AS TransmitPCT,

                        Maxbps_95 / Interfaces.InBandwidth     * 100     AS MaxPCT

                        

          FROM Nodes

          INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

          INNER JOIN (

                 SELECT InterfaceID,

                               dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,

                               dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,

                               dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95

                 FROM InterfaceTraffic

                 WHERE

                               InterfaceTraffic.DateTime >= @StartDate

                               AND InterfaceTraffic.DateTime <= @EndDate

                               AND InterfaceID in (30167,

                                     34395,

                                     33765,

                                     17950

                                     )

                 GROUP BY InterfaceID

          ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

          WHERE

                 (1=1)

          ORDER BY

                 NodeName,

                 Interface_Caption