Reporting Question - Number of minutes an interface was > 85% utilized

christopher.beyer

Hello Forum!

I'm hoping someone else has figured this out already to save me some headache (and hair):

 

I want to run a monthly report that will list all managed interfaces that have seen utilization > 85%, and the number of minutes they ran >85% for the month.  I'm looking for a report that will read something like this:

     Device     Interface    Time Spent >85% (minutes)

     Router1   Serial0/0    130 

     Router2   Serial0/0    2005

     Router 3  Serial0/0    892

 

I have not ahd any luck getting this to work... any suggestions would be greatly appreciated!

njoylif

you might be able to adapt from this report, which counts the number of instances that utilization goes over 45% within the last 7 days for serial (sub)interfaces where the hostname is like 'R-XRT%'

 

<Report Version="1.0" Group="Custom SQL" Title="Customer WAN &gt; 45% COUNT" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="Within the last 7 Days (NON FTP Customers)" Description="Provides Router and Interfaces where Utilization AVERAGED &gt; 45% FOR 5 MIN, and the number of times it occurred in the last 7 days." Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="6/11/2007" Ending_DateTime="7/12/2007" Grouping="Date" Group_Position="Beginning" SQL="SELECT
Nodes.NodeID,
Nodes.HostName,
Interfaces.InterfaceID,
Interfaces.Caption,
Interfaces.OutBandwidth,
Interfaces.InBandwidth,
COUNT(*)

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

WHERE
(Nodes.HostName LIKE 'R-XRT-%') AND (Nodes.ServiceFTP &lt;&gt; 1) AND
(   (InterfaceName LIKE 'Ser%.%') AND (InterfaceName NOT LIKE 'Ser%.16')  )     AND
(
    (((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) &gt; 45)
         OR
    (((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) &gt; 45)
)

GROUP BY
Nodes.HostName, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,
Interfaces.InBandwidth, Interfaces.Caption

ORDER BY Nodes.HostName" TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups><Group Field="HostName" GroupHeader="${HostName}" WEBURL="" Alignment="0" ForeColor="0" BackColor="16777215" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group><Group Field="Caption" GroupHeader="${Caption}" WEBURL="/NetPerfMon/View.asp?View=InterfaceDetails&amp;NetObject=I:${InterfaceID}" Alignment="0" ForeColor="0" BackColor="16777215" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group></LayoutGroups><Fields><Field Name="NodeID" Header="Node ID" Range="Positive" Parser="None" Format="" Alignment="Center" Width="0" Units="" Hidden="True" WebURL="/NetPerfMon/View.asp?View=NodeDetails&amp;NetObject=N:${NodeID}"></Field><Field Name="HostName" Header="HostName" Range="ALL" Parser="None" Format="" Width="0" Units="" Hidden="False" WebURL="">orion.ndchealth.int/.../View.asp Name="InterfaceID" Header="Interface ID" Range="Positive" Parser="None" Format="" Alignment="Center" Width="0" Units="" Hidden="True" WebURL=""></Field><Field Name="Caption" Header="Interface" Range="ALL" Parser="None" Format="" Alignment="Left" Width="0" Units="" Hidden="False" WebURL="">orion.ndchealth.int/.../View.asp Name="" Header="Number of Occurences" Range="ALL" Parser="Numeric" Format="" Alignment="Center" Width="0" Units="" Hidden="False" WebURL=""></Field></Fields></Report>