Hello,
I've created a custom SQL monthly report that shows all of my branch routers utilization of the T1 circuits. The report shows hour by hour and filters out any utilization that is below 80%. I'm having trouble with the new addition i'm making to it. Here is the SQL query
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
WHERE
(datetime > dateadd(mm,-1,getdate())
AND
(
(Nodes.DeviceType = 'Branch Router') AND
(Interfaces.InterfaceName = 'Serial0/0/0') AND
(
(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End >= 80) AND
(DatePart(Hour,DateTime) >= 0))
))
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0),
Nodes.Caption, Interfaces.Caption
ORDER BY SummaryDateTime ASC, 2 ASC, 3 ASC
Is it possible to add a duration to this report? i would like to see how long the receive utilization stays above 80%. if i cannot do it in this report, is it possible to put it into another report? Any help would be much appreciated.
