.png)
Inside a Generic Report
Updated: December 6, 2010
Applies To: 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.
-
DATEPART(hh, @StartDate)returns the hour part of the@StartDatevariable. This value is increased by 1 to return the next full hour after the time specified in@StartDate. -
convert(varchar(8), @StartDate, 112)converts the@StartDateparameter to character data(varchar(8))in the format yyyymmdd; the112specifies that format. -
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)))
