Knowledge Store: The Availability Report

The Availability Report

In contrast to the Alert report, the Availability report draws information from several different views. The Availability report shows the time in any given state (Red, Yellow, Green, White, Disabled, Planned Maintenance, and Unplanned Maintenance) for the monitored entities.

This report returns availability data, which is a state condition, so the information is in the vState views. The query starts off by selecting the amount of time in each state:

SELECTvState.InRedStateMilliseconds,

vState.InYellowStateMilliseconds,

vState.InGreenStateMilliseconds,

vState.InWhiteStateMilliseconds,

vState.InDisabledStateMilliseconds,

vState.InPlannedMaintenanceMilliseconds,

vState.InUnplannedMaintentanceMilliseconds,

vState.HealthServiceUnavailableMilliseconds

The report also needs to show which entities the information is associated with. Therefore, the query retrieves the name, ID, and path for the managed entities in the report. The ManagedEntityRowId is again important because it’s used as a foreign key later.

vManagedEntity.ManagedEntityRowId,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.Path,

The query also retrieves the type of the managed entity by retrieving the GUID for that entity:

vManagedEntityType.ManagedEntityTypeGuid,

The GUID for the managed entity type is enough to identify it but the report also displays the name for that type. If the management pack author did not include a display name, this field will not be available so the stored procedure uses an ISNULL statement to retrieve the name if it is available. If not, it retrieves the default name instead:

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName

The Availability report also displays an icon for each managed entity in the table so the query retrieves that image also:

vManagedEntityTypeImage.Image

In addition to the managed entity, the query also retrieves the monitor data:

vManagedEntityMonitor.ManagedEntityMonitorRowId,

The query also retrieves the display name for the monitor, just as it did for the managed entity. If the name isn’t available, the ISNULL statement substitutes the default monitor name.

ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,

The following two items are related to entity monitor dependencies:

vManagedEntityMonitorDependency.ParentManagedEntityRowId,

vManagedEntityMonitorDependency.[Level],

If the entities being monitoring are selected by Management Group, the query retrieves the ID and name of the group also:

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName

The SELECT clause retrieves all the data for display in the report. The next step is to determine where to select the data from. This information is with state data, which means you have a choice between vStateDailyFull and vStateHourlyFull. The Microsoft Generic Report Library uses vStateDailyFull:

FROM vStateDailyFull as vState

The query joins the vManagedEntityMonitor view to the state view in order to retrieve the monitors for the managed entities. The ManagedEntityRowId is the foreign key used:

INNER JOIN vManagedEntityMonitor ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityRowId

The query also joins the vManagedEntity view. This connects the specified monitors to the managed entities, again using ManagedEntityRowId as the foreign key:

INNER JOIN vManagedEntity ON

vManagedEntityMonitor.ManagedEntityRowID = vManagedEntity.ManagedEntityRowId

The query then joins the vManagedEntityType view using ManagedEntityTypeRowId as the foreign key:

INNER JOIN vManagedEntityType ON

vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId

Next, the query joins the vMonitor view to the state data. MonitorRowId is the key:

INNER JOIN vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId

The query also joins the dependencies:

INNER JOIN #ManagedEntityMonitorDependency AS vManagedEntityMonitorDependency ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId

Finally, the query joins the management groups to their managed entities using the ManagementGroupRowId as the foreign key:

INNER JOIN vManagementGroup ON

vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId

The query then retrieves the image to use for the entity type:

LEFT OUTER JOIN vManagedEntityTypeImage

ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId

AND vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

The last few joins retrieve the display strings for the managed entity and the monitor:

LEFT OUTER JOIN vDisplayString ON

vManagedEntityType.ManagedEntityTypeGuid = vDisplaySTring.ElementGuid AND

vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON

vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND

vDisplayStringMonitor.LanguageCode = @LanguageCode

The WHERE clause is the next part of the query:

WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert (varchar(8), @StartDate, 112))) AND

(vState.DateTime < DATEADD (hh, DATEPART(hh, @EndDate) +1, convert(varchar(8) @EndDate, 112))) AND

(vState.Date BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))

This clause retrieves all data that falls between the two dates that the report user specified in the date picker. @StartDate and @EndDate are the parameters from the date picker.

1. DATEPART(hh, @StartDate) returns the hour part of the @StartDate variable. This value is increased by 1 to return the next full hour after the time specified in @StartDate.

2. convert(varchar(8), @StartDate, 112) converts the @StartDate parameter to character data (varchar(8)) in the format yyyymmdd; the 112 specifies that format.

3. DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert (varchar(8), @StartDate, 112)) adds the specified hour to the specified date, in yyyymmdd format, rounded up to the next hour.

The effect of the entire WHERE clause is to specify the vState data collected between the specified hours. This WHERE clause can be useful in many different reports, and is used throughout the Microsoft Generic Report Library.

Unlike the Alert Report, the Availability report uses information from several different views, most of which can be joined on the ManagedEntityRowId. Also note how the vManagedEntityType and vMonitor views are joined if you want to include that information in your custom report.

Here’s what the complete query looks like:

SELECT

vState.DateTime,

vState.InRedStateMilliseconds,