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.
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.
|
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.
|
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.
No comments:
Post a Comment