Inside a Generic Report

適用於: Operations Manager 2007 R2

Before you start building your own queries, it may be helpful to see how the queries for the built-in reports work. The sections below look at the queries used by three existing reports from the Microsoft Generic Report Library: The Availability report, the Alerts report, and the Performance report. For each report, the query is located in a stored procedure. The stored procedures are reproduced here so you can see the Transact-SQL code.

The Alert Report

The purpose of the Alert report is to present a simple list of alerts raised by the managed objects specified in the parameters of the report. The report in the Generic Library can list events of all severities (Information, Warning, and Critical) and all Priorities (Low, Medium, and High). For each alert, it provides the Alert Name, Repeat Count, Priority, the Object that raised the alert, and the first and last dates where the alert appeared.

This query deals with Alert data, so it uses the vAlert view. First, it retrieves the AlertName, Severity, and Priority, which are all basic information. It also retrieves the ManagedEntityRowId, which will be used as a foreign key later in the query:

SELECT 
Alert.vAlert.AlertName,
Alert.vAlert.Severity,
Alert.vAlert.Priority,
Alert.vAlert.ManagedEntityRowId,

Each time the alert is raised it creates a new record. To get the first and last dates where the alert was raised, this query uses the MAX and MIN functions:

MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,
MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,

The query also uses the MIN function to retrieve the AlertDescription. The description is just text, but using MIN guarantees that the query will retrieve only one entry for the given alert. This is required because the descriptions should all be identical:

MIN(Alert.vAlert.AlertDescription) AS AlertDescription,

To get the number of times an alert was repeated, the query uses the COUNT function. This simply counts the number of rows retrieved and indicates how many times the event was raised:

COUNT(*) AS RepeatCount,

Finally, the query retrieves the SiteName and the AlertProblemGuid. It does not use the AlertGuid because that row is unique for each individual Alert. Instead, it uses the AlertProblemGuid, which identifies the specific type of alert:

Alert.vAlert.SiteName, 
Alert.vAlert.AlertProblemGuid

To define the FROM clause, the query retrieves all of the rows above from the vAlert view. It then joins that information to several temporary tables. The #ObjectList table holds the objects passed in to the query from the report. The vAlert.Severity and vAlert.Priority fields are simply integers, which can be used with the #SeverityList.Severity field and the #PriorityList.Priority tables to return text strings:

FROM Alert.vAlert
INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId
INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity
INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority

The WHERE clause simply limits the query by the dates and the site name (if any) that was passed from the report form. The variables preceded by the @ symbol are parameters defined in the report form.

WHERE 
Alert.vAlert.RaisedDateTime >= @StartDate AND
Alert.vAlert.RaisedDateTime < @EndDate AND 
(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)

Finally, there’s a simple GROUP BY clause which groups records with identical results into summary rows, so that the report does not contain duplicate alerts:

GROUP BY
Alert.vAlert.AlertName,
Alert.vAlert.Severity,
Alert.vAlert.Priority,
Alert.vAlert.ManagedEntityRowId,
Alert.vAlert.SiteName, 
Alert.vAlert.AlertProblemGuid

Here’s what the complete query looks like:

SELECT 
Alert.vAlert.AlertName,
Alert.vAlert.Severity,
Alert.vAlert.Priority,
Alert.vAlert.ManagedEntityRowId,
MAX(Alert.vAlert.RaisedDateTime) AS LastRaisedTime,
MIN(Alert.vAlert.RaisedDateTime) AS FirstRaisedTime,
MIN(Alert.vAlert.AlertDescription) AS AlertDescription,
COUNT(*) AS RepeatCount,
Alert.vAlert.SiteName, 
Alert.vAlert.AlertProblemGuid
FROM Alert.vAlert
INNER JOIN #ObjectList ON #ObjectList.ManagedEntityRowId = Alert.vAlert.ManagedEntityRowId
INNER JOIN #SeverityList ON #SeverityList.Severity = Alert.vAlert.Severity
INNER JOIN #PriorityList ON #PriorityList.Priority = Alert.vAlert.Priority
WHERE 
Alert.vAlert.RaisedDateTime >= @StartDate AND
Alert.vAlert.RaisedDateTime < @EndDate AND 
(@SiteName IS NULL OR Alert.vAlert.SiteName = @SiteName)
GROUP BY
Alert.vAlert.AlertName,
Alert.vAlert.Severity,
Alert.vAlert.Priority,
Alert.vAlert.ManagedEntityRowId,
Alert.vAlert.SiteName, 
Alert.vAlert.AlertProblemGuid

Although this query may seem complicated, its function isn’t. It retrieves most of the values from the vAlert view, filtered by date, with additional access to some outside lists.

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, 
vState.InYellowStateMilliseconds, 
vState.InGreenStateMilliseconds,
vState.InWhiteStateMilliseconds,
vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds,
vState.InUnplannedMaintentanceMilliseconds,
vState.HealthServiceUnavailableMilliseconds
vState.IntervalDurationMilliseconds,
vManagedEntity.ManagedEntityRowId,
vManagedEntityMonitor.ManagedEntityMonitorRowId,
vManagedEntityTypeImage.Image,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid,
vManagedEntityMonitorDependency.ParentManagedEntityRowId,
vManagedEntityMonitorDependency.[Level],
ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,
vManagedEntity.ManagedEntityGuid,
vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName
vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName
FROM vStateDailyFull as vState
INNER JOIN vManagedEntityMonitor ON
vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityRowId
INNER JOIN vManagedEntity ON
vManagedEntityMonitor.ManagedEntityRowID = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType ON
vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vMonitor ON
vState.MonitorRowId = vMonitor.MonitorRowId
INNER JOIN #ManagedEntityMonitorDependency AS vManagedEntityMonitorDependency ON
vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId
INNER JOIN vManagementGroup ON 
vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityTypeImage ON
vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N’u16x16Icon’
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

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))

The Performance Report

The Performance report is similar to the Availability report in that it retrieves data for a specified time period on specific entities and retrieves the information from various views. The purpose of the Performance report is to report on the change of a value over time.

The query retrieves some basic information from the vPerfDaily view, which, in this query, has the alias of vPerf:

SELECT   
vPerf.DateTime, 
vPerf.SampleCount, 
vPerf.AverageValue, 
vPerf.MinValue, 
vPerf.MaxValue, 
vPerf.StandardDeviation,

The query also retrieves the performance rules located in the vPerformanceRuleInstance view:

vPerformanceRuleInstance.RuleRowId, 
vPerformanceRuleInstance.InstanceName,

As in the other queries, data is retrieved from the vManagedEntity view, in particular the ManagedEntityRowId, although the GUID, default name, and path are also retrieved:

vManagedEntity.ManagedEntityRowId, 
vManagedEntity.ManagedEntityGuid, 
vManagedEntity.ManagedEntityDefaultName, 
vManagedEntity.Path,

As in the Availability query, the query retrieves a display name if it exists:

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

To display management group information, the query accesses the vManagementGroup view:

vManagementGroup.ManagementGroupGuid, 
vManagementGroup.ManagementGroupDefaultName,

The query also accesses the vRule and vPerformanceRule views:

vRule.RuleGuid, 
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,
vPerformanceRule.MultiInstanceInd,

The query also retrieves group information from the temporary table #OptionList:

OptionList.[Group], 
OptionList.GroupTitle, 
OptionList.Position, 
OptionList.ChartScale, 
OptionList.ChartType, 
OptionList.ChartColor, 
OptionList.OptionXml,
vManagedEntityTypeImage.Image

The FROM clause in the Performance query is more complex than in the Alert or Availability queries. It begins with the information retrieved from vPerfDaily:

FROM Perf.vPerfDaily as vPerf

The query joins that to the performance rule view, using PerformanceRuleInstanceRowId as the foreign key:

INNER JOIN   vPerformanceRuleInstance 
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

The query then joins that to the vManagedEntity view, again using ManagedEntityRowId as the key:

INNER JOIN   vManagedEntity 
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRow

Once joined to the vManagedEntity view, the query joins to the vManagedEntityType view to retrieve the type for the managed entity:

INNER JOIN   vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 

The query also joins to the vManagementGroup view using the ManagementGroupRowId as the key:

INNER JOIN   vManagementGroup 
ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId

To retrieve performance information, the query needs to join the vRule and vPerformanceRule views to the vPerformanceRuleInstance view using the RuleRowId as the key in each case.

INNER JOIN   vRule 
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 
INNER JOIN   vPerformanceRule 
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

The query needs to join to the #InstanceList:

INNER JOIN   #InstanceList AS InstanceList 
ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)

The query needs to join to the #OptionList:

INNER JOIN   #OptionList As OptionList 
ON (InstanceList.Position = OptionList.Position)

Finally, the query joins the ObjectList to the InstanceList and to the vManagedEntity view using the ManagedEntityRowId as the key:

INNER JOIN   #ObjectList AS ObjectList 
ON (InstanceList.Position = ObjectList.Position) 
AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)

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'

Finally, the query joins the vDisplayString and vDisplayStringRule views to retrieve the necessary display strings for the report. The @LanguageCode is a parameter specified by the report designer.

LEFT OUTER JOIN   vDisplayString 
ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid 
AND vDisplayString.LanguageCode = @LanguageCode 

LEFT OUTER JOIN  vDisplayString vDisplayStringRule 
ON vRule.RuleGuid = vDisplayStringRule.ElementGuid 
AND vDisplayStringRule.LanguageCode = @LanguageCode

The WHERE clause is similar to the one in the Availability report. It retrieves all the data that falls between the two dates specified in the date picker:

WHERE   (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) 
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

The important points to note in this query are that the ManagedEntityRowId is a useful foreign key, and the format of the WHERE clause is similar to that used in the Availability report.

Here’s what the complete query looks like

SELECT   
vPerf.DateTime, 
vPerf.SampleCount, 
vPerf.AverageValue, 
vPerf.MinValue, 
vPerf.MaxValue, 
vPerf.StandardDeviation,
vPerformanceRuleInstance.RuleRowId, 
vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, 
vManagedEntity.ManagedEntityGuid, 
vManagedEntity.ManagedEntityDefaultName, 
vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) 
AS DisplayName,
vManagementGroup.ManagementGroupGuid, 
vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid, 
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,
vPerformanceRule.MultiInstanceInd,
OptionList.[Group], 
OptionList.GroupTitle, 
OptionList.Position, 
OptionList.ChartScale, 
OptionList.ChartType, 
OptionList.ChartColor, 
OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM     Perf.vPerfDaily as vPerf 
INNER JOIN   vPerformanceRuleInstance 
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId 
INNER JOIN   vManagedEntity 
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
INNER JOIN   vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId 
INNER JOIN   vManagementGroup 
ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId 
INNER JOIN   vRule 
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 
INNER JOIN   vPerformanceRule 
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId 
INNER JOIN   #InstanceList AS InstanceList 
ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)
INNER JOIN   #OptionList As OptionList 
ON (InstanceList.Position = OptionList.Position) 
INNER JOIN   #ObjectList AS ObjectList 
ON (InstanceList.Position = ObjectList.Position) 
AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId) 
LEFT OUTER JOIN  vManagedEntityTypeImage 
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId 
AND  vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' 
LEFT OUTER JOIN   vDisplayString 
ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid 
AND vDisplayString.LanguageCode = @LanguageCode 
LEFT OUTER JOIN  vDisplayString vDisplayStringRule 
ON vRule.RuleGuid = vDisplayStringRule.ElementGuid 
AND vDisplayStringRule.LanguageCode = @LanguageCode
WHERE   (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) 
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))