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

Question

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!

mulambo · Accepted Answer

You nailed it! This is what I was looking for! Thank you dgsmith80

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

Top Replies