1 Reply Latest reply on Nov 15, 2018 8:02 AM by zackm

    Help tuning out of box Application Availability report to only business hours (M-F, 0700-1900)


      Hi THWACK,


      I need some assistance tuning the out of box "Application Availability - Last Month" report to only report on data from business hours (0700-1900) Monday thru Friday.  This is the SQL code that comprises the report from report writer:


      DECLARE @utcOffset int

      DECLARE @actualMonths int




      SET @utcOffset = datediff(minute, getdate(), getutcdate())

      SET @actualMonths = datediff(month, 1, getdate())





           max(APM_ApplicationStatus.[TimeStamp]) as [Month],


           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 have tried the following other resources to tune this report but I have not had any success:






      It seems most of the articles I have found are mostly relevant to NPM reports not SAM focused ones.  Any thoughts?