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,

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

Inside a Generic Report

Inside a Generic Report

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 OperationsManagerDW Schema

The OperationsManagerDW Schema

The OperationsManagerDW database stores data for use in reports.  This is in contrast to the OperationsManager database, which stores data for use at the Operations Manager console. A large number of database views are provided in order to make queries easier to write, without requiring you to have detailed knowledge of the schema.

Datasets

There are five main groupings, or datasets, within the OperationsManagerDW database:

Alert

Contains records of alerts raised by the management pack rules and monitors.

Event

Contains records of event data collected by management pack rules.

Performance

Contains data regarding the performance of managed entities (objects monitored by Operations Manager), collected by management pack rules.

State

Contains data collected by monitors about the state of a managed entity.

ManagedEntity

Provides information about monitored objects, management packs, management groups, and relationships. The views in this dataset are typically joined to other views (Alert, Event, Performance, and so on) to provide the name of the monitored object, or to scope the query to a specific group, management pack, or management group.

Each of these datasets corresponds to a different category of reports in the operator console.

Database Views

The tables of the OperationsManagerDW database should never be accessed directly. Instead, a number of database views are provided for access. When structural changes are made to the OperationsManagerDW database, every effort is made to ensure that the views are consistent so that existing reports can be used without changes. The tables, however, may be changed, and these changes may cause existing reports that query the tables directly to no longer work properly.

Key Views for Each Dataset

Alert Dataset

Alerts are generated by rules and monitors in Operations Manager and are stored in the database together with their respective details and parameters. No aggregation or summarization is performed. Alert data can be seen in four views in the Data Warehouse database:

Alert.vAlert

This view contains general information about the alert: Name, Description, Severity, the workflow that created it, the time that it was created, and the managed entity that it is associated with. This view can be joined to the other alert views on the AlertGuid column. It may also be joined to the ManagedEntity, Rule, and Monitor views to retrieve additional details.

Alert.vAlertDetail

This view contains the Custom Field, Owner, and TicketID data for the alert. It can be joined to the Alert.vAlert view on the AlertGuid column.

Alert.vAlertResolutionState

This view contains information about each Resolution State that the alert has been in, when it was set, and how long it was in the state.  This view can be joined to the Alert.vAlert view on the AlertGuid column.

Alert.vAlertParameter

This view contains the value for each parameter in the alert. It can be joined to the Alert.vAlert view on the AlertGuid column.

Sample Alert Dataset Query

To retrieve the number of alerts generated yesterday, use the following Transact-SQL query:

SELECT COUNT (AlertName)

FROM

Alert.vAlert

WHERE DATEDIFF(dd,0,RaisedDateTime) = DATEDIFF(dd,0,GETDATE()-1)

Event Dataset

Events are collected from monitored objects by Event Collection Rules in Operations Manager and are stored in the database together with their respective details and parameters. No aggregation or summarization is performed. Event data can be seen in several views in the Data Warehouse database:

Event.vEvent

This is the main view for Events. It  contains the unique ID for the Event (EventOriginId), the date/time of the Event, the Event Number, and other unique IDs that can be joined to the other Event views to get the details of the Event.

Event.vEventDetail

This view can be joined to the Event.vEvent view on the EventOriginId column to provide the event description found in the EventData column.

Event.vEventParameter

This view can be joined to the Event.vEvent view on the EventOriginId column to provide the event parameters. This is generally the same information that is in the event description, with part of it in each parameter. Separate joins should be done from the Event.vEvent view for each parameter that is needed.

Event.vEventRule

This view contains the RuleRowId of the rule that generated the event and the ManagedEntityRowId of the managed entity that the alert came from. This view should be joined to the Event.vEvent, vRule, and vManagedEntity views to get these details.

vEventCategory

This view provides the event category information and can be joined on the EventCategoryRowId column in the Event.vEvent view.

vEventLevel

This view provides the event Level (Warning, Error, and so on) and can be joined on the EventLevelId column in the Event.vEvent view.

vEventUserName

This view provides the user name that was logged with the event and can be joined on the UserNameRowId column in the Event.vEvent view.

vEventChannel

This view provides the channel for the event. This will be either the name of the event log (Application, System, and so on) or a name supplied in a custom event collection rule. This view can be joined on the EventChannelRowId column in the Event.vEvent view.

vEventPublisher

This view provides the publisher for the event. This will be either the Source in the event log or a name supplied in a custom event collection rule. This view can be joined on the EventPublisherRowId column in the Event.vEvent view.

vEventLoggingComputer

This view provides the name of the computer that logged the event and can be joined on the LoggingComputerRowId column in the Event.vEvent view.

Sample Event Dataset Query

To retrieve the ten most frequently occurring events in the OperationsManagerDW database, use the following Transact-SQL query:

SELECT TOP 10 EventDisplayNumber, COUNT(*) AS Events

FROM Event.vEvent

GROUP BY EventDisplayNumber

ORDER BY Events DESC

Performance Dataset

Performance data is available in daily and hourly aggregates, which contain the minimum, maximum, average, and standard deviation of the values collected.  You will generally query either the Perf.vPerfDaily or Perf.vPerfHourly views and join them to other views to get the managed entity and rule information.  The vPerfRaw view can be queried (instead of the hourly or daily views) to get the value of each sample.  By default, this data is kept for a shorter time than the hourly and daily aggregated data. For optimal report performance, it is recommended that you use vPerfDaily or vPerfHourly instead of vPerfRaw. The Performance data views are as follows:

Perf.vPerfDaily and Perf.vPerfHourly

These views contain the hourly and daily aggregated performance data.  The minimum, maximum, average, and standard deviation of the values in the vPerfRaw view are calculated and stored in these views.  These views can be joined to the vManagedEntity table on the ManagedEntityRowId column to get the information for the object that the values were collected from. They can then be joined to the vPerformanceRuleInstance view on the PerformanceRuleInstanceRowId column to get the name of the performance object and counter and the rule that was used to collect them.

vPerformanceRuleInstance

This view contains the ID for the instance of the counter that was collected together with the IDs for the performance counters and the rules that collected them.  Use this view to link the daily/hourly aggregation views to the vPerformanceRule view on the PerformanceRuleInstanceId column, and then to the vRule view on the RuleRowId column.

vPerformanceRule

This view contains the counter and object names that were collected and can be joined to the PerformanceRuleInstanceRowId column in the vPerformanceRuleInstance view.

vRule

This view contains the name of the rule that collected the performance counter and can be joined on the RuleRowId column in the vPerformanceRuleInstance view.  It also contains a ManagmentPackRowId column which can be joined to the vManagementPack view to get the information about the management pack that contains the rule.

Sample Performance Dataset Query

To retrieve the top ten performance rule instances being monitored, use the following query:

SELECT TOP 10 PerformanceRuleInstanceRowId, COUNT(*) AS Count

FROM Perf.vPerfDaily

GROUP BY PerformanceRuleInstanceRowId

ORDER BY Count DESC

State Dataset

The State data views contain data on how long any given monitor was in each possible state (Healthy, Warning, Critical, and so on). The data is summarized for each object and aggregated daily and hourly.  The State data views are described and illustrated below:

vStateHourlyFull and vSTateDailyFull

These views contain the hourly and daily aggregated state data and contain data on how long each monitor/managed entity combination was in each possible state.  The MonitorRowId and ManagedEntity columns can be joined to the vMonitor and vManagedEntity views to get information about the monitor that changed the state and the managed entity that the state was changed for. It is not recommended that you use these views in Availability reports; use State.vStateHourly and State.vStateDaily instead.

State.vStateHourly and State.vStateDaily

These views are derived from the vStateHourlyFull and vStateDailyFull and are used in availability reports. These views can be joined to the vManagedEntityMonitor on the ManagedEntityMonitorRowId column.

vMonitor

This view contains the information about the monitor that caused the state change and can be joined on the MonitorRowId column of the vStateHourlyFull and vStateHourlyDaily views.

Sample State Dataset Query

To retrieve the ManagedEntityMonitorRowID for any entity that has been in the red (Critical) state for more than one minute, use the following query:

SELECT ManagedEntityMonitorRowId

FROM State.vStateHourly

WHERE InRedStateMilliseconds > 60000

Managed Entity Dataset

The managed entity dataset provides information about monitored objects, management packs, relationships, and management groups.  These views are typically joined to other views (Alert, Event, Performance, or State) to provide the name of the monitored object or to scope the query to a specific group, management pack, or management group.

vManagedEntity

This view provides the names for all monitored objects in the OperationsManagerDW database.  This view is generally joined to views from the other datasets to provide the name of the object that the collected data applies to. This view can also be joined to the vManagedEntityType view on the ManagedEntityTypeRowId column to scope the list of objects down to a specific type of object, to the vRelationship view on the ManagementGroupRowId column to scope the list of objects down to a specific group, and to the vManagementGroup view on the ManagementGroupRowId column to scope the list of objects to a specific management group.

vManagedEntityType

This view provides information about the types of objects that occur in the vManagedEntity view (operating system, database, and so on).  This view can be joined to the vManagedEntity view on the ManagedEntityTypeRowID column.  This view can also be joined to the vManagementPack view on the ManagementPackRowId column to show which management pack defines each type of object.

vManagementPack

This view provides information about the Management Packs in the Operations Manager environment and can be joined to the vManagedEntityType, vMonitor, and vRule views.

vRelationship

This view provides relationship information, which can be used to retrieve group membership for Managed Entities.  This view can be joined to the vManagedEntity view on the SourceManagedEntityRowId and TargetManagedEntityRowId columns.

vRelationshipType

This view provides information about the type of relationships identified in the vRelationship view and can be joined to other views on the RelationshipTypeRowId column.

vManagementGroup

This view provides information about Management Groups that send data to the Data Warehouse and can be joined to several other views on the ManagementGroupRowId column.

The OperationsManagerDW Schema

The OperationsManagerDW Schema

The most important part of writing a custom report is generating the correct query to extract the information you need from the OperationsManagerDW database. To write the correct query, you need to understand the database schema. Reports should only query the database views built into OperationsManagerDW; you should not access the tables directly.

In This Section

The OperationsManagerDW Schema

Before you can start to write a custom report, you need to know what views in the OperationsManagerDW database hold the information you want to retrieve. For custom queries, you only need to access a subset of the schema. This section covers this subset of the schema and introduces the views that you’ll need to access in order to write a custom query.

Inside a Generic Report

A good way to become familiar with the SQL queries you’ll need in order to create custom reports is to look inside one of the published reports that are installed with the Operations Manager Management Pack. This section examines the SQL queries in several of the reports and explains how they function, which will enable you to build your own queries.

Creating Custom Queries

Once you understand the schema and how the queries work in the existing reports, this section shows you how to write your own custom report query. This section will use multiple examples, starting with a basic query that retrieves just one counter across several computers, and then moving to more complex examples. Although these examples address specific custom reporting needs, the intent is that you will be able to use them as a model to create your own custom queries.

Custom Report Parameters

Operations Manager reports contain a parameter area that allows users to specify the bounds of the database query with respect to target objects, monitoring times, and other options. You can define what parameters are available in your custom report, and populate those parameters with the default data that users are most likely to want. This section explains how to customize the parameter area of your custom report.

Localizing Reports

This section describes how to localize management packs for multiple language support.

Using Stored Procedures in a Custom Report

Stored procedures provide more convenience and security than using embedded Transact-SQL queries in a report, but they also require some additional configuration. This section describes how to create stored procedures, use them in reports, and create scripts to enable stored procedures to be distributed with management packs.

Using a Report Builder

Unlike linked reports, custom reports require that you design the report using a report designer, either in SQL Server Business Intelligence Development Studio (BIDS), Visual Studio Report Developer, or SQL Server Report Builder. This section explains how to use BIDS to create clear and useful reports.

Operations Manager 2007 Report Authoring Guide

Operations Manager 2007 Report Authoring Guide

This guide provides the details for creating a custom report in Operations Manager 2007 R2. You should use this guide if you have reporting requirements that cannot be addressed with one of the reports provided in the management packs that you have installed in your management group or by one of the generic reports that are installed by default.

Related Content

The Operations Manager 2007 Reporting Guide provides general information on the reporting feature and describes how to perform functions such as running or scheduling an existing report.

The Management Pack Authoring Guide provides complete documentation on Linked Reports which allow you to include an existing report customized for your application in your management pack.

Overview of Custom Reports

Custom reports are intended for advanced users who are comfortable creating their own reports by using Microsoft SQL Server Reporting Services and SQL Server Business Intelligence Development Studio, SQL Server Reporting Services Report Builder, or Microsoft Visual Studio Report Designer.

If report types using prebuilt queries do not deliver the reporting data that you need, use custom reports by creating the queries yourself to retrieve just the information you want. For example, you can create a query that consolidates the data from multiple objects into a single report. You can then customize the appearance of the report, using one of several tools or programs that include Microsoft SQL Server 2008 Business Intelligence Development Studio, Visual Studio Report Designer, Microsoft SQL Server Reporting Services Report Builder, or Excel, to fit the needs of your organization. In addition, you can localize custom reports for multiple languages. After you have deployed the report, you and other users can run the report like any other report.

Considerations for Choosing the Appropriate Type of Report

Before you create a custom report, you must first decide what information you need in the report. Determine what counters you want to monitor, and what parameters you want to report on. For example, if you are reporting on availability, what time period should the report cover? If you are reporting on performance, what is the acceptable threshold for the counter? Next, determine the appearance of the report. Simple numeric data might be sufficient, or you might require a more advanced graph that uses customized images, such as your corporate logo.

After you have concluded these determinations, collect feedback from users who will use this report. Their input might lead you to revise the report design.

·      If your report concerns one type of data, such as an Availability Report, see the existing Generic Reports. All counters and performance data stored in the data warehouse are available through one of the generic reports.

·      If your report uses specific parameters that users should not change, for example, availability data over the last week, use a linked report. Both generic and linked reports can be created and used without knowledge of SQL Server or the data warehouse. For more information about linked reports, see Linked Reports.

·      If your report uses multiple types of objects, or multiple types of data, such as performance and availability in a single report, or if you need a report that has a customized appearance, use a custom report. Because custom reports require a higher level of familiarity with Transact-SQL and the data warehouse schema, we recommend that you only use a custom report if your report has these requirements and you are an advanced user.

Prerequisites for Custom Reports

To write a custom report, you must install the following services and programs on your report development server:

·      System Center Operations Manager 2007 Reporting Services

·      SQL Server 2005 Reporting Services or SQL Server 2008 Reporting Services

·      SQL Server 2005 Business Intelligence Development Studio or SQL Server 2008 Business Intelligence Development Studio, SQL Server 2005 Reporting Services Report Builder or SQL Server 2008 Reporting Services Report Builder, or Visual Studio 2008 Report Designer

Overview of Custom Report Authoring

To write a custom report, follows these basic steps:

1.   Write a Transact-SQL query that retrieves the information that you need.

2.   Add parameters to the query for operators to use to run the report.

3.   Design the report in SQL Server Report Designer or Visual Studio Report Designer.

4.   Optionally, load the report definition and Report Definitions Language (RPDL) file into a management pack for distribution.

5.   Import the report or the management pack.

In This Section

Setting up the Environment

Describes the installation requirements for setting up Operations Manager 2007 Reporting Services and explains how to create a read-only user and how to create a new data source.

Creating Custom Reports

Provides the background and walkthroughs for creating custom reports and describes how to write a custom query, how to use the Business Intelligence Development Studio, and how to set up the report parameters area.

Deploying Reports

Describes how to deploy reports to the Operations Manager Reporting Server or to save to custom management packs for deployment to other Operations Manager installations.

Setting up the Environment

Operations Manager 2007 Reporting requires that you install Microsoft SQL Server 2005 or SQL Server 2008 and SQL Server Reporting Services (SSRS). Because Operations Manager 2007 Reporting includes SQL Server Reporting Services, you do not need to install it separately. For more information about Operations Manager 2007 Reporting, see the Operations Manager 2007 Deployment Guide in the System Center Operations Manager 2007 R2 Technical Library. The default configuration of SQL Server 2005 and SQL Server 2008 includes Business Intelligence Development Studio (BIDS).

BIDS is a subset of Microsoft Visual Studio 2008 designed specifically for use with SQL Server 2008. It has various tools for SQL Server users including the robust Report Designer. You can create custom reports for use in Operations Manager with a variety of tools that include SQL Server Reporting Services Report Builder, Visual Studio, and Microsoft Excel. This guide focuses on BIDS, but you can use the tool that you are most comfortable with. For more information about the tools in BIDS, see Introducing Business Intelligence Development Studio in the SQL Server 2008 R2 Books Online (http://technet.microsoft.com/en-us/library/ms173767.aspx).

If you already have Visual Studio 2008 installed on your Operations Manager Reporting server, Business Intelligence Design Studio uses Visual Studio instead.

A common practice for authoring reports is to create a read-only account in SQL Server that enables multiple authors to access the OperationsManagerDW database to test their reports without giving each author individual access. To create a read-only data source, you first must create a database user that only has Read permissions to the OperationsManagerDW database.

To create a database user that has only Read permission

1.   Open Microsoft SQL Server Management Studio (SSMS) and connect to the database server. 2.   In Object Explorer, right-click the server name, and then click Properties. When the Server Properties dialog box opens, click the Security tab. 3.   In the Server authentication section, ensure that SQL Server and Windows Authentication mode is selected. Click OK. You might have to restart SQL Server for the change to take effect. Close the Server Properties dialog box. 4.   In Object Explorer, expand Security, right-click Logins, and then click New login. The Login – New dialog box appears. Enter a login name, select SQL Server Authentication, and enter a password. Clear the User must change password at next login check box. 5.   Select the User Mapping page on the left. In the Users mapped to this login box, select OperationsManagerDW. The box at the bottom of the dialog box changes to Database role membership for: OperationsManagerDW. Select the db_datareader role. Click OK.

As an alternative to creating a database user that has Read-Only permission, especially in environments where operating in mixed mode is not desired, you can create a login for each operator’s Active Directory account, and then grant that login db_datareader role membership on the OperationsManagerDW database.

To set up BIDS to create reports, you must create a new data source to connect to the reporting data warehouse.

To create a new report project

1.   On the Start menu, point to SQL Server 2008, and then click SQL Server Business Intelligence Development Studio. 2.   On the Start page, click File, click New, and then click Project, or click the Create: Project link. This opens the New Project dialog box. 3.   In the Project types pane, ensure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project. In the Name box, enter a name for the project. Change the location where the project is stored or accept the default. Click OK. The new report project is now ready for you to create a data source.

To create a new data source

1.   After you create a new report project, the Solution Explorer and Properties panes appear on the right side of the Start page. In the Solution Explorer, right-click Shared Data Sources, and then click Add New Data Source. 2.   In the Shared Data Source Properties dialog box, enter a Name for the data source. Leave the Type set to Microsoft SQL Server. Click the Edit button next to the Connection String. The Connection Properties dialog box appears. 3.   In the Server name box, enter the name of the server where the reporting data warehouse is installed. If you created a user that has Read-Only permissions, in the Log on to the Server section, select the Use SQL Server Authentication button, and enter the credentials for the SQL Server Login that you created. If not, use whatever authentication is appropriate in your environment. In the Connect to a Database section, select the Select or enter a database name  button, and then select OperationsManagerDW from the box. If you do not see OperationsManagerDW, verify that you have selected the correct server and authentication. 4.   Click OK. The connection string in the Shared Data Source Properties dialog box will be filled in for you. Click OK to close this dialog box. The data source now appears in the Properties pane, with the extension .rds. 5.   To make the data source available to other projects, right-click the data source in the Solution Explorer and click Properties. The Property Pages window for this data source opens. Click TargetServerURL and enter the URL for the report server where the project is deployed. Click OK.  6.   Right-click the data source in the Solution Explorer and click Deploy. The Output window shows the progress of the deployment. The data source is now ready to use with a custom report.

Creating Custom Reports

If the generic reports provided with Operations Manager do not meet your needs, you can create a custom report. For example, you may need to report on multiple types of objects or multiple types of data, such as performance and availability, in a single report; or, you may need a report that has a customized appearance. The following sections will show you how to create a SQL query that retrieves the information you want, design the report in a report design tool, and set up parameters so operators can customize the report.

Operations Manager Databases

Operations Manager uses two separate databases:

OperationsManager database

This database stores alerts, discovered inventory, performance data, state data, events, and metadata that is collected by the agents and passed to the Operations console. It also contains information about the management group and management packs. This is information that the operator needs to see and respond to in real time. Any data viewed in the Operator’s Console outside the reporting workspace is stored in this database.

OperationsManagerDW database

This database stores data for use in reporting. By default, performance data is sent to this database, as are alerts, events, and state information. This database is updated regularly with the latest information from the OperationsManager database to ensure that the data is current.

The OperationsManagerDW database, also called the Data Warehouse, is the focus of this section. The OperationsManagerDW database is optimized for reporting.  It stores raw data from the monitors and rules for a limited time only. By default, to improve performance, reports only access aggregated data. There are daily and hourly aggregations.