Creating Custom Reports

  • Article

Applies To: Operations Manager 2007 R2

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. See Kevin Holman’s blog for more information about Data Warehouse grooming: https://go.microsoft.com/fwlink/?LinkId=204249.

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.