1 Reply Latest reply on Dec 18, 2014 10:36 AM by njoylif

    Custom SQL report questions

    michael.tabor

      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.

        • Re: Custom SQL report questions
          njoylif

          maybe a stepping stone...

          this report [logic] can show you how to add a count for the number of times the [poll interval] average is > your setting...

          so for this report as is, polling every 5 minutes, it gives the number of times that 5min avg was > 60%.

          you could also take that count *5 = # of minutes avg > your setting...  (or * your poll interval)

           

          SELECT

               (count logic here) as 'counter',

               (count logic here)*5 as 'durationInMinutes',

               ...

          FROM

          ...

          2 of 2 people found this helpful