I have a read a few post which happen to include some SQL scripts for reporting on the monitored applications. I have been able to test a few of them, getting the report to run and present the data in the desired form, but there are a few things I am still not able to do and I was wondering if anyone has been able to successfully do either of the following.
1.) I have seen a few times now a complaint about the results of the reports all being 100%. I am seeing a similar result and I know at least one of the listed services has been down within the period of the last 30 days. So, the issue may be within the way I am looking for a date/time range, but I am not sure why it is showing 100% for all services. Which leads to the next question.
2.) I report at the beginning of every month, so reporting on the last 30 days is not always applicable. I have tried referencing some of the last month reports built in, but they all seem to be slightly different in the WHERE area.
I have attached the script below. If anyone can, please let me know if you have any ideas how I might be able to overcome issue #1 and implement the "Last Month" feature within #2.
SELECT
Applications.FullName AS ApplicationName,
AVG(ApplicationStatistics.Availability) AS AVERAGE_of_AvailabilityFROM
Applications JOIN ApplicationStatistics ON (Applications.AppID = ApplicationStatistics.AppID)
WHERE
( DateTime > (GetDate()-30) )AND
(ApplicationStatistics.Availability<>0)
GROUP BY
Applications.AppID, ApplicationStatistics.AppID, ApplicationStatistics.Availability, Applications.FullName
ORDER BY 2 DESC
Thanks,
pierrobp