1 Reply Latest reply on Jan 24, 2017 11:36 AM by ctlswadmin

    I've created a report for Last Month utilization on all store wan interfaces and set it to summarize to the month but instead of returning 1 record per interface, it returns the max of 10,000 records.  I just want what the avg utilization was for the past

    narvak

      This is what my sql looks like ....

       

      SELECT  TOP 10000 Interfaces.InterfaceID AS InterfaceID,

      1. Nodes.Caption AS NodeName,
      2. Interfaces.Caption AS Interface_Caption,
      3. Interfaces.InterfaceSpeed AS Interface_Speed,

      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 CircuitUtil_AvgRecvXmit

       

      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 BETWEEN 42642 AND 42672.9999884259 )

      AND 

      (

        (Interfaces.InterfaceAlias LIKE '%ATT%') AND

        (DatePart(Hour,DateTime) >= 8) AND

        (DatePart(Hour,DateTime) < 21) AND

        (

         (Nodes.Caption LIKE '%us%st_%'))

      )

       

       

       

      ORDER BY 5 DESC