2 Replies Latest reply on Sep 2, 2011 10:59 AM by pmailloux

    Report stalling ORION.

    pmailloux

      The following sql report runs just fine without the bold code, but as soon as it's added in the report doesn't load and then all other Orion reports will not load until the server is reset.

      Any idea's why this might be occurring?

      <Report Version="1.0" Group="DEV" Title="95 tile test" ModuleTitle="" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="" Description="" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="7/30/2011 0:0:0" Ending_DateTime="8/30/2011 0:0:0" Grouping="Date" Group_Position="Beginning" SQL="DECLARE @StartDate DateTime
      DECLARE @EndDate DateTime

      SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
      SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

      set nocount on
      create table #tmpJoin (fromDate datetime, toDate datetime)
      insert into #tmpJoin values(@StartDate,@EndDate)
      set nocount off

      SELECT Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.HA,
      Nodes.Site,
      Nodes.VendorIcon AS Vendor_Icon, 
      Nodes.Caption AS NodeName,    
      Interfaces.InterfaceIcon AS Interface_Icon,
      WANBandwidth AS Bandwidth,
      Maxbps_In95,
      Maxbps_Out95,
      Maxbps_95,
      SUM(InterfaceTraffic.In_TotalBytes) AS SUM_of_Total_Bytes_Received
      FROM Nodes
      INNER JOIN InterfaceTraffic ON (InterfaceTraffic.NodeID = Nodes.NodeID)
      INNER JOIN Interfaces
          ON (Nodes.NodeID = Interfaces.NodeID)

      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              JOIN #tmpJoin ON A.DateTime &gt;= #tmpJoin.fromDate AND A.DateTime &lt;=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_IN
          ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              JOIN #tmpJoin ON A.DateTime &gt;= #tmpJoin.fromDate AND A.DateTime &lt;=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_OUT
          ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              JOIN #tmpJoin ON A.DateTime &gt;= #tmpJoin.fromDate AND A.DateTime &lt;=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_MAX
          ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
      WHERE interfaces.WANFeed = 'Primary' AND nodes.region &lt;&gt; 'ENG'

      GROUP BY Nodes.HA, Nodes.NodeID, Nodes.Site, Nodes.VendorIcon, Nodes.Caption, Interfaces.InterfaceIcon, Interfaces.InterfaceID, Nodes.wanbandwidth, RESULT_IN.Maxbps_in95, RESULT_OUT.maxbps_out95, RESULT_MAX.maxbps_95

      ORDER BY WANBandwidth DESC, MAXbps_95 DESC


      Drop table #tmpJoin" TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups></LayoutGroups><Fields></Fields></Report>

        • Re: Report stalling ORION.
          qle

          Are you saying that all three bold sections have to be removed in order for the query to complete? That is to say, if you remove the first two sections and leave the GROUP BY clause in place, it still won't complete?

            • Re: Report stalling ORION.
              pmailloux

              The GROUP BY can stay and it would still run fine, but it needs to be changed so it doesn't reflect the removed fields (interfacetraffic.in_totalBytes).

               

              It's only when I try to select and join the Interfacetraffic table the the web interface in Orion stalls out. The database server spikes for a few seconds, then the cpu usage on the ORION web server maxes out and stays there until it's reset.