Macros and Reports

Question

I'm doing some work on the report generation code as part of the ASP.NET transition and euphorik's question 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.

jp · Answer

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 WHERE ( --Sunday after 2AM and before 8PM ( (DatePart(weekday,r.DateTime) = 1 AND DatePart(hour,r.DateTime) > 2 AND DatePart(hour,r.DateTime) < 19) OR --Monday after 2AM (DatePart(weekday,r.DateTime) = 2 AND DatePart(hour,r.DateTime) > 2) OR --Tuesday (DatePart(weekday,r.DateTime) = 3) --Before 8PM Wednesday OR (DatePart(weekday,r.DateTime) = 4 AND DatePart(hour,r.DateTime) < 19) OR --After 2AM Thursday (DatePart(weekday,r.DateTime) = 5 AND DatePart(hour,r.DateTime) > 2) OR --Before 8PM Friday (DatePart(weekday,r.DateTime) = 6 AND DatePart(hour,r.DateTime) < 19) OR --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