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

0 RichardLetts over 8 years ago

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
Cancel
Up 0 Down

Cancel

Reply

0 RichardLetts over 8 years ago

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
Cancel
Up 0 Down

Cancel

Children

No Data