cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Group Availability Reports

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.

0 Kudos
16 Replies
Level 8

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?

0 Kudos

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.

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?

0 Kudos

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

0 Kudos

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

0 Kudos

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.

0 Kudos

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.

0 Kudos
Level 10

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.

0 Kudos

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.

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
0 Kudos

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

reports-2-2-000722.png

Thanks, but I'm not great with SQL.

0 Kudos
Level 7

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.

0 Kudos

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.

0 Kudos

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

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.

0 Kudos