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)

    msib

      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())

       

       

       

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

      https://thwack.solarwinds.com/docs/DOC-188643

      https://thwack.solarwinds.com/thread/60701

      https://support.solarwinds.com/Success_Center/Server_Application_Monitor_(SAM)/Knowledgebase_Articles/Create_an_Application_Availability_report_with_status

      https://thwack.solarwinds.com/community/solarwinds-community/geek-speak/blog/2014/02/25/filtering-for-days-of-the-week-in-orion-report-writer

       

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