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.

[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?

[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.

[clarsen]

Can anyone say if this issue is still present as of today (Jan 25, 2016)?  Are the availability stats recorded as floats now, or are they still integers?

[clarsen]

Sorry: found it.  The tables 'dbo.ContainerStatus_Daily' or 'dbo.ContainerStatus_Detail' column PercentAvailability data is still an integer.  <heavy sigh>

[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

[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.

[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.

[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.

[LadaVarga]

Hello stefanIT

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

Optimizied Steven Klassen 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.

[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:

Hide this column in the resulting table

Allow HTML tags

Most often used for reference data, this column will be included in exports to Excel

Add display settings:

Select new ... Days Ago Details Page Link Generic Icon Preserve Whitespace Status Icon (Watermark) Task Result Status Icon Warranty Status Icon

Data aggregation:

Not specifiedCountMinMaxCount (Distinct)

Valid range:

Not specifiedAll valuesZero and aboveOnly positive values

Alignment:

Left alignedCenter alignedRight aligned

Column 2: Group Availability

Database column:

Group Status History/Group Availability

Display name:

Hide this column in the resulting table

Allow HTML tags

Most often used for reference data, this column will be included in exports to Excel

Display setting:

Custom Format

»What are valid Custom Formats?

Add display settings:

Select new ... Details Page Link Generic Icon Status Icon Status Icon (Watermark) Task Result Status Icon VIM Host Status Icon Warranty Status Icon

Data aggregation:

Not specifiedSumCountAverageMinMaxCount (Distinct)

Valid range:

Not specifiedAll valuesZero and aboveOnly positive values

Alignment:

Left alignedCenter alignedRight aligned

Column 3: Timestamp

Database column:

Group Status History/Timestamp

Display name:

Hide this column in the resulting table

Allow HTML tags

Most often used for reference data, this column will be included in exports to Excel

Add display settings:

Select new ... DateTime format Days Ago Details Page Link Preserve Whitespace Status Icon (Watermark) Task Result Status Icon Warranty Status Icon

Data aggregation:

Not specifiedCountMinMaxCount (Distinct)

Valid range:

Not specifiedAll valuesZero and aboveOnly positive values

Alignment:

Left alignedCenter alignedRight aligned

Time-based settings:

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

   No  Yes    

 

Date/Time column in this table is:

Timestamp   This field must be specified

This is the column we will use for summarization and limiting by time period

  Sample Interval:

Show all values in the database, do not summarize  Hour  Day  Week  Month  Quarter  Year  Day of Week 

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 Column...Name - GroupGroup Availability - Group Status HistoryTimestamp - Group Status History  

Select Field  

  Group results by:

Select Column...Name - GroupGroup Availability - Group Status History  

  Filter results:

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

  Show all records
  Show only the top Value must be a valid integer between 1 and 9999 Value must be a valid integer between 1 and 9999   records
  Show only the top Value must be a valid integer between 1 and 100 Value must be a valid integer between 1 and 100   % of records

[LadaVarga]

When you creating Custom Table you pick Type of datasource. There is SQL.

[stefanIT]

Thanks, but I'm not great with SQL.

[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.

[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.

[mrxinu]

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.

[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.