Avg Utlization report??

Question

Has anyone figured out the SQL to make a reporr that would show, for example, all object where the Average Utlization for the past 30 days is greater than 60%???

We're trying to make such a report so we can easily see sites that need a WAN line bandwidth speed bump-up.

Any ideas would be greatly welcome, and thanks in advance!!

Don Barry
Aegis Mortgage Corp
Houston, TX

Isaac · Answer

Hi Don,

Let me start by saying that I am no SQL master.  With that said, I think this should be fairly simple.  You can use the report writer to generate a report with the node name, interface name, and average utilization.  Once you have this built all you need to do is add a filter for avg util greater than 60%.

Here is the SQL for the report I just built to do this (my threshold was 50%):SELECT CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
Interfaces.InterfaceID AS InterfaceID,
Interfaces.InterfaceName AS Interface_Name,
Interfaces.InterfaceSpeed AS Interface_Speed,
AVG(Case
    When InBandwidth+OutBandwidth=0 Then 0
    When InBandwidth=0 Then
        (Out_Averagebps/OutBandwidth) * 100
    When OutBandwidth=0 Then
        (In_Averagebps/InBandwidth) * 100
    Else
        ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit

FROM 
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE 
( DateTime BETWEEN 38535 AND 38566 )
 AND  
(
  (Case
    When InBandwidth+OutBandwidth=0 Then 0
    When InBandwidth=0 Then
        (Out_Averagebps/OutBandwidth) * 100
    When OutBandwidth=0 Then
        (In_Averagebps/InBandwidth) * 100
    Else
        ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
End > 50)
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101), 
Nodes.NodeID, Nodes.Caption, Interfaces.InterfaceID, Interfaces.InterfaceName, Interfaces.InterfaceSpeed

ORDER BY SummaryMonth ASC, 3 ASC, 5 ASC

Be fantastic.