by Ben Fawcett
This tip walks through an example of how to pull Tivoli Inventory data into the ITM 6.1 Tivoli Enterprise Portal (TEP). Although this example is for a DB2 Inventory database the principles are the same if you want to show data from any RDBMS database - TEC or CMDB for example.
The steps required to bring RDBMS data into TEP can be broken down as follows :
- Collect data from RDBMS
- Filter data by context
- Display data
There are a number of techniques that can be used to bring RDBMS data into TEP.
Data can be collected using the TEPS Custom SQL queries or using the Universal Agent, both these options require ODBC.
Data can be filtered by a where clause in Custom SQL, by attribute filters in the Query Editor or by attribute filters in the TEP table view.
The usual display method will be using a table or graph in the TEP. These can be part of a standard workspace or in a linked workspace. The advantage of a linked workspace is that additional context filtering can be done with variable substitution at this stage.
In this example I show how to retrieve data from the Inventory view NATIV_SWARE_VIEW. This view contains a row for every software binary on every Endpoint so it can be very large. In this case it is very important to filter the data as early as possible in order to minimize the impact on the TEMS and TEPS. For this reason I will use a TEPS Custom SQL query filtered at data collection time by the where clause. I use a standard workspace to show the data.
Setting Up ODBC
The first step is to set up the Inventory database as an ODBC source available from the TEP Server.
1. Select the Windows ODBC Connectivity utility, e.g. "Data Sources (ODBC)" from "Control Panel -> Administrative Tools" on Windows 2000
2. On the System DSN tab select "Add..."
3. Select the relevant ODBC driver, in this case I need DB2
4. Name the Data Source "Inventory" and make sure it is configured to the correct Database Alias. On this system I have cataloged the Inventory database as "CM_DB" in DB2 so that is what I will use
5. The Inventory ODBC Data Source is now configured
Configure the TEPS ODBC Connection
In order for ITM 6 to access the ODBC connection it must be registered in the TEP Server KFWENV file.
1. The TEP Server KFWENV file can be edited directly in $CANDLE_HOME/CNPS/KFWENV. It can also be edited via the TEPS menu in the Manage Tivoli Enterprise Monitoring Services utility under "Advanced -> Edit ENV file..."
2. Add the a line similar to the following to the KFWENV file, using a valid user (UID) and password (PWD) for your database connection
If DSUSER1 is already defined then use the next available number, e.g. DSUSER2.
3. Restart the TEP Server
Create a Custom Query
The next step is to create a custom query against the ODBC Data Source.
1. Login to the TEP
2. Select "Edit -> Query Editor" from the main menu
3. Create a new Query
- Name : Software Query
- Category : Universal Data Provider
- Data Source : Inventory
- Custom SQL: Yes
4. Edit the SQL for the Query
select * from NATIV_SWARE_VIEW where NATIV_SWARE_VIEW.TME_OBJECT_LABEL = $NODE:-1022$
You can use any view or table in this query, I am using the Native Software View in this example.
The key to using this method for retrieving data is to use a where clause in the SQL statement. This means that the query results are filtered before being pulled back to the TEPS and significantly reduces the performance overhead for this kind of query.
The where clause in this example uses the ITM 6 variable $NODE:-1022$ which will dynamically map to the current system in the navigator tree when we create a workspace that uses it.
Create a Workspace to View the Query
The query will work if you use it in a workspace at the Managed System level or below in the TEP navigator. However, I recommend that you use it at the Agent level.
This has the advantage that the saved workspace is then automatically available on that Agent type for every Managed System in the environment.
Create a new workspace to show the Inventory data.
1. Create a table view and edit the properties
2. Set the Query for the table to "Queries -> Universal Data Provider -> Custom SQL -> Software Query"