Tivoli reporting integration: IBM Tivoli Monitoring and TADDM
This chapter describes integrating IBM Tivoli Common Reporting for Asset and Performance Management V1.3 with IBM Tivoli Monitoring and TADDM to show you how to create reports using data collected by various Tivoli products. IBM Tivoli Common Reporting for Asset and Performance Management V1.3 is contains embedded IBM Cognos 8 technology.
|
Important: The framework model used in these scenarios can be downloaded from the ITSO FTP site. For downloading instructions, see Appendix A, “Additional material” on page 803. This framework model is included on an “as-is” basis and not officially supported. It is provided with this book to give you a preview of the planned product functionality for Tivoli Common Reporting integration.
|
This chapter contains the following topics:
8.1 Scenario overview
Tivoli products gather large numbers of data in their own databases. Each product or solution maintains a separate database instance, which contains its own schema that has the purpose to fulfill requirements of the product for storage of the data. Although delivering a value for the user based on the data gathered within the one product is easy, the challenge is to deliver additional value based on the data taken from multiple products (data sources). The scenario in this chapter describes this kind of integration. Data gathered by two products are integrated through a reporting engine. These products are IBM Tivoli Monitoring data, gathered by operating system agents, and IBM Tivoli Application Dependency Discovery Manager data.
By having cross-product data virtually integrated and having access to them from one location, a user is able to find the answers to additional questions from IT management area and make more confident business decisions. Cross-product data integration provides additional facts that cannot be derived from a database of a single product. The Tivoli portfolio contains a reporting product, IBM Tivoli Common Reporting for Asset and Performance Management, which helps build a cross-product reporting solution.
Manual browsing through the database structure is a difficult task for someone who does not know how the product is implemented and what is the mechanism for storing the data. IBM Tivoli Common Reporting for Asset and Performance Management can cover a database structure with a user interface in which reporting against data gathered by various products becomes easier.
8.2 Products involved
Cross-product reporting integration is performed on two levels: product database level and reporting engine level. IBM Tivoli Common Reporting for Asset and Performance Management server is the central point of the solution. The server is connected to the separate databases that are used by the Tivoli products. This way creates opportunity to access data gathered by separate Tivoli products from one, central location. Cognos BI Reporting engine, which is running behind the IBM Tivoli Common Reporting for Asset and Performance Management server, provides mechanisms to join the data from separate data sources and, based on that information, generate reports.
The scenario in this chapter shows a sample cross-product reporting solution that is based on two Tivoli products:
•IBM Tivoli Monitoring 6.2.2 Fix Pack 2
•IBM Tivoli Application Dependency Discovery Manager 7.2
The element that connects those two products is IBM Tivoli Common Reporting for Asset and Performance Management server (also known as IBM Tivoli Common Reporting 1.3). It includes embedded IBM Cognos 8 technology. This reporting solution is available for free at the IBM Tivoli Monitoring version 6.2.2 Fix Pack 2 users.
8.3 Benefits
The following list summarizes general benefits of cross-product reporting solution and IBM Tivoli Common Reporting for Asset and Performance Management platform:
•Revealing new facts. Cross-products reporting reveals information that already exists in separate databases. However, without suitable linkage the information is meaningless or hardly accessible for the product’s users. It is not possible to deliver one product that addresses all the questions related to IT infrastructure that are being asked by the users. However, based on the data and reporting integration, we can get the answers to more questions by using the products that we already have. Data level integration “glues” products together as a component and delivers additional value through the reports.
•Integration of various products. IBM Tivoli Common Reporting for Asset and Performance Management provides a vehicle on which products developed in separate technologies can be easily integrated on the databases level. Cross-product solution enables users to rapidly ask questions of multiple data sources.
•No database skills are required for business users. Cognos models facilitate access to information that is gathered by Tivoli products. Users without SQL skills and knowledge of the structure of the product’s database can easily get the data and analyze it, based on individual needs. Cognos models provide understandable names for database tables, views, and columns.
•Single access point for multiple data sources. Tivoli Common Reporting server can be used as a single entry point to all the IT data that is gathered in the company.
•Performance analysis by application. This integration can help you understand, for a period of time, when there is a performance problem and also see other relevant metrics for the other application components.
Sample analyses can be performed by means of the IBM Tivoli Monitoring and TADDM integrated reporting solution, summarized in the following list:
•Show CPU, memory, disk usage by a manufacturer or a model of the server. You may select computer systems discovered by TADDM, group them by a manufacturer and by model, join through the common dimension with IBM Tivoli Monitoring data. The computer system is the join-point. In addition, you may display performance metrics (such as average CPU utilization), aggregated for groups introduced in TADDM selection.
•Show CPU, memory, disk usage by an operating system type. Group computer system data in any way based on the information gathered in TADDM, for example by operating system name and version. Check aggregated average CPU utilization for this grouping.
•Check the resources utilization for application servers and databases. You may select application server objects discovered by TADDM. These are web and application servers, databases, custom servers, and so on. Filter the applications from your area of interest, for example DB2 databases instances that you are administering. Select computer systems on which these databases are running. Join the servers through common dimension with monitoring data. Again computer system attributes such as host name are the join-point between two products. Display average disk utilization for machines and indirectly for databases selected from TADDM. Find the database instance which will hit not enough disk space issue before the problem occurs. Perform the same steps for your web servers and check average CPU and memory utilization of the machines on which your servers are running.
•Show CPU utilization and energy consumption. Compare systems from different server makers and manufacturers in terms of CPU utilization and energy consumption.
8.4 Architecture of the integration
Figure 8-1 presents all the components that are used in the integration scenario. The main components are:
•Tivoli Data Warehouse, which is used by the IBM Tivoli Monitoring solution
•Database instance, which works in the background of Tivoli Application Dependency Discovery Manager
•IBM Tivoli Common Reporting for Asset and Performance Management server (IBM Tivoli Common Reporting 1.3).
Figure 8-1 Integration architecture
The scenario assumes that IBM Tivoli Monitoring, Tivoli Application Dependency Discovery Manager, and IBM Tivoli Common Reporting server are already installed. All additional steps that are required to set up the integration are covered in this chapter.
Tivoli Data Warehouse contains additional schema called IBM_TRAM (IBM Tivoli Reporting and Analytics Model), which contains dimensions data (time dimension table and ComputerSystem dimension), common data between IBM Tivoli Monitoring operating system agents and TADDM application. The IBM Tivoli Common Reporting for Asset and Performance Management server is installed on a separate machine. It is connected to the databases of the products through the database native client (DB2 client).
JDBC connections are not supported in Cognos BI Reporting engine, which is used internally by IBM Tivoli Common Reporting for Asset and Performance Management sever. DB2 client has those two databases (IBM Tivoli Monitoring and TADDM) cataloged. Cognos engine uses the connections from that catalog. Additional tools are responsible for populating and maintaining common dimension tables. Cross-product model, which is deployed on Cognos BI Reporting server, is an abstraction layer on top of the databases schema. This entry point is for the reporting in Cognos. The user builds reports or uses an ad-hoc reporting feature based on that model.
Cognos engine operates on the model that contains objects from both Tivoli Data Warehouse and TADDM databases. Joining between those databases is implemented in the IBM_TRAM schema, particularly in this case, in ComputerSystem table. This table contains computer systems that are common for both products. The computer systems are both monitored by IBM Tivoli Monitoring operating system agents and discovered by TADDM.
The ComputerSystem table with common data is populated by an additional tool that was created for this integration scenario. At this time, products that are involved in the integration do not provide capabilities to automatically populate common dimensions. The additional tool is an external Java program that connects to three databases (Tivoli Data Warehouse, TADDM, and database on which IBM_TRAM schema is installed). The program finds the common computer systems (machines) between Tivoli Data Warehouse and TADDM and stores the results in the IBM_TRAM.ComputerSystem table. The utility must be run periodically to refresh joint point, which is the ComputerSystem table.
Figure 8-2 shows a sample report that was generated, based on data taken from two products: TADDM and IBM Tivoli Monitoring. Detailed information about the hosts configuration (operating system name, manufacturer and model of the hardware, CPU type) come from TADDM database. Average percentage CPU utilization is received from Tivoli Data Warehouse view. The utilization value is additionally aggregated by Cognos BI Reporting engine: the average value is computed for all days that the data is stored in the warehouse. The data is integrated through the IBM_TRAM schema. Matching is done for the host names of the computer systems.
Figure 8-2 Sample cross-product report
|
Note: This report is an ad-hoc report. It was created in the Query Studio component, taking less than one minute to select the columns and join them together. Tables and views were glued by Cognos BI Reporting engine according to the rules defined in the model. Figure 8-3 on page 204 presents a part of the cross-product reporting model, which is provided as an additional download material for this chapter. Model objects can be grouped into three categories: TADDM domain, IBM Tivoli Monitoring domain, common domain (IBM_TRAM).
|
How does the cross-product join work? Elements from various products are connected to common dimensions. In our case, common dimension is the IBM_TRAM.ComputerSystem table.
Cross-product-join behind the report can be converted to the following SQL conditions:
•[TADDM].BB_COMPUTERSYSTEM40_V.FQDN_C = [IBM_TRAM].ComputerSystem.FQDN
•[IBM_TRAM].ComputerSystem.ManagedSystemName = [TDW].ManagedSystem.DisplayName
•[TDW].ManagedSystem.ManagedSystemName = [TDW].NT_System_DV.Server_Name
BB_COMPUTERSYSTEM40_V view from TADDM has the relationship with ComputerSystem table from IBM_TRAM schema. MANAGEDSYSTEM table from Tivoli Data Warehouse also has a relationship with ComputerSystem table from IBM_TRAM schema. All views from IBM Tivoli Monitoring with metrics (such as NT_System_DV, NT_System_HV, System_DV, System_HV, KLZ_CPU_HV) have relationships with MANAGEDSYSTEM table.
Additional relationships between metrics views and TIME_DIMENSION table (for example [TDW].NT_System_DV.WRITETIME = [IBM_TRAM].TIME_DIMENSION.CANDLETIMESTAMP) introduce the possibility of filtering data by custom date ranges, executing the reports for a specific period of time only.
Figure 8-3 Cross-product model fragment
Figure 8-4 on page 205 shows the origination of each column in the report by mapping them on the query subjects from model. In certain cases, the full path to database query subjects is presented. The diagram was generated by Cognos BI Reporting engine.
Figure 8-4 Database views and tables behind the sample report
8.5 Implementation steps
This scenario assumes that IBM Tivoli Monitoring and TADDM products are already installed and configured. There is no need to perform additional configuration steps on TADDM server. The only prerequisite is to have several servers that are monitored by IBM Tivoli Monitoring agents and were discovered by TADDM. This means, information about the same machine must exist in the Tivoli Data Warehouse and TADDM database.
In case of IBM Tivoli Monitoring, enabling historical data collection for some attributes is required. For information about which attributes to configure, see 8.5.2, “Configuration of the products” on page 209.
The scenario also assumes that IBM Tivoli Common Reporting for Asset and Performance Management server is already installed. The server can be installed on an additional machine, but it can also be running on one of the machines on which other products participating in the scenario are installed. IBM Tivoli Common Reporting for Asset and Performance Management installation is covered in the Information Center for the product:
For this integration, a sufficient approach is to select “Single-computer installation” scenario (described in the document at the website) and proceed with basic setup (without “BIRT-based Tivoli Common Reporting engine” and LDAP setup).
Scenario implementation consists of the following steps, which are detailed in this section:
1. Database connections setup
2. Cross-product database structures creation
3. Common dimensions population with data
4. Cross-product model installation and publication
8.5.1 Database native client
In addition to the IBM Tivoli Common Reporting for Asset and Performance Management server on the same machine database, native client should be installed and configured with remote databases information. IBM Tivoli Common Reporting 1.3 introduces Cognos BI Reporting engine; this integration scenario is based on that reporting engine. Cognos does not support JDBC connections, which were used in the previous version of IBM Tivoli Common Reporting by BIRT reporting engine. It uses native connection drivers such as DB2 client.
To set up a data source for DB2 in Cognos BI Reporting, you must install the DB2 client application on the machine where the IBM Tivoli Common Reporting for Asset and Performance Management server is installed. Another requirement is to set up (catalog) a connection to the remote databases within that DB2 client. The installation is not required if IBM Tivoli Common Reporting 1.3 server is installed on the same machine on which Tivoli Data Warehouse or TADDM databases are running. In this case, one of the databases is local and it is already cataloged. It is only required to catalog a connection to the second remote database.
This section explains the DB2 Client installation and configuration. We will be acting in the environment when Tivoli Data Warehouse and TADDM DB2 databases are remotely accessible for the IBM Tivoli Common Reporting 1.3 server machine.
Perform the following steps to set up the native connections:
1. Download the DB2 client application package. Select the version that matches your database. Download the application from either of the following websites:
– IBM support and download site
• DB2 9.1:
• DB2 9.5:
For example, the IBM Data Server Client for Windows file:
– IBM Fix Central site
i. Select group Information Management, product DB2, correct database version and platform.
ii. Browse for the client application
For example:
DB2-nt32-client-9.5.400.576-FP004
DB2 9.5 Fix Pack 4 for Windows/x86-32 (32 bit), IBM Data Server Client
2. Install the client application.
|
Note: On UNIX or Linux machines it is necessary to create manually additional operating system user for DB2 administration purposes and create DB2 instance associated with that user. On Windows machine installation program will do all the required work for you.
Instance creation is required even though a real database is not going to be created.
|
For Windows platform, the installation wizard guides you through the installation process.
For UNIX or Linux platform, use the following steps to add the user and create a new DB2 instance:
a. Install the client application by invoking db2_install script first.
b. As a root user, create a new operating system user (Example 8-1).
Example 8-1 Creating a new user for database administration
nc043243:~ # groupadd db2grp1
nc043243:~ # useradd -d /home/db2inst1 -g db2grp1 db2inst1
nc043243:~ # passwd db2inst1
Changing password for db2inst1.
New Password:
Reenter New Password:
Password changed.
c. As a root user create a new database instance - issue db2icrt command. Associate the instance with the user created (Example 8-2).
Example 8-2 Creating DB2 instance
nc043243:~ # cd /opt/ibm/db2/V9.5/instance/
nc043243:/opt/ibm/db2/V9.5/instance # ./db2icrt -u db2inst1 db2inst1
DBI1070I Program db2icrt completed successfully.
3. Add remote databases to the DB2 client catalog:
a. Open the command line for DB2 (see Figure 8-5 on page 208):
On Windows: Start → All Programs → IBM DB2 → DB2COPY1 (Default) → Command Line Tools → Command Window
Figure 8-5 Sample DB2 client command-line window
On UNIX or Linux, log in as the database user (for example execute su db2inst1).
b. Catalog the database instance node by executing the following command:
db2 CATALOG TCPIP NODE <node-name> REMOTE <hostname-or-IP-address> SERVER <service-name-or-port-number> REMOTE_INSTANCE <instance-name> SYSTEM <system-name>
c. Catalog the database by executing the following command
db2 CATALOG DATABASE <database-name> AS <alias> AT NODE <node-name>
d. Test connection to the database by issuing a sample select SQL query. For Tivoli Data Warehouse and TADDM databases, see Example 8-3 and Example 8-4 on page 209.
e. Repeat the commands (step 3 on page 207) for Tivoli Data Warehouse database and for TADDM database. Remember the aliases under which you have cataloged your databases connections because you will use them later in configuration for IBM Tivoli Common Reporting for Asset and Performance Management server.
Example 8-3 Adding Tivoli Data Warehouse to the client’s catalog
C:Program FilesIBMSQLLIBBIN>db2 CATALOG TCPIP NODE TDWNODE1 REMOTE 9.42.171.253 SERVER 60000 REMOTE_INSTANCE db2inst1 SYSTEM TDWSYS1
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
C:Program FilesIBMSQLLIBBIN>db2 CATALOG DATABASE WAREHOUS AS TDWDB1 AT NODE TDWNODE1
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
C:Program FilesIBMSQLLIBBIN>db2 CONNECT TO TDWDB1 USER db2inst1 USING itso4you
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TDWDB1
C:Program FilesIBMSQLLIBBIN>db2 select count(*) from "ITMUSER"."NT_System_DV"
1
-----------
177
1 record(s) selected.
Example 8-4 shows adding TADDM database to the catalog
Example 8-4 Adding TADDM database to the client’s catalog
C:Program FilesIBMSQLLIBBIN>db2 CATALOG TCPIP NODE TADDMN1 REMOTE 9.42.171.157 SERVER 50000 REMOTE_INSTANCE db2inst1 SYSTEM TADDMS1
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
C:Program FilesIBMSQLLIBBIN>db2 CATALOG DATABASE DEMO AS TADDMDB1 AT NODE TADDMN1
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.
C:Program FilesIBMSQLLIBBIN>db2 CONNECT TO TADDMDB1 USER db2inst1 USING smartway
Database Connection Information
Database server = DB2/LINUX 9.5.0
SQL authorization ID = DB2INST1
Local database alias = TADDMDB1
C:Program FilesIBMSQLLIBBIN>db2 select count(*) from BB_COMPUTERSYSTEM40_V
1
-----------
2790
1 record(s) selected.
8.5.2 Configuration of the products
To implement this integration scenario, we do not have to introduce significant configuration changes in the products. The common data must be for the machines that are discovered by TADDM and monitored by Tivoli Monitoring. For example, if there is data for a machine that is discovered by TADDM, but this machine is not monitored by Tivoli Monitoring, the data about this machine is not taken into account for this scenario.
For the TADDM application, you do not have to perform extra steps except for obtaining access information to the database. The list of views that are required by the solution should have the following data (as a result of TADDM discovery scans):
•BB_COMPUTERSYSTEM40_V
•BB_COMPUTERSYSTACES_76E84D2AJ
•BB_IPINTERFACE96_V
•BB_IPADDRESS73_V
•BB_L2INTERFACE41_V
For IBM Tivoli Monitoring, configuring historical data collection for some attributes is necessary. By enabling data collections for attribute groups special tables and views are created in Tivoli Data Warehouse. The views that are used to set up the scenario are in the following list. Not all views are required, but at least one set of views must be for one agent (Windows, Linux or UNIX). You do not need to have all the agent types in your environment.
•Linux Agent
– Linux_Machine_Information_DV
– Linux_IP_Address_DV
•UNIX Agent
– Machine_Information_DV
– UNIX_IP_Address_DV
– System_DV
•Windows Agent
– NT_Computer_Information_DV
– NT_System_DV
Instructions for enabling attribute groups for historical data collection is in the Information Center for IBM Tivoli Monitoring:
8.5.3 Databases setup
The integration scenario described in this chapter is based on two databases:
•Tivoli Data Warehouse database, which is used by the IBM Tivoli Monitoring solution,
•Tivoli Application Dependency Discovery Manager database.
Configuration steps described in this section are based on IBM DB2 databases only. However similar steps can be applied to other databases (such as Oracle or MSSQL) that are supported by IBM Tivoli Monitoring and by IBM Tivoli Application Dependency Discovery Manager.
Tivoli Data Warehouse setup
Create and populate data for the MANAGEDSYSTEM table within IBM Tivoli Monitoring data warehouse schema (by default ITMUSER schema). This additional table is introduced for IBM Tivoli Monitoring operating system agents model and reports package for IBM Tivoli Common Reporting for Asset and Performance Management. The table contains a list of all machines that are monitored by types of operating system agents (Windows, Linux, and UNIX). The table is a single access point to data gathered by three types of agents.
Follow the instructions provided with IBM Tivoli Monitoring 6.2.2 Fix Pack 2 to create and populate additional table:
We present sample steps of the instructions from IBM Tivoli Monitoring Information Center. The example is executed on a Windows machine and it is based on the DB2 client (installation and configuration is explained in 8.5.1, “Database native client” on page 206). DB2 command window is used to connect to the remote database and execute all the necessary steps.
Perform the following steps:
1. Download IBM Tivoli Monitoring 6.2.2 Fix Pack 2 Agent Reports package and extract it on the machine on which you have DB2 client and IBM Tivoli Common Reporting for Asset and Performance Management server installed.
Download the package from the IBM support and downloads site:
The package is required only for the purpose of obtaining the following file:
6.2.2-TIV-ITM_TMV-AgentReports-FP0002.tar
The agent reports package can be also obtained from IBM Passport Advantage® site. The package name is: IBM Tivoli Monitoring v6.2.2 Fix Pack 2 Agent Reports, Multiplatform, English (part number CZH3MEN).
The package contains all the necessary scripts that are used in this procedure. The scripts can be found in the db_scriptsdb2 subdirectory of the archive.
2. Open DB2 command line by clicking Start → All Programs → IBM DB2 → DB2COPY1 (Default) → Command Line Tools → Command Window. See Figure 8-6.
Figure 8-6 Sample DB2 client connection to Tivoli Data Warehouse database and resources script execution
3. Connect to the Tivoli Data Warehouse database by executing the following command (Example 8-5) in the window. Be sure you have administrative access rights to create new table within the ITMUSER schema:
db2 CONNECT TO <database-alias> USER <user-name> USING <password>
Example 8-5 Establishing connection to Tivoli Data Warehouse database
C:Program FilesIBMSQLLIBBIN>db2 CONNECT TO TDWDB1 USER db2inst1 USING itso4you
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TDWDB1
C:Program FilesIBMSQLLIBBIN>
db2 -tf gen_resources.db2
If you have other than the default schema name in Tivoli Data Warehouse database (different from ITMUSER), edit the script and update it with your schema name. Replace all 'ITMUSER' occurrences in the script with the correct value.
Example 8-6 Creating MANAGEDSYSTEM table
C:Program FilesIBMSQLLIBBIN>db2 -tf C:Integration6.2.2-TIV-ITM_TMV-AgentReports-FP0002db_scriptsdb2gen_resources.db2
DB20000I The SQL command completed successfully.
...
DB20000I The SQL command completed successfully.
C:Program FilesIBMSQLLIBBIN>
5. Create the stored procedure that will populate the MANAGEDSYSTEM table (see Example 8-7). Execute the populate_resources.db2 script:
db2 -td@ -vf populate_resources.db2
If you have other than the default schema name in Tivoli Data Warehouse database (different from ITMUSER), edit the script and update it with your schema name. Replace all ‘ITMUSER’ occurrences in the script with the correct value.
Example 8-7 Creating population procedure
C:Program FilesIBMSQLLIBBIN>db2 -td@ -vf C:Integration6.2.2-TIV-ITM_TMV-AgentReports-FP0002db_scriptsdb2populate_resources.db2
create procedure ITMUSER.POPULATE_OSAGENT(IN MSNCOL VARCHAR(50), IN FQHNCOL VARCHAR(50), IN DFQHNCOL VARCHAR(50), IN TABLENAME VARCHAR(50), IN OSTYPE VARCHAR(32))
LANGUAGE SQL
BEGIN
...
END
DB20000I The SQL command completed successfully.
C:Program FilesIBMSQLLIBBIN>
6. Call the stored procedure to populate the table with machines information taken from the various agents views. See Example 8-8. Use the schema name appropriate to your database instance (by default it is ITMUSER):
db2 call ITMUSER.POPULATE_OSAGENTS();
Example 8-8 MANAGEDSYSTEM table population
C:Program FilesIBMSQLLIBBIN>db2 call ITMUSER.POPULATE_OSAGENTS();
Return Status = 0
C:Program FilesIBMSQLLIBBIN>
Now, a MANAGEDSYSTEM table is created and populated with the entries for each monitored machine (Figure 8-7).
Figure 8-7 Sample MANAGEDSYSTEM table contents
Tivoli Reporting and Analytics Model database/schema setup
Set up IBM_TRAM (IBM Tivoli Reporting and Analytics Model) schema and populate TimeDimension table and ComputerSystem table.
The schema can be created in one of the existing databases, Tivoli Data Warehouse or TADDM, or it can also be a separate DB2 database instance. However, the preference is to create schema in the Tivoli Data Warehouse.
Two main tables are in the IBM_TRAM schema:
•TimeDimension with timestamps generated according to specified period of time (for example 1 June 2010 - 31 December 2010, every 5 minutes).
•ComputerSystem that will hold the information about the machines that are common for Tivoli Data Warehouse and IBM Tivoli Application Dependency Discovery Manager. This table will be populated by the utility described later in this section.
Follow the instructions provided with IBM Tivoli Monitoring 6.2.2 Fix Pack 2 to create and populate additional schema. See the following web address for instructions:
We present sample steps of the instructions for DB2 database. The example is based on the same environment as in “Tivoli Data Warehouse setup” on page 210. The scripts that are used during the configuration are from the same IBM Tivoli Monitoring agent reports package downloaded previously.
Perform the following steps:
1. Open a DB2 command line by clicking Start → All Programs → IBM DB2 → DB2COPY1 (Default) → Command Line Tools → Command Window. See Figure 8-8.
Figure 8-8 Sample DB2 client connection and IBM_TRAM.db2 script execution
2. Connect to the Tivoli Data Warehouse database by executing the following command in the window. You must have administrative access rights to create new schema, tables and stored procedure in the Tivoli Data Warehouse database.
db2 CONNECT TO <database-alias> USER <user-name> USING <password>
See Example 8-9.
Example 8-9 Establishing a connection to Tivoli Data Warehouse database
C:Program FilesIBMSQLLIBBIN>db2 CONNECT TO TDWDB1 USER db2inst1 USING itso4you
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TDWDB1
C:Program FilesIBMSQLLIBBIN>
3. Create IBM_TRAM schema and tables by running the create_schema_IBM_TRAM.db2 script (see Example 8-10):
db2 -tf create_schema_IBM_TRAM.db2
Example 8-10 Creating IBM_TRAM schema and tables
C:Program FilesIBMSQLLIBBIN>db2 -tf C:Integration6.2.2-TIV-ITM_TMV-AgentReports-FP0002db_scriptsdb2create_schema_IBM_TRAM.db2
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
...
C:Program FilesIBMSQLLIBBIN>
4. Create a stored procedure that will be used to populate TIME_DIMENSION table. Run gen_time_dim_granularity_min.db2 script (see Example 8-11):
db2 -td@ -vf gen_time_dim_granularity_min.db2
Example 8-11 Creating population procedure
C:Program FilesIBMSQLLIBBIN>db2 -td@ -vf C:Integration6.2.2-TIV-ITM_TMV-AgentReports-FP0002db_scriptsdb2gen_time_dim_granularity_min.db2
drop procedure IBM_TRAM.CREATE_TIME_DIMENSION
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0204N "IBM_TRAM.CREATE_TIME_DIMENSION" is an undefined name.
SQLSTATE=42704
create procedure IBM_TRAM.CREATE_TIME_DIMENSION(IN startDate VARCHAR(32), IN end Date VARCHAR(32), IN granularity SMALLINT)
LANGUAGE SQL
BEGIN
...
END
DB20000I The SQL command completed successfully.
C:Program FilesIBMSQLLIBBIN>
5. Populate the TIME_DIMENSION table by calling the stored procedure created in the previous step. The procedure requires three arguments:
db2 call IBM_TRAM.CREATE_TIME_DIMENSION('start_date', 'end_date', granularity_of_data);
For example, the command in Example 8-12 on page 216 generates timestamps from 15 May 2010 - 05/15/2010 to 1 August 2010, for 1 minute granularity. You can also generate more months in the range, depending on your needs.
Example 8-12 TIME_DIMENSION table population
C:Program FilesIBMSQLLIBBIN>db2 call IBM_TRAM.CREATE_TIME_DIMENSION('2010-05-15-00.00.00.000000', '2010-08-01-00.00.00.000000', 1);
Return Status = 0
C:Program FilesIBMSQLLIBBIN>
Now, you have IBM_TRAM schema created in your Tivoli Data Warehouse. See Figure 8-9. The schema contains five tables:
•TIME_DIMENSION: This table contains generated timestamps. Generation is done by the stored procedure, within a specified date range and granularity. Timestamps are converted to separate keys at the generation time and stored in separate columns.
•WEEKDAY_LOOKUP: This table is of globalized weekday names for Time Dimension.
•MONTH_LOOKUP: This table has globalized month names for Time Dimension.
•TIMEZONE_DIMENSION: This table stores information about the timezone of the collected data.
•“ComputerSystem”: This table is dimension for common machines between separate Tivoli products (in this scenario between IBM Tivoli Monitoring and TADDM).
Figure 8-9 IBM_TRAM schema tables
ComputerSystem table population
In this step, the IBM_TRAM.”ComputerSystem” table will be populated with common data taken from Tivoli Data Warehouse and TADDM. A simple Java utility program is provided for this integration scenario; it performs all the necessary work. The program connects to Tivoli Data Warehouse and TADDM databases through the JDBC connections, selects the records about the machines monitored by IBM Tivoli Monitoring and discovered by TADDM, and then merges them in the “ComputerSystem” table that is defined within the IBM_TRAM schema. See Figure 8-10.
Figure 8-10 Common data population utility
The following high-level steps are performed by the utility:
1. Creates computer systems list monitored by IBM Tivoli Monitoring. Three separate queries must be executed because agents for separate operating system types (Windows, Linux and UNIX) store data in separate tables:
a. Gets all Windows computer systems from Tivoli Data Warehouse.
b. Gets all Linux computer systems from Tivoli Data Warehouse.
c. Gets all UNIX computer systems from Tivoli Data Warehouse.
2. Creates computer systems list discovered by TADDM.
3. Iterates through the IBM Tivoli Monitoring list created in step 1, creates the result list.
a. If that TADDM list that is created in step 2 contains a computer system (host names are equal or IP addresses are equal or serial numbers are equal):
i. Adds the machine to the result list.
ii. Updates the result computer system information with TADDM data (they tend to be more accurate in the area of configuration).
b. If TADDM list does not contain computer system, skips the machine.
4. Removes all the rows from the IBM_TRAM.ComputerSystem table.
5. Inserts all the elements from the result list into the IBM_TRAM.ComputerSystem table.
|
Important: Tivoli Data Warehouse data and TADDM data are not modified by this utility. The only table that is cleaned and populated with the new data is IBM_TRAM.ComputerSystem table. Access to the tables from Tivoli Data Warehouse and TADDM systems can be considered as read-only access type.
|
Tables in this section summarize columns mapping between IBM_TRAM.ComputerSystem table and Tivoli Data Warehouse and TADDM views and columns.
Table 8-1 shows Windows agent columns mapping.
Table 8-1 Windows agent columns mapping on ComputerSystem table
|
IBM_TRAM.ComputerSystem table (common dimension)
|
IBM Tivoli Monitoring Windows Agent views and columns
|
|
CSID
|
Automatically generated
|
|
FQDN
|
NT_Computer_Information_DV. LAT_Computer_Domain_Name
|
|
Manufacturer
|
NT_Computer_Information_DV. LAT_Computer_Vendor
|
|
Model
|
NT_Computer_Information_DV. LAT_Computer_Name
|
|
SerialNumber
|
NT_Computer_Information_DV. LAT_Computer_ID_Number
|
|
VMID
|
Not available
|
|
PrimaryMACAddress
|
Not available
|
|
IpAddress
|
NT_System_DV. LAT_Network_Address
|
|
Signature
|
Not available
|
|
ManagedSystemName
|
NT_Computer_Information_DV. System_Name
|
|
Group
|
Not available
|
|
TIME_RANGE_START
|
Out of the scope of this integration
|
|
TIME_RANGE_END
|
Out of the scope of this integration
|
Windows computer systems data is taken from two Tivoli Data Warehouse views:
•NT_Computer_Information_DV
•NT_System_DV
Join operation between these two views is created through the following columns:
•NT_Computer_Information_DV.System_Name
•NT_System_DV.Server_Name
The last entry (maximum value from the WRITETIME column) at the time when the tool is executed is taken for each machine that is stored in the database.
Table 8-2 on page 219 lists Linux agent columns mapping.
Table 8-2 Linux agent columns mapping on ComputerSystem table
|
IBM_TRAM.ComputerSystem table (common dimension)
|
IBM Tivoli Monitoring Linux Agent views and columns
|
|
CSID
|
Automatically generated
|
|
FQDN
|
Linux_IP_Address_DV.LAT_DNS_Name
|
|
Manufacturer
|
Linux_Machine_Information_DV. LAT_Hardware_Brand
|
|
Model
|
Linux_Machine_Information_DV. LAT_Hardware_Model
|
|
SerialNumber
|
Linux_Machine_Information_DV. LAT_Machine_Serial
|
|
VMID
|
Not available
|
|
PrimaryMACAddress
|
Not available
|
|
IpAddress
|
Linux_IP_Address_DV.IP_Address
|
|
Signature
|
Not available
|
|
ManagedSystemName
|
Linux_Machine_Information_DV. System_Name
|
|
Group
|
Not available
|
|
TIME_RANGE_START
|
Out of the scope of this integration
|
|
TIME_RANGE_END
|
Out of the scope of this integration
|
Linux computer systems data is taken from two Tivoli Data Warehouse views:
•Linux_Machine_Information_DV
•Linux_IP_Address_DV
Join operation between these two views is created through the System_Name columns. Similar to Windows agent, the last entry (maximum value from the WRITETIME column) for each machine is taken. If a machine has several IP addresses configured, one IPv4 address is taken (excluding local host addresses).
Table 8-3 shows UNIX agent columns mapping.
Table 8-3 UNIX agent columns mapping on ComputerSystem table
|
IBM_TRAM.ComputerSystem table (common dimension)
|
IBM Tivoli Monitoring UNIX Agent views and columns
|
|
CSID
|
Automatically generated
|
|
FQDN
|
Machine_Information_DV.LAT_Hostname
|
|
Manufacturer
|
Machine_Information_DV.LAT_Hardware_Manufacturer
|
|
Model
|
Machine_Information_DV.LAT_Hardware_Model
|
|
SerialNumber
|
Machine_Information_DV.LAT_Machine_Serial
|
|
VMID
|
Not available
|
|
PrimaryMACAddress
|
Not available
|
|
IpAddress
|
System_DV.LAT_Net_Address
|
|
Signature
|
Not available
|
|
ManagedSystemName
|
Machine_Information_DV.System_Name
|
|
Group
|
Not available
|
|
TIME_RANGE_START
|
Out of the scope of this integration
|
|
TIME_RANGE_END
|
Out of the scope of this integration
|
UNIX computer systems data is taken from two Tivoli Data Warehouse views:
•Machine_Information_DV
•System_DV
Join operation between these two views is created through the System_Name column. Again, in this case, only the last entry (maximum value from the WRITETIME column) for each of the machines is selected.
Table 8-4 shows TADDM database columns mapping.
Table 8-4 TADDM database columns mapping on ComputerSystem table
|
IBM_TRAM.ComputerSystem table (common dimension)
|
TADDM views and columns
|
|
CSID
|
Automatically generated
|
|
FQDN
|
BB_COMPUTERSYSTEM40_V.FQDN_C
|
|
Manufacturer
|
BB_COMPUTERSYSTEM40_V.MANUFACTURER_C
|
|
Model
|
BB_COMPUTERSYSTEM40_V.MODEL_C
|
|
SerialNumber
|
BB_COMPUTERSYSTEM40_V.SERIALNUMBER_C
|
|
VMID
|
BB_COMPUTERSYSTEM40_V.VMID_C
|
|
PrimaryMACAddress
|
BB_L2INTERFACE41_V.HWADDRESS_C
|
|
IpAddress
|
BB_IPADDRESS73_V.STRINGNOTATION_C
|
|
Signature
|
BB_COMPUTERSYSTEM40_V.SIGNATURE_C
|
|
ManagedSystemName
|
BB_COMPUTERSYSTEM40_V.FQDN_C
|
|
Group
|
Not available
|
|
TIME_RANGE_START
|
Out of the scope of this integration
|
|
TIME_RANGE_END
|
Out of the scope of this integration
|
TADDM database may also contain historical data, for example information about the computer system configuration, which was discovered in the past. The same computer system identified, for example by the fully qualified domain name (FQDN) that is assigned to different machine models. This situation can occur, for example, with hardware replacement. Because of this reason, only the last entry for given FQDN is selected from computer systems view (maximum value from the LASTMODIFIEDTIME_C column per computer system).
Computer system may contain several network interfaces which implicate several IP addresses and MAC addresses. This information is stored by TADDM and obtained by the data synchronization utility based on the following joins:
•BB_COMPUTERSYSTEM40_V.PK_C = BB_COMPUTERSYSTACES_76E84D2AJ.PK__JDOID_C
•BB_COMPUTERSYSTACES_76E84D2AJ.PK__IPINTERFACES_C = BB_IPINTERFACE96_V.PK_C
•BB_IPINTERFACE96_V.PK__IPADDRESS_C = BB_IPADDRESS73_V.PK_C
•BB_IPINTERFACE96_V.PK__L2INTERFACE_C = BB_L2INTERFACE41_V.PK_C
To run the data population utility perform the following steps:
1. Extract the .zip package (listed for this chapter in Appendix A, “Additional material” on page 803) into a convenient location in the file system. The tool is in the Cross_Product_Setup subdirectory.
2. Update config.properties file with connections details to your databases. You must provide connections to three databases:
– Database with the IBM_TRAM schema
– Tivoli Data Warehouse database
– TADDM database
The tool uses JDBC connections, not the native connections, therefore reusing DB2 client configuration that was prepared in the previous steps is not possible.
The config.properties file contains a sample configuration. Specify IP address, port on which the database process is listening, database name, schema name, and access credentials suitable for your environment. In Example 8-13, IBM_TRAM schema was created inside Tivoli Data Warehouse so the connections information is the same except for the schema name value.
Example 8-13 Sample JDBC configuration
############################################
# IBM_TRAM
############################################
tram.server=9.42.171.253
tram.port=60000
tram.user=db2inst1
tram.password=itso4you
tram.db=WAREHOUS
tram.schema=IBM_TRAM
############################################
# IBM Tivoli Monitoring - Tivoli Data Warehouse
############################################
tdw.server=9.42.171.253
tdw.port=60000
tdw.user=db2inst1
tdw.password=itso4you
tdw.db=WAREHOUS
tdw.schema=ITMUSER
############################################
# TADDM
############################################
taddm.server=9.42.171.157
taddm.port=50000
taddm.user=db2inst1
taddm.password=smartway
taddm.db=DEMO
taddm.schema=DB2INST1
3. Update the run.bat script. Replace the path to the Java Virtual Machine executable with your settings. By default, the tool is configured to run on Java provided with IBM Tivoli Common Reporting for Asset and Performance Management server. Replace the following string with path to java.exe program from your environment
"C:IBM ivoli ipjavainjava"
If you do not have a JRE installed, you can use one that is provided with IBM Tivoli Common Reporting or with DB2 Client:
“C:Program FilesIBMSQLLIBjavajdkjreinjava.exe”
4. Execute the run.bat script. The script inserts rows into IBM_TRAM.ComputerSystem table according to the algorithm described in “ComputerSystem table population” on page 216.
Figure 8-11 shows an error reported for the UNIX agent query. The error is because no UNIX machines are in the environment so no UNIX database tables are in the warehouse.
Figure 8-11 Sample population tool execution
If you are running the population utility on a Linux or UNIX platform you can execute the Java class manually (see Example 8-14) by specifying a similar command as in the run.bat script. The path to Java Virtual Machine is also required.
Example 8-14 Executing the Java class manually
[root@tsekman2 Synchronize]# /opt/IBM/tivoli/tip/java/bin/java -classpath lib/db2jcc.jar:lib/db2jcc_license_cu.jar:lib/synchronizer.jar com.ibm.tivoli.tcr.tram.synchronizer.PopulateData config.properties
8.5.4 Data sources definition in IBM Tivoli Common Reporting
Now you have products databases prepared for cross-product reporting sample. You also have the DB2 client installed on your IBM Tivoli Common Reporting for Asset and Performance Management server machine and cataloged connections to your databases. It is time to start using reporting utility. First we need to define three data sources in the Cognos BI Reporting engine.
Sample cross-product model attached to the book is based on the following three data sources:
•IBM_TRAM: Connection to the database in which IBM_TRAM schema was created.
In our case it will be the same connection as TDW. Even in this case, a separate data source must be defined.
•TDW: Connection to the Tivoli Data Warehouse database
•TADDM: Connection to the TADDM database
Using the same names for the data sources definitions is a good idea; modifying the sample model is not necessary. However, small updates in the area of schema names still might be required. We describe the details in a subsequent sections.
To connect IBM Tivoli Common Reporting for Asset and Performance Management to a database, create a data source definition within the Cognos reporting engine. The definition is based on the native or ODBC connection. Cognos does not support light JDBC connections.
To show a sample configuration, we use connections that we have defined in the section 8.5.1, “Database native client” on page 206. We will create data sources in Cognos BI Reporting engine based on databases’ aliases cataloged in that section.
1. Log in to the IBM Tivoli Common Reporting web interface. The application is available by default under the following addresses:
– http://myserver.mycompany.com:16310
– https://myserver.mycompany.com:16316/ibm/console
Connection on non-secure port 16310 is redirected to a secure one 16316.
Enter the user name and password. Choose the user that has the administrative privileges (by default tipadmin).
2. In the navigation area on the left side of the window, click Reporting → Common Reporting. The IBM Tivoli Common Reporting for Asset and Performance Management report navigation pane opens, IBM Cognos Connection application. See Figure 8-12 on page 224.
Figure 8-12 IBM Tivoli Common Reporting for Asset and Performance Management navigation pane
3. At the top right corner of the portlet click Launch → IBM Cognos Administration. See Figure 8-13.
Figure 8-13 Switching between Cognos applications
4. In the IBM Cognos Administration portlet, click the Configuration tab. All the data sources defined within your instance are listed. See Figure 8-14 on page 225. By default, only one connection is created during the IBM Tivoli Common Reporting 1.3 server installation. That connection is used by MOSWOS (Manage Our Stuff With Our Stuff) report.
Figure 8-14 Data sources administration portlet
5. To define a data source, click the New Data Source icon, which is located in the top right corner of the administration portlet. Follow the New Data Source wizard. Specify the following values:
The sample model for this chapter is based on that data source connection name. Therefore, define the same name (TDW).
Figure 8-15 Enter data source name
Figure 8-16 Specify connection type
Use the database alias under which you have cataloged your Tivoli Data Warehouse database.
Figure 8-17 Enter database alias from the DB2 client catalog
Select Signons and Password, and enter the User ID and Password to your Tivoli Data Warehouse database. See Figure 8-18.
Figure 8-18 Enter database credentials
6. Click Finish to save the connection definition and return to the administration portlet.
7. Click the name of the newly created data source. We will test the connection.
Figure 8-19 Testing the connection
9. Click Test in the Test the connection panel.
Cognos engine tests the connection and returns the result, as shown in Figure 8-20 on page 228.
|
Note: On Linux or UNIX platforms you may experience difficulties with the data sources tests. You might see the errors related with native libraries loading. IBM Tivoli Common Reporting for Asset and Performance Management must be able to find the native DB2 libraries. To be sure, verify whether the DB2 lib32 directory exists before starting the IBM Tivoli Common Reporting for Asset and Performance Management server. Configure the system library path to point to the database client library directory by modifying the following environment variable:
•LIBPATH on AIX
•SHLIB_PATH on HP-UX
•LD_LIBRARY_PATH on Linux or Solaris
An example is as follows:
export LD_LIBRARY_PATH=/opt/ibm/db2/V9.5/lib32:$LD_LIBRARY_PATH
|
Figure 8-20 Successful connection test
Follow the step 4 on page 224 through step 9 on page 227 to define two remaining data sources: IBM_TRAM and TADDM. In our example, the IBM_TRAM data source is defined in the same way as TDW, using the same database alias. The reason is because IBM_TRAM schema was created within Tivoli Data Warehouse database, not in the separate database instance. Nevertheless it is important to define IBM_TRAM as a separate connection because the sample cross-product model is based on this data source.
Now, your three data sources are defined and tested. See Figure 8-21.
Figure 8-21 Required data sources
For more information about the data sources definition and how to set up data sources for various database vendors, see the IBM Tivoli Common Reporting for Asset and Performance Management documentation:
8.5.5 Cross-product model update
The .zip package that is listed as an additional material for this chapter (see Appendix A, “Additional material” on page 803)contains a sample cross-product model that can be used to generate Cognos reports. The model is delivered in two forms: published version and source file. If you have default schema names in your IBM Tivoli Monitoring and TADDM databases, respectively ITMUSER and DB2INST1, you may skip this section and go straight to the next step to import a published version of the model into your IBM Tivoli Common Reporting 1.3 server (8.5.6, “Package import” on page 235). Otherwise, you must modify the model source by updating it with the schema names from your databases. After the modification you will be able to publish the model on your reporting server directly without the necessity of the package import.
However, performing this step is a good practice, even if your schema names match the values from the ready-package. By performing this step, you will set up Cognos BI Modelling tool (Framework Manager) and learn how to modify the model, customizing it for your needs.
|
What publishing the model means: The source of the model is written in the XML file that is maintained by the Cognos BI Modeling tool: Framework Manager. The tool is attached (connected) to the IBM Tivoli Common Reporting for Asset and Performance Management server (more precisely to the Cognos BI Reporting engine). This connection Framework Manager is able to get the information about the data sources that are defined in Cognos BI Reporting engine and also store the information about objects from the model into the Cognos content store. By storing selected objects (query subjects, query items, relationships, and so on) in the content store Framework Manager, they become more available for the reporting engine users. This process is called model publication. After the model is published, it can be exported from the Cognos BI Reporting engine into the packaged version (different from the source) and imported into another Cognos BI Reporting engine instance.
|
Framework Manager allows necessary configuration updates. It is the utility in which you can model a business domain, an abstraction layer that is based on the objects from the database. The model covers database structure and facilitates report creation in IBM Tivoli Common Reporting for Asset and Performance Management (especially ad-hoc reports).
More information about the modeling tool is in the Framework Manager User Guide, available at the following location:
Framework Manager configuration
To update and publish the cross-product reporting model you need the additional Cognos BI Modeling tool: Framework Manager. It is a stand-alone application, development utility, which can be installed only on the Windows platform.
The program is available on Windows installation media of IBM Tivoli Common Reporting for Asset and Performance Management (part number CZ6SWML). The installer executable (issetup.exe) file is in the CognosModelingwin32 subdirectory. After the installation (Figure 8-22) be sure to attach the Framework Manager instance to the IBM Tivoli Common Reporting 1.3 server by specifying suitable URIs to Cognos BI Reporting engine. Follow the instruction at the IBM Tivoli Common Reporting for Asset and Performance Management information center to install and configure Framework Manager:
|
Note: If you have IBM Tivoli Common Reporting 1.3 server running on a Windows machine, you may install Framework Manager on the same machine, to the directory of the Cognos engine that is installed and used by the IBM Tivoli Common Reporting 1.3 server. The advantage of this solution is that Framework Manager requires no additional configuration and is ready to use after the installation. To achieve this state, be sure to specify the correct destination directory during the modeling tool installation wizard. If your IBM Tivoli Common Reporting instance is installed in C:IBM ivoli ip directory (the default location), specify the following destination directory for Framework Manager:
C:IBM ivoli ipproducts crCognosc8
Ignore the warnings about replacing existing files. You will have to stop IBM Tivoli Common Reporting for Asset and Performance Management server for the installation time.
|
Figure 8-22 Framework Manager destination directory
After the installation and configuration, you may start the Framework Manager by clicking either of the following selection paths (if you have IBM Tivoli Common Reporting 1.3 server installed on the same machine):
•Start → All Programs → Tivoli Common Reporting → Framework Manager
•Start → All Programs → IBM Cognos 8 → Framework Manager
Working with Cognos model
Now you are ready to work with the Cognos models. Extract the .zip package (listed as an additional material for this chapter in Appendix A, “Additional material” on page 803) into a convenient location in the file system. The model is located in the Cross_Product_Model subdirectory.
1. Select Open a project from the Framework Manager welcome window and browse to the Cross_Product_Model directory. Select the Cross_Products_Model_v3.cpf file and click Open. See Figure 8-23.
Figure 8-23 Opening Cognos model project
2. Framework Manager tries to establish the connection with IBM Tivoli Common Reporting for Asset and Performance Management server (Cognos BI Reporting engine). Framework Manager must be able to get access to data sources that are defined in Cognos. The connection is performed over HTTP protocol so you must accept the certificate and provide administrator credentials (tipadmin user name and password), the same account that you are using to log in to the IBM Tivoli Common Reporting console. In Framework Manager, you have to authenticate twice because there is no support for single sign-on functionality.
Framework Manager does not accept cookies, therefore provide the same user name and password twice:
– The first time is to access WebSphere application container. See Figure 8-24 on page 232.
Figure 8-24 First login window in Framework Manager
– The second time is to access Cognos application deployed inside that container. See Figure 8-25.
Figure 8-25 Second login window in Framework Manager
3. After connection, establishing Framework Manager loads the model. Examine the various namespaces and query subjects, which are available in the model. See Figure 8-26 on page 233.
Figure 8-26 Namespaces and query subjects which are available in the model
4. Update the schema names in the data sources definitions:
a. Select a data source node from the model tree. See Figure 8-27.
Figure 8-27 Data Sources model node
b. Specify your schema name for TDW and TADDM data sources. See Figure 8-28.
Figure 8-28 Schema name property
5. Optional: you can introduce additional changes to the model. For example add query subjects for the tables or views you are interested in. For this task you will need to be familiar with the structure of product’s database.
6. Save the model changes.
7. Publish the package:
Figure 8-29 Model publication wizard
b. Go through the publication wizard with default settings by clicking Next.
c. Click Publish to publish the model. Ignore the warnings in the summary window at this time. See Figure 8-30 on page 235.
Figure 8-30 Model publication summary
After successful publication your model becomes available in IBM Tivoli Common Reporting for Asset and Performance Management web application.
8.5.6 Package import
The ready-to-use cross-product sample package is listed as an additional material for this chapter (see Appendix A, “Additional material” on page 803). You may import that package into your IBM Tivoli Common Reporting for Asset and Performance server without any modifications if you have the default schema names for Tivoli Data Warehouse (ITMUSER) and TADDM (DB2INST1) databases. Otherwise, follow the steps in 8.5.5, “Cross-product model update” on page 229 to update the model and publish it on the server. The result of both paths (publication and import) is the same: the reporting package available on IBM Tivoli Common Reporting for Asset and Performance Management server, ready for reports generation.
To import the package, perform the following steps:
1. Extract the .zip package for this chapter (see Appendix A, “Additional material” on page 803) into the convenient location in the file system and copy TivoliReportingPackage.zip into the Cognos BI Reporting engine deployment folder:
<TCR_installation_directory>products crCognosc8deployment.
2. Log in to the IBM Tivoli Common Reporting for Asset and Performance Management console and go to Common Reporting.
3. In the Work with reports window on the right, choose the IBM Cognos Administration from the Launch drop-down list.
4. In the IBM Cognos Administration portlet select Configuration tab and open the Content Administration section.
5. Start a new package import wizard by clicking the New Import icon, which is located in the top-right corner of the administration portlet. See Figure 8-31.
Figure 8-31 Importing report package
6. Follow the wizard to import the package:
a. Select TivoliReportingPackage from the list of available packages. The list contains all the .zip files from the deployment directory.
b. Keep the import name default.
c. Select which element should be imported from the package. This important step is frequently omitted. Select the Tivoli Reporting Package check box. See Figure 8-32.
Figure 8-32 Package entry selection
d. Follow the remaining wizard steps, accepting the default settings.
e. Save and run after the import task is defined. Click Finish and then click Run.
You have now defined import task and executed it immediately at the end of the import wizard. Cross-product reporting package is available in IBM Tivoli Common Reporting for Asset and Performance Management console.
8.6 Scenario walk-through
You may now test the integration scenario. This section also explains how to create sample cross-product ad-hoc reports.
8.6.1 Integration test
To test the integration scenario perform the following steps:
1. Log in to the IBM Tivoli Common Reporting for Asset and Performance Management web application and open Common Reporting portlet: IBM Cognos Connection application. You see the new Tivoli Reporting Package (Figure 8-33). This package was published or imported to the Cognos BI Reporting engine in previous steps.
Figure 8-33 Tivoli Reporting Package - cross-product model
2. Click Tivoli Reporting Package to open it. The package is empty; it has no advanced or ad-hoc reports at this time. Only the model is available, but it is not visible in IBM Cognos Connection application.
3. To test the model in action, open Query Studio application. At the top right corner of the portlet click Launch → Query Studio. See Figure 8-34 on page 238.
Figure 8-34 Launching Query Studio for cross-product package
Query Studio application can run a report that is based on one query composed from the elements taken from domain model. The query result is presented in a table. Additionally chart can be added. The main area of the Query Studio is the result table into which you drag elements from the model tree, available on the left side of the application window. See Figure 8-35 on page 239.
The cross-product model contains the following branches:
– ITM OS Agents Model: Query subjects based on views from Tivoli Data Warehouse,
– ITM Shared Dimensions: Query subjects based on MANAGEDSYSTEM table, created and populated in the previous sections,
– TRAM Shared Dimensions: Query subjects based on the IBM_TRAM schema, created and populated in the previous sections,
– TADDM Model: Query subjects based on views from TADDM database
Figure 8-35 Cross-product model in Query Studio
4. Drag the following query items from the model:
a. Tivoli Reporting / TADDM Model / Computer System / Manufacturer
b. Tivoli Reporting / TADDM Model / Computer System / Model
c. Tivoli Reporting / ITM Share Dimensions / Managed System / Managed System Name
d. Tivoli Reporting / ITM Share Dimensions / Managed System / Agent Type
e. Tivoli Reporting / ITM OS Agents Model / Windows / Daily / System Daily / Average % Total Processor Time
While you drag elements, Cognos creates the necessary joins for you, execute the queries, aggregate the data, and present the results. You see a table that is similar to the one in Figure 8-36 on page 240. Notice that daily data taken from System Daily query subject where it is automatically rolled up (aggregated). Average CPU utilization value is presented for the entire time range, gathered in Tivoli Data Warehouse. Therefore, we do not see average values for each day but average values for all days computed (by Cognos) per selected machine.
Figure 8-36 Sample cross-product report
What exactly was done by Cognos reporting engine behind this sample scenario? Three tables were joined by the engine based on the rules that are defined in the model. Two tables are taken from Tivoli Data Warehouse, and one is from TADDM database. Computer System view from TADDM was joined with Computer System table from shared dimension namespace, which subsequently was also joined with System Daily view from Tivoli Data Warehouse.
Cognos performed cross-database data merging outside the databases, on the IBM Tivoli Common Reporting for Asset and Performance Management server machine. In certain cases, this merge might have considerable impact on query execution performance and disk space requirements.
The user who prepares the report in Query Studio does not have to know what database structures are behind the model. All relationships, foreign and primary keys, views and tables are hidden behind the model. The model designer is responsible to deliver complex database structures in a clear form, understandable by a business domain analyst.
Figure 8-37 on page 241 presents lineage of the data that is used in the test report.
Figure 8-37 Cross-product query details
8.6.2 Aggregating resources utilization per machine model and manufacturer
Perform the following steps to create the report:
1. Log in to the IBM Tivoli Common Reporting for Asset and Performance Management console and open the Query Studio for Tivoli Reporting cross-product package. See Figure 8-38 on page 242.
2. Start with TADDM Computer System data.
3. Insert the TADDM Model / Computer System / Manufacturer query item.
4. Insert the TADDM Model / Computer System / Model query item.
5. Select Manufacturer column heading and click the Group icon from the Query Studio toolbar.
The machines are now grouped by manufacturer name.
6. Add resource utilization metrics from IBM Tivoli Monitoring.
For example, insert the ITM OS Agents Model / Windows-Unix / Hourly / CPU hourly / AVG_%_CPU_Used query item
7. The processor utilization values are now rolled up for each model and manufacturer. You may split the aggregation by adding the host name to the query.
Figure 8-38 CPU average utilization for particular manufacturers and machine models
8.6.3 Aggregating resources utilization per operating system
Perform the following steps to create this report:
1. Log in to the IBM Tivoli Common Reporting for Asset and Performance Management console and open the Query Studio for Tivoli Reporting cross-product package.
2. Start with TADDM Operating system data.
3. Insert the TADDM Model / Operating System / Name query item.
4. Right-click the Name column heading and select Filter.
5. From the list of all operating systems. which were discovered in your environment, select operating systems that you want to check.
6. Insert the TADDM Model / Operating System / Version query item.
7. Select Name column heading and click the Group icon from the Query Studio toolbar.
Your operating systems are now grouped by name.
8. Add resource utilization metrics from IBM Tivoli Monitoring.
For example, insert the ITM OS Agents Model / Windows-Unix / Hourly / CPU Hourly query subject. It is possible to add a whole query subject to a report. All query items from the subject are added in that case.
9. The processor utilization data are now rolled up for each operating system version and name. You may split the aggregation by adding the host name to the query. See Figure 8-39 on page 243.
Figure 8-39 CPU utilization for particular operating systems
8.7 Online resources
See the following resources:
•IBM Tivoli Common Reporting for Asset and Performance Management 1.3 information center
•IBM Tivoli Monitoring 6.2.2 Fix Pack 2 information center
•IBM Tivoli Application Dependency Discovery Manager 7.2 information center
8.8 Summary
Figure 8-40 shows the quick summary of this integration,
Figure 8-40 Quick summary
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.