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

  • The issue is the start and end date and time.  You are pulling data from the start of business on Monday until the end of business on Friday.  I'm not good enough with SQL to help you fix it.  I set up individual reports for each day during business hours since I couldn't get it to work like I was wanting.  I'm sorry I couldn't be of more help.

  • Right.. that was I am thinking before but this is a tedious task specially if you don't have enough manpower to do this emoticons_sad.png

  • 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

  • I have use the report writer logic to make weekday business hours reports:

    I filter the results like this:

    Select Records where all of the following apply:

    Records where time of the day (24 hour format) is greater than or equal to 05:00

    Records where time of the day (24 hour format) is less than or equal to 16:59

    Records where Day of the Week is greater than or equal to Monday

    Records where Day of the Week is less than or equal to Friday

    I also threw in though I don't think it's necessary:

    Records where Day of the Week is not equal to Saturday

    Records where Day of the Week is not equal to Sunday

    This may not look pretty like ur SQL but works.

  • Need to create WAN utilization report for business hours only.

    Please provide suggestions

  • Thank you !! let me try and come back