1 Reply Latest reply on Dec 12, 2008 5:11 PM by jp

    Macros and Reports


      I'm doing some work on the report generation code as part of the Support for custom ASP resources ends in v9.5 - switch to ASP.NET and Report Writer per Device brings up an important feature of that code: macro subsitution in the SQL query and filter. I'd like to make sure that when we ship this code that it supports all the ways you are using macros with reports. If you post them here, we can test them.

        • Re: Macros and Reports

          I'm really leaning on SQL reports, the built in reports dont' really seem to serve most of my purposes.  I don't think i'm expanding any macros in here.

          I use the field selector (IE: Availability -> /Orion/NetPerfMon/CustomChart.aspx?ChartName=AVGRTLOSS&Title=&SubTitle=&SubTitle2=&Width=640&Height=0&NetObject=N:${NodeID}&CustomPollerID=&Rows=&SampleSize=10M&Period=Last%207%20Days&PlotStyle=&FontSize=2&NetObjectPrefix=N&SubsetColor=&RYSubsetColor=&ResourceID=13 )

          Within the fields I will use ${Macros}.

          SELECT n.StatusLED, r.NodeID, n.Caption,  AVG(r.Availability) as Availability, n.Critical, n.LogicalDescription  FROM ResponseTime_Detail as r
          JOIN Nodes as n
          ON r.NodeID = n.NodeID
              --Sunday after 2AM and before 8PM
              (DatePart(weekday,r.DateTime) = 1 AND DatePart(hour,r.DateTime) > 2 AND DatePart(hour,r.DateTime) < 19)
              --Monday after 2AM
              (DatePart(weekday,r.DateTime) = 2 AND DatePart(hour,r.DateTime) > 2)
              (DatePart(weekday,r.DateTime) = 3)
              --Before 8PM Wednesday
              (DatePart(weekday,r.DateTime) = 4 AND DatePart(hour,r.DateTime) < 19)
              --After 2AM Thursday
              (DatePart(weekday,r.DateTime) = 5 AND DatePart(hour,r.DateTime) > 2)
              --Before 8PM Friday
              (DatePart(weekday,r.DateTime) = 6 AND DatePart(hour,r.DateTime) < 19)
              --After 2AM Saturday and before 8PM
              (DatePart(weekday,r.DateTime) = 7 AND DatePart(hour,r.DateTime) > 2 AND DatePart(hour,r.DateTime) < 19)
            --Allows uptime statistics to accumulate for nodes if they are not down during scheduled change activity
            OR (r.Availability <> '0')
           --Last seven days (This may break in the new year for the first query)
          AND (DATEDIFF(dayofyear,getdate(),r.DateTime) > -7)
           --We monitor UNIX and WINTEL nodes, but these are not part of our statistics (They are responsible for their own reboots)
           AND (n.Owner = 'Network')
           --Removes invalid Availability polling statistics that could cause questionable percentages
           AND NOT (r.PercentLoss = 0 AND r.Availability = 0)

          GROUP BY r.NodeID, n.Caption, n.LogicalDescription, n.StatusLED, n.Critical
          ORDER BY Critical DESC,Availability ASC