16 Replies Latest reply on Jan 25, 2016 4:18 PM by clarsen

    Group Availability Reports

    rjs2011

      Hi, new to SolarWinds and it's reporting so apologies if this is a simple question.

      I'm trying to report on the availability of groups of servers at different sites. The most obvious way to do this appeared to be creating groups and using the Group Availability reports, however frustratingly the availability figures reported appear to round to the nearest whole number. I need these to report to two decimal places, e.g. 99.86%.

      I'm hoping this is a simple modification to make. Any ideas?

      Many thanks in advance.

        • Re: Group Availability Reports
          rkmurph

          I know this thread is very old but I'm also looking for the same thing.  Rounding to the whole number for availability isn't what management is looking for.

            • Re: Group Availability Reports
              rjs2011

              I ended up using Node availability reports, filtering on keywords in the Comments field, and then inputting the data into a spreadsheet which did the rest.

              Not really satisfactory, but it did the job in my case.

              Would still be interested in a neater solution if one exists.

                • Re: Group Availability Reports
                  Steven Klassen

                  The issue is the data type that comes from the group availability views in the database (integer).  Unfortunately you can't convert data types using the regular Report Writer interface.  If you take the SQL from one of the out-of-the-box reports (Group Availability - This Month) by selecting Report > Show SQL you'll get the following:

                   

                  SELECT  TOP 10000

                    Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

                    Containers_AlertsAndReportsData.GroupName AS Group_Name,

                    AVG(Containers_DailyContainerAvailability.GroupPercentAvailability) AS AVERAGE_of_Group_Availability

                  FROM Containers_AlertsAndReportsData

                  INNER JOIN Containers_DailyContainerAvailability ON (Containers_AlertsAndReportsData.GroupID = Containers_DailyContainerAvailability.GroupID)

                  WHERE ( DateTime BETWEEN 40328 AND 40346 )

                  GROUP BY

                    Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

                    Containers_AlertsAndReportsData.GroupName

                  ORDER BY SummaryDate ASC, 2 ASC

                   

                  The issue is on line four: AVG(Containers_DailyContainerAvailability.GroupPercentAvailability) AS AVERAGE_of_Group_Availability

                   

                  Paste the entire thing into a new advanced SQL report and make the following adjustments (bold below):

                   

                  SELECT  TOP 10000

                    Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

                    Containers_AlertsAndReportsData.GroupName AS Group_Name,

                    AVG(CONVERT(FLOAT,Containers_DailyContainerAvailability.GroupPercentAvailability)) AS AVERAGE_of_Group_Availability

                  FROM Containers_AlertsAndReportsData

                  INNER JOIN Containers_DailyContainerAvailability ON (Containers_AlertsAndReportsData.GroupID = Containers_DailyContainerAvailability.GroupID)

                  WHERE (MONTH(DateTime) = MONTH(GetDate()))

                  GROUP BY

                    Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

                    Containers_AlertsAndReportsData.GroupName

                  ORDER BY SummaryDate ASC, 2 ASC

                   

                  Also, make sure you set the field formatting to 'Numeric Data' and the format string to '0.00' or '0.000' depending how much precision you'd like.

                  SNAG-0139.png

                  1 of 1 people found this helpful
                    • Re: Group Availability Reports
                      ksmithhhf

                      This code change isn't working for me. When I add the CONVERT FLOAT line specified above I receive the following error message:

                       

                      The multi-part identifier

                      "Containers_DailyContainerAvailability.GroupPercentAvailability"

                      could not be bound.

                       

                      We're in bad need of a canned report that provides Group and Group Member average availability the way everyone seems to want it. It should not be washed out by the best or worst availability of a single member as currently designed. It should show an average of the average availability of its members over the time period specified.

                • Re: Group Availability Reports
                  stefanIT

                  Did anyone find a solution to this? I've tried creating a web report to do the same thing, and although the functionality is there, the resulting query (when including 30+ Groups) brings the Orion server to its knees.

                    • Re: Group Availability Reports
                      LadaVarga

                      Hello stefanIT

                       

                      Could you please provide us your query we can look and try optimize.

                       

                      Optimizied mrxinu query:

                       

                      DECLARE @StartDate DateTime

                      SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime);

                      SELECT  TOP 10000

                        Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

                        Containers_AlertsAndReportsData.GroupName AS Group_Name,

                        AVG(Containers_DailyContainerAvailability.GroupPercentAvailability*1.00) AS AVERAGE_of_Group_Availability

                      FROM Containers_AlertsAndReportsData

                      INNER JOIN Containers_DailyContainerAvailability ON (Containers_AlertsAndReportsData.GroupID = Containers_DailyContainerAvailability.GroupID)

                      WHERE DateTime > @StartDate /*and @EndDate  */

                      GROUP BY

                        Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

                        Containers_AlertsAndReportsData.GroupName

                      ORDER BY SummaryDate ASC, 2 ASC

                       

                      Can be added as SQL datasource and use it in Custom Table.

                        • Re: Group Availability Reports
                          stefanIT

                          Thanks LadaVarga, and sorry about the late response.

                           

                          This is from a web report, so I don't know how to display it as a SQL query. Settings are as follows:

                           

                          For:       "Service Catalogue" (30+ Groups containing nodes, applications, interfaces, volumes etc)

                          From:     Last 30 Days     

                           

                          Table

                           

                          Column 1: Name

                          Database column:Group/Name   Display name:      Most often used for reference data, this column will be included in exports to ExcelAdd display settings: Data aggregation:   Valid range:   Alignment:

                           

                          Column 2: Group Availability

                          Database column:Group Status History/Group Availability   Display name:      Most often used for reference data, this column will be included in exports to ExcelDisplay setting:Custom Format
                          »What are valid Custom Formats?
                          Add display settings: Data aggregation:   Valid range:   Alignment:

                           

                          Column 3: Timestamp

                          Database column:Group Status History/Timestamp   Display name:      Most often used for reference data, this column will be included in exports to ExcelAdd display settings: Data aggregation:   Valid range:   Alignment:

                           

                          Time-based settings:

                            Do you only want to see data in the table above from a specific time period? 

                               

                           

                          Date/Time column in this table is:

                                This field must be specified  
                          This is the column we will use for summarization and limiting by time period

                           

                            Sample Interval:

                           

                          from Last 30 Days (Feb 11 - Mar 13, 2014)

                           

                          You can change this after submit.  

                            This column is used for filtering or summarizing data by time period 

                            Sort results by:

                           

                            

                           

                          Select Field  

                            

                           

                            Group results by:

                           

                            

                           

                            

                            Filter results:

                           

                            This table can include all the records retrieved, or records can be filtered.



                          Value must be a valid integer between 1 and 9999 Value must be a valid integer between 1 and 9999
                          Value must be a valid integer between 1 and 100 Value must be a valid integer between 1 and 100
                      • Re: Group Availability Reports
                        handbrake2

                        Hi guys,

                         

                        I followed your advice and have the following SQL report:

                         

                        SELECT  TOP 10000 CONVERT(DateTime,

                        LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

                        101) AS SummaryMonth,

                        Containers_AlertsAndReportsData.GroupName AS Group_Name,

                        AVG(CONVERT(FLOAT,Containers_DailyContainerAvailability.GroupPercentAvailability*1.000)) AS AVERAGE_of_Group_Availability

                         

                        FROM

                        Containers_AlertsAndReportsData INNER JOIN Containers_DailyContainerAvailability ON (Containers_AlertsAndReportsData.GroupID = Containers_DailyContainerAvailability.GroupID)

                         

                         

                        WHERE

                        ( DateTime BETWEEN 41623 AND 41714 )

                        AND 

                        (

                          (Containers_AlertsAndReportsData.GroupName = 'Core Network Devices') OR

                          (Containers_AlertsAndReportsData.GroupName = 'Distribution Switches') OR

                          (Containers_AlertsAndReportsData.GroupName = 'Access Switches')

                        )

                         

                         

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

                        Containers_AlertsAndReportsData.GroupName

                         

                         

                        ORDER BY SummaryMonth ASC, 2 ASC

                         

                        The problem is that it is telling me that all of the averages are 100.000. That makes no sense. There was a power outage that caused down-time up to yesterday.Can anyone explain why everything is still being rounded to 100% and what I can do to resolve it?

                          • Re: Group Availability Reports
                            LadaVarga

                            Hello,

                             

                            How do you have set Rollup status on that groups?

                            what say this query:

                            SELECT TOP 10000 * FROM [dbo].[Containers_DailyContainerAvailability] JOIN

                            Containers_AlertsAndReportsData ON (Containers_AlertsAndReportsData.GroupID = Containers_DailyContainerAvailability.GroupID)

                            where datetime BETWEEN 41623 AND 41714

                            AND

                            (

                              (Containers_AlertsAndReportsData.GroupName = 'Core Network Devices') OR

                              (Containers_AlertsAndReportsData.GroupName = 'Distribution Switches') OR

                              (Containers_AlertsAndReportsData.GroupName = 'Access Switches')

                            )

                             

                             

                            Do you have somewhere 0 not only 100?

                             

                            Thanks

                              • Re: Group Availability Reports
                                handbrake2

                                Hi,

                                 

                                Status rollup mode is: Mixed status shows warning.

                                 

                                When I try to run the query you requested it generated more than 1000 pages of results.

                                What I really want is to get the individual member availabilities for the period and the number of members in each group and use that to calculate the overall average for each group.

                                 

                                Regards,

                                David.

                                  • Re: Group Availability Reports
                                    LadaVarga

                                    Hello,

                                     

                                    You can add many types of netobject to group (nodes, interfaces, application, volumes). We can't store availability for every member, that mean you can get only availability of whole group, which are computed from Rollup status -> can be 0 100 or 50. That above report do that.

                                     

                                    If you need availability of nodes, there is exist many other reports. You can group by custom property for example.

                                • Re: Group Availability Reports
                                  dave.stafford

                                  Be very careful about Group Availability reports as they will almost certainly be wrong. The Percentage availability for groups is stored as an integer in the database, and so any averages calculated will be returned as an integer. This is why you always see round numbers no matter how many decimal places you try to format to.

                                   

                                  The response above from Steve recognises this deficiency and converts to a float so that you get a floating point value in return, but this is even worse in my opinion as it masks the problem, and is still wrong.

                                   

                                  Individual measurements are rolled up to an hourly average, but stored as an integer. So if you have 99.9% availability for the hour it is probably stored as 100%. If you have 99.4% it'll probably be stored as 99% depending on how the database does rounding. Similarly after 30 days the hourly values are rolled up to daily averages, and again precision is lost in exactly the same way.

                                   

                                  And it gets worse. If you are reporting for last month, the SQL is run against a view that combines the daily and hourly tables, and generates an average of the averages. However, this is completely wrong statistically because it is combining hourly and daily averages with no weighting, i.e. an hourly average should be weighted 1/24th the value of a daily average.

                                   

                                  So essentially do not trust any group availability reporting. It is almost certainly incorrect.

                                   

                                  Note that the rolled up PercentageAvailability values in the Node and APM tables are stored as floating points and can be trusted.

                                   

                                  But the unweighted averaging problem is the same for Node and APM availability reports in ReportWriter, and so you cannot trust results for last month.

                                   

                                  The Web-based reports for Node and APM availability reports seem to be correct, but are also incorrect for Groups due to the database design issue.