DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
-- dynamic time constraints
SET @StartDate = CAST (DATEADD(day, DATEDIFF(day,0,GETDATE())-1, 0) as datetime)
SET @EndDate = CAST (DATEADD(millisecond, -1, DATEADD(day, DATEDIFF(day,0,GETDATE()), 0)) as datetime)
-- SQL query
SELECT Nodes.Caption AS NodeName, Interfaces.OutBandwidth AS Xmit_Bandwidth, MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps, COUNT(Interfaces.Inbps) AS COUNT_of_Recv_bps, MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps, COUNT(Interfaces.Outbps) AS COUNT_of_Xmit_bps, AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps, AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps, Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID FROM ( Nodes INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID ) ) INNER JOIN InterfaceTraffic ON ( Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID ) WHERE
(
(DATEPART(weekday, DateTime) <> 1) AND
(DatePart(Hour,DateTime) >= 8) AND
(DatePart(Hour,DateTime) <= 20) AND
(
(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End >= 70) OR
(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End >= 70)) AND
(
NOT (Nodes.Caption LIKE '%ATM%')) AND
(
NOT (Nodes.Caption LIKE '%doberman%')) AND
(
NOT (Nodes.Caption LIKE '%FW%')) AND
(
NOT (Nodes.Caption LIKE '%DC6509%'))
)
AND ( DateTime BETWEEN @StartDate AND @EndDate ) GROUP BY Nodes.Caption, Interfaces.OutBandwidth, Nodes.NodeID, Interfaces.InterfaceID ORDER BY 4 DESC, 6 DES
