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.

Overview

The steps required to bring RDBMS data into TEP can be broken down as follows :

  1. Collect data from RDBMS
  2. Filter data by context
  3. 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..."

 odbc 1

3. Select the relevant ODBC driver, in this case I need DB2

 odbc 2

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

 odbc 3

5. The Inventory ODBC Data Source is now configured

odbc 4

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

DSUSER1="DSN=Inventory;UID=db2admin;PWD=t1voli"

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

create query

4. Edit the SQL for the Query

select * from NATIV_SWARE_VIEW where NATIV_SWARE_VIEW.TME_OBJECT_LABEL = $NODE:-1022$

 

edit query

 

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.

 nav system or agent

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"

inv workspace

Twitter Feed

OrbData RT @OrbData: Read how using Orb Data's remote support service can reduce your ESM project costs significantly https://t.co/bS70jThVGh #IBM
OrbData RT @OrbData: Find out how to use #IBM #Netcool Impact’s new RESTful API to integrate with #Atlassian #JIRA Service Desk https://t.co/OYCdRk

Address

Address:
100 Longwater Avenue, Green Park, Reading, RG2 6GP, U.K.
Tel:
+44 (0) 118 945 0130
E-Mail:
This email address is being protected from spambots. You need JavaScript enabled to view it.

markerFind on Google Maps

About Us

Orb Data brings together People, Process and Technology to deliver the cornerstone of business success: the management of IT infrastructure. At our heart are our people. We have unrivalled experience, helping us to achieve an enviable reputation for excellence in project delivery. Because we’re independent, we identify actual issues and help organisations resolve them –from spec to deployment, and beyond –providing the right solution in terms of best of breed technology and support. We offer a refreshingly simple approach to the way we conduct business. We take pride in our abilities to provide first class solutions to business problems, and to conduct working relationships with honesty and integrity.

Follow Us On:

JoomShaper