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!