I'm trying to write monthly reports for application availability in Report Writer. I'm trying to get historical application availability data for each month that has gone and all of the web-based reports don't give the option to create a custom that contains application availability.
The built-in 'Application Availability - Last Month' (under APM: Daily Application Availability) report seems to fit the bill nicely after adding more WHERE conditions to dust off some of the irrelevant data, but I'm only able to get only the last month data showing and amending the -1 (to say -2) after the @actualMonths doesn't update the selected month that it is being displayed.
I've also tried amending the 0 in the second WHERE clause, like to -1 hoping that would put end month value to the one before.
SET @utcOffset = datediff(minute, getdate(), getutcdate())
SET @actualMonths = datediff(month, 0, getdate())
SELECT
max(APM_ApplicationStatus.[TimeStamp]) as [Month],
Nodes.Caption,
APM_Application.Name AS ApplicationName,
sum(APM_ApplicationStatus.PercentAvailability * APM_ApplicationStatus.RecordCount) / sum(APM_ApplicationStatus.RecordCount) AS PercentAvailability
FROM APM_ApplicationStatus with(nolock)
INNER JOIN APM_Application with(nolock) ON APM_Application.ID = APM_ApplicationStatus.ApplicationID
INNER JOIN Nodes with(nolock) ON Nodes.NodeID = APM_Application.NodeID
WHERE APM_ApplicationStatus.[TimeStamp] >= dateadd(minute, @utcOffset, dateadd(month, @actualMonths - 1, 0)) AND
APM_ApplicationStatus.[TimeStamp] < dateadd(minute, @utcOffset, dateadd(month, @actualMonths, 0))
GROUP BY APM_Application.ID, APM_Application.Name, Nodes.Caption
ORDER BY Nodes.Caption, APM_Application.Name
I've also tried changing the max(APM_ApplicationStatus.[TimeStamp]) as [Month] after SELECT but it doesn't affect the SELECTED/displayed month either.
Can anyone give any assistance please? What's the best way to write monthly reports older than one month?