DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @StartHour INT
DECLARE @EndHour INT
SET @StartDate = '2018-03-01'
SET @EndDate = '2018-04-01'
SET @StartHour = 6
SET @EndHour = 17
-- Putting @EndHour = 17 will include 17:59 or 5:59 PM
SELECT @StartDate AS Start_Date,
@EndDate AS End_Date,
Interfaces.InterfaceId,
Nodes.Site_ID,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Maxbps_In90,
Maxbps_Out90,
Maxbps_In95,
Maxbps_Out95,
Maxbps_In99,
Maxbps_Out99
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
SELECT InterfaceID,
(SELECT MAX(In_Averagebps) AS Maxbps_In90 from (SELECT TOP 90 PERCENT In_Averagebps
FROM InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY In_Averagebps ASC
) AS x ) AS Maxbps_In90,
(SELECT MAX(Out_Averagebps) AS Maxbps_Out90 from (SELECT TOP 90 PERCENT Out_Averagebps
FROM InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY Out_Averagebps ASC
) AS x ) AS Maxbps_Out90,
(SELECT MAX(In_Averagebps) AS Maxbps_In95 from (SELECT TOP 95 PERCENT In_Averagebps
FROM InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY In_Averagebps ASC
) AS x ) AS Maxbps_In95,
(SELECT MAX(Out_Averagebps) AS Maxbps_Out95 from (SELECT TOP 95 PERCENT Out_Averagebps
FROM InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY Out_Averagebps ASC
) AS x ) AS Maxbps_Out95,
(SELECT MAX(In_Averagebps) AS Maxbps_In99 from (SELECT TOP 99 PERCENT In_Averagebps
FROM InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY In_Averagebps ASC
) AS x ) AS Maxbps_In99,
(SELECT MAX(Out_Averagebps) AS Maxbps_Out99 from (SELECT TOP 99 PERCENT Out_Averagebps
FROM dbo.InterfaceTraffic
WHERE InterfaceTraffic.InterfaceID = IFT.InterfaceID AND DateTime >= @StartDate AND DateTime <= @EndDate
AND DATEPART(HOUR, InterfaceTraffic.DateTime) BETWEEN @StartHour and @EndHour
AND DATEPART(weekday, DateTime) NOT IN (1, 7)
ORDER BY Out_Averagebps ASC
) AS x ) AS Maxbps_Out99
FROM InterfaceTraffic AS IFT
GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
Where
(
--My custom properties
Nodes.Site_ID LIKE '4450' OR
Nodes.Site_ID LIKE '9999'
)
AND
(
(Interfaces.InterfaceName LIKE 'GigabitEthernet0/1.5%%') OR
(Interfaces.InterfaceName LIKE 'GigabitEthernet1.50%%') OR
(Interfaces.InterfaceName LIKE 'GigabitEthernet0/1.%%') OR
(Interfaces.InterfaceName LIKE 'Serial2/0%')
--(Interfaces.InterfaceName LIKE 'mult%%')
