0 Replies Latest reply on Jun 28, 2018 11:46 AM by kwan

    Application availability report - data older than 1 month

    kwan

      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?