1 Reply Latest reply on Mar 1, 2017 11:52 AM by tdanner

    Monthly/Quarterly Business hour SLA Report

    jkrenzien

      I recently attended a SolarWinds webinar about Business hour SLA reports thru SWQL. This is the query they provided:

      SELECT N.Caption, DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime)) AS Day,

      SUM(N.ResponseTimeHistory.Availability*N.ResponseTimeHistory.Weight)

      /SUM(N.ResponseTimeHistory.Weight) AS SLA_Availability,

          N.CustomProperties.time_start, N.CustomProperties.time_end

      FROM Orion.Nodes N

      WHERE HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) >= N.CustomProperties.time_start

          AND HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) < N.CustomProperties.time_end

      GROUP BY N.NodeID, N.Caption,

          DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime)),

          N.CustomProperties.time_start, N.CustomProperties.time_end

       

      I would like to take this a few steps further. First only Monday-Friday (excluding holidays would be great, but I feel that would add to much complexity to what is likely an already complex query). Second I would like it to give me the Monthly or Quarterly SLA for a node. I have it returning data from the first quarter and only Mon-Fri, but I am unsure how to go about consolidating the data to a Monthly or Quarterly average. Also I have to limit this to only one node when using the SWQL Studio otherwise it times out after 2 minutes.

       

      SELECT N.Caption, DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime)) AS Day,

      SUM(N.ResponseTimeHistory.Availability*N.ResponseTimeHistory.Weight)

      /SUM(N.ResponseTimeHistory.Weight) AS SLA_Availability,

          N.CustomProperties.time_start, N.CustomProperties.time_end

      FROM Orion.Nodes N

      WHERE HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) >= N.CustomProperties.time_start

          AND HOUR(TOLOCAL(N.ResponseTimeHistory.DateTime)) < N.CustomProperties.time_end

          AND WEEKDAY(TOLOCAL(N.ResponseTimeHistory.ObservationTimestamp)) IN (1,2,3,4,5)  

          AND QuarterofYear(DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.ObservationTimestamp))) = '1'

      GROUP BY N.NodeID, N.Caption,

          DATETRUNC('day', TOLOCAL(N.ResponseTimeHistory.DateTime)),

          N.CustomProperties.time_start, N.CustomProperties.time_end

       

       

      Any help or guidance would be appreciated.

        • Re: Monthly/Quarterly Business hour SLA Report
          tdanner

          First, what are your data retention settings? The default retention settings (found in the product at /Orion/Admin/PollingSettings.aspx) are to summarize from detailed data to hourly at 7 days and from hourly to daily at 30 days. Once the data has been summarized to daily, it is no longer possible to do business hour filtering. The time associated with the daily value will be midnight, so it will probably be filtered out from the query as written.

           

          Assuming you have the data, you can consolidate the SLA number to a monthly or quarterly number by changing DATETRUNC('day', ...) to DATETRUNC('quarter', ...) or DATETRUNC('month', ...) in the SELECT and GROUP BY clauses.