This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Help Modifying a custom report to give me the ability to enter specific dates (pull historical) and then default to the last "Month"

HI Guys,

I have had NCM installed and monitoring for a few months now but due to issues never got my "Uptime" report working.  I worked with presales before purchasing and they helped get a demo report working off a custom field using SWQL queries.

The problem with these queries is that the WHERE statement is hardcoded to last 30 days and I lost the ability to change the table to use "Last Month" or any of the other prebuilt options in report builder.  

How can I change this SWQL statement to be able to hard code specific dates so I can get my missing historical data?  

Then I want to schedule the report automatically to report on the last month at the first of the new month.  How do I get it to properly do 30/31 days?

I have two custom queries as follows right now that both need modified.

SELECT 
     Orin.CustomProperties.AvailabilityReportingGroups as [Team]
,    OriN.Caption as [Node Name]
,    ToString(Round(AVG(OriN.ResponseTimeHistory.Availability),2))+'%' as Avail
     FROM Orion.Nodes OriN
WHERE OriN.ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
GROUP BY Orin.CustomProperties.AvailabilityReportingGroups, OriN.Caption
ORDER BY Orin.CustomProperties.AvailabilityReportingGroups, OriN.Caption DESC

SELECT
      OriC.AvailabilityReportingGroups as [Team]
,     ToString(Round(AVG(OriC.Node.ResponseTimeHistory.Availability),2))+'%' as [Availabilty]
FROM Orion.NodesCustomProperties OriC
WHERE OriC.AvailabilityReportingGroups IS NOT NULL and OriC.node.ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
GROUP BY OriC.AvailabilityReportingGroups

Any help is appreciated!