This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Convert BPS Output to Percentage of Interface Bandwidth

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

  • 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

  • Hi Haydanb,

    Thanks mate for sharing the SQL Query. I am totally new to the SQL query but I have a similar requirement wherein I want to convert the avg and peak bps utilization in avg % and peak% (separate for In and Out, not a combine result).

    Please suggest if you can share some relevant query. I want an output in below form.

    pastedImage_0.png