5 Replies Latest reply on May 30, 2016 3:20 AM by chinmaychavan

    Monthly Availability Business Hours Report

    foxy_no5

      Hi

       

      I have created various application groups containing various servers and applications that make up each service.

       

      I am looking to run monthly availabilty reports for these services within working hours 8-17:30 Mon-Fri.

      I am unable to find how to group these services in an SQL query or add the time correctly as is does not display (24 hour format?)

       

      Any help would be much appretiated!

       

      Thanks

        • Re: Monthly Availability Business Hours Report
          Detroiter

          What are you looking for availability of?  The Nodes, Applications, Components or Groups (actually called Containers in the database)?

           

          For Nodes, use:  AVG([OrionDB].dbo.ResponseTime.Availability)

          For Applications, use:  AVG([OrionDB].dbo.APM_ApplicationAvailability)

          For Components, use:  AVG([OrionDB].dbo.APM_ComponentAvailability)

          For Groups(Containers), use:  AVG([OrionDB].dbo.Containers_ContainerAvailability)

           

          As for the business hours, use a nested AND section in the WHERE clause.  Something like this:

          ((DATEPART(weekday, DateTime) >= 2)   --Monday

              AND (DATEPART(weekday, DateTime) <= 6)   --Friday

              AND (Convert(Char,DateTime,108) >= '08:00')

              AND (Convert(Char,DateTime,108) <= '18:00'))

            • Re: Monthly Availability Business Hours Report
              foxy_no5

              Hi many thanks but do you hav a full SQL script I could add?

               

              I have created various tabs in the Applications that contain Servers and Applications that make up each Service!

              I need to report on the SLA availabilty of each service for the month e.g Intranet/Source docs/Citrix/GIS

               

              I have created a report with the business hours but i do not get any results?

               

              Many thanks

                • Re: Monthly Availability Business Hours Report
                  Detroiter

                  So here is the SQL query that I use to report on all the availability of all the applications assigned to a particular set of servers.  Since I want it to be dynamic, the date variables are used to ensure it always polls the last month's data.  Is this what you are looking for?

                   

                  DECLARE

                   

                  @LastMonth1 datetime = (SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),

                  @Lastmonth2 datetime = (SELECT DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)))

                   

                   

                  SELECT  TOP 10000

                   

                  CAST (CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS Date) AS "Summary Month",

                  Nodes.Caption AS "Node Name",

                  APM_AlertsAndReportsData.ApplicationName AS "Application Name",

                  ROUND (AVG(APM_DailyApplicationAvailability.PercentAvailability), 3) AS "AVERAGE of Application Availability"

                   

                   

                  FROM

                  ([DL-ORION].dbo.Nodes INNER JOIN [DL-ORION].dbo.APM_AlertsAndReportsData ON (Nodes.NodeID = [DL-ORION].dbo.APM_AlertsAndReportsData.NodeId))  INNER JOIN [DL-ORION].dbo.APM_DailyApplicationAvailability ON (APM_AlertsAndReportsData.ApplicationId = APM_DailyApplicationAvailability.ApplicationID)

                   

                  WHERE

                  ( DateTime BETWEEN @LastMonth1 AND @LastMonth2 )

                  AND 

                  (

                    (Nodes.UnManaged = 0) AND

                    (Nodes.Comments LIKE 'WV%')

                  )

                   

                  GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

                  Nodes.Caption, APM_AlertsAndReportsData.ApplicationName

                   

                  ORDER BY "Summary Month" ASC

              • Re: Monthly Availability Business Hours Report
                dave.stafford

                There are a number of issues with SolarWinds Availability reporting that you should be aware of.

                 

                Group availability reporting averages will usually be incorrect due to the database design. The Group Percent Availability values are stored as integers, and when you perform integer arithmetic you get integer results. So even if your actual group availability is 99.9999999% the report will be 99.00%

                 

                If you change the report SQL from:

                 

                AVG(Containers_ContainerAvailability.GroupPercentAvailability) AS AVERAGE_of_Group_Availability,

                 

                to (shortened for brevity)

                 

                round((SUM(GroupPercentAvailability)+0.0)/COUNT(b.GroupPercentAvailability),3) AS AVERAGE_of_Group_Availability,

                 

                Adding 0.0 causes the database to perform floating point arithmetic, and you will get close to the expected result. However, the daily averages will all be integers as well, so even if a system had 99.999% availability for the day, it may have been written as 99% to the database.

                 

                But the availability result for both Groups and Nodes may still be wrong depending on when you do the calculation.

                 

                The problem is that Solarwinds seems to keep 30 days of rolled up hourly averages, and thereafter daily averages. So if you do a report for last month say in the middle of this month, it will average 15 x  one daily average, and 15 x 24 hourly averages which is totally incorrect and will completely skew any results depending on when the outage was.

                 

                What it should do is weight the results so that the daily value has a weight of 24 and the hourly result a weight of 1. 

                 

                Note that the web-based report for Node availability seems to be correct (i.e. weighted), but the report writer is definitely not. Both Web-based and Report writer reports for group availability seem to be incorrect.

                 

                I noticed this because we had an planned outage for power maintenance at a site that lasted 29 hours in the middle of last month. Everything at the site was off and so availability should have been around 96% , i.e. 100-(29/744)=96.1    744 being the number of hours in the month.

                 

                Fortunately I checked the availability reporting in the middle of the month and noticed totally different results to the expected 96% except for the Web-Report for node availability. Had I checked at the start of the month or the end of the month the results would have looked correct as the averages would have been nearly all hourly or all daily.

                 

                Dave