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.

WAN Bandwidth Utilization on Business hours only

I am using the SQL command below to generate weekdays report (business hour only) but to find out that it still covers the non-business hours. Please advise. Thanks.

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = '2014-12-08 07:00:00'

SET @EndDate = '2014-12-12 19:00:00'

set nocount on

create table #InterfaceIds (

      InterfaceID int NOT NULL,

      CONSTRAINT [PK_#InterfaceIds] PRIMARY KEY CLUSTERED

      (

            InterfaceID ASC

      )

)   

insert into #InterfaceIds

    SELECT DISTINCT A.InterfaceID

    FROM dbo.InterfaceTraffic A

    WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate

SELECT Interfaces.InterfaceId,

Nodes.NodeID,

Nodes.Caption AS NodeName,

Nodes.City,

 

Interfaces.Caption AS Interface_Caption,

Interfaces.InterfaceIcon AS Interface_Icon,

Interfaces.InterfaceSpeed,

ROUND(dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) / InBandwidth * 100,0) AS PercentIN,

ROUND(dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate)/ OutBandwidth * 100,0) AS PercentOUT

FROM Nodes

INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

WHERE (Interfaces.InterfaceID in (4515,4517,4215,4214,3703,3228,4498,4496,4548,4550,4633,4635,2723,2728,3222,3226,3140,3128,3135,4320,4316,4318,4590,3170,3164,3167,3020,3172,3014,4544,4546,3742,3180,4739,4741,8780) )

  AND (Interfaces.InterfaceSpeed != 0)

ORDER BY 8 DESC, 9 DESC

DROP TABLE #InterfaceIds

Parents
  • in the report I'm not seeing where temp table #interfaceids is actually being used (unless I'm reading it wrong).; it seems to be created, populated, and dropped, but never actually used.

    Here's the outline of a function to get the GetWorkdayInBps95th -- takes the start and end hours for the period under consideration.

    I leave it as an exercise to duplicate this  for Out_Maxbps, and update the sql to make use of these two new functions.

    Create FUNCTION [dbo].[usrGetWorkdayInBps95th]

    (

      @InterfaceId int,

      @StartDate DateTime,

      @EndDate DateTime,

      @StartHour int,

      @EndHour int

    )

    RETURNS real

    AS

    BEGIN

      DECLARE @ResultVar real

      SELECT @ResultVar = MAX(In_Maxbps)

      FROM (

      SELECT TOP 95 PERCENT In_Maxbps

      FROM dbo.InterfaceTraffic WITH (NOLOCK)

      WHERE InterfaceID = @InterfaceId

      AND DateTime between @StartDate AND @EndDate

      AND DATEPART(HOUR,[DateTime]) between @StartHour and @EndHour

      ORDER BY In_Maxbps ASC

      ) AS AA

      RETURN @ResultVar

    END

    [edit to prefix the function with usr so you can find the user-defined functions]

    /RjL

Reply
  • in the report I'm not seeing where temp table #interfaceids is actually being used (unless I'm reading it wrong).; it seems to be created, populated, and dropped, but never actually used.

    Here's the outline of a function to get the GetWorkdayInBps95th -- takes the start and end hours for the period under consideration.

    I leave it as an exercise to duplicate this  for Out_Maxbps, and update the sql to make use of these two new functions.

    Create FUNCTION [dbo].[usrGetWorkdayInBps95th]

    (

      @InterfaceId int,

      @StartDate DateTime,

      @EndDate DateTime,

      @StartHour int,

      @EndHour int

    )

    RETURNS real

    AS

    BEGIN

      DECLARE @ResultVar real

      SELECT @ResultVar = MAX(In_Maxbps)

      FROM (

      SELECT TOP 95 PERCENT In_Maxbps

      FROM dbo.InterfaceTraffic WITH (NOLOCK)

      WHERE InterfaceID = @InterfaceId

      AND DateTime between @StartDate AND @EndDate

      AND DATEPART(HOUR,[DateTime]) between @StartHour and @EndHour

      ORDER BY In_Maxbps ASC

      ) AS AA

      RETURN @ResultVar

    END

    [edit to prefix the function with usr so you can find the user-defined functions]

    /RjL

Children
No Data