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