This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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
  • 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

Reply Children
No Data