MDB 1.4 Schema Overview

The CA Management Database (MDB) is a relational database used to store the operational, transactional, and analytical data required for CA products.

The MDB schema contains tables for many solutions offered by CA.

Note:The tables represented in this Schema Viewer reflect the MDB 1.04 base release. To view the tables provided with MDB 1.5, click here. Depending on which products you have installed and which patches you have applied changes may have been made to the tables in your MDB. To determine which changes affect the objects you are interested in, use the database tools provided with your particular database application (for example, Microsoft SQL Server).

Sharing a database results in reduced administration costs and enhanced and simplified integration.

The schema documentation provides the following information:

• Table names
• Table comments
• Column names
• Column data types
• Column null option
• Column comments

The schema documentation is intended to help you:

• Better understand the content in the MDB
• Create reports
• Integrate other information as desired
• Understand the overall MDB architecture

This information is provided as-is and for your convenience only. You should consult your product documentation for most current schema tables and best practices.

Common Asset Tables

The CA Management Database (MDB) contains data maintained by all CA products. Central to the MDB are the common asset tables. These tables are used to relate all the data about the computing assets of an organization available through the various CA products.

These tables are maintained and updated through a common asset registration API: all products use the API to register the assets they manage. The API matches and reconciles entries for the same asset made by different products.

The common asset tables maintain the identification information for all assets. Additional information is contained within the individual product tables.

Customers with several CA products can use the common asset tables to reach data about an asset in tables maintained by multiple products.

The following illustration shows the relationships between a few of the common asset tables and the main asset tables used by a few specific products.

Cross Product Information Access

To access cross product asset information in the MDB, you must understand the relationship between the common asset tables and the product tables.

The ca_logical_asset table is a common asset table that contains one row for each distinct instance of an asset as it appears to CA management products.

The logical asset level also supports the existence of multiple virtual logical assets that can be part of one physical asset. The most common instances of this are VMWare or Microsoft Virtual PC sessions and dual-boot scenarios. If a single computer is running two VMWare virtual sessions, then a discovery tool such as UAM or NSM will discover these logical assets with different host names. The logical asset allows for the tracking of these virtual assets.

The ca_asset_source table is a common asset table that contains one row for each product that supplies information for each logical asset. For example, if three products have information for a logical asset named L1, there will be three rows in the ca_asset_source table for L1, each linking to a row in a table for the associated product.

By traversing the ca_asset_source table, you can identify all of the sources (products) that have identified a given logical asset. From here you can gather product specific information about logical assets.

By knowing the specific asset_source_uuid of the various products, you can gather additional available information about that logical asset from applications such as Unicenter Network and System Management, Unicenter Desktop and Server Management, Unicenter Service Plus Service Desk, Unicenter Asset Portfolio Management, and the Unicenter System Performance Option.

The following table lists the primary products that contribute information about logical assets, and the table and column they use to establish the relationship. In the following table, the Key column shows the item in the product table that matches the asset_source_uuid in the ca_asset_source table.

View MDB Asset Schema Information from Multiple Products

The following examples show only basic attributes; there are numerous columns in the asset tables that may be useful to include in your specific query or view. These examples are meant for an MDB that is both in an Ingres RDBMS and is being used for more than one product.

To view logical asset information from more than one product table, you must locate the corresponding ca_asset_source table rows, then access the associated product tables.

You can get ownership information (such as location, contact, and vendor) that has been stored by products such as Unicenter Service Desk or Unicenter Asset Portfolio Management, for an asset that is being managed by Unicenter NSM.

The following steps show how to build an SQL query to obtain the resource name of a managed object by accessing information in the ca_owned_resource table for a tng_managedobject row.

To start, you must know the name of the object (logical asset) you want information on. For this example, the name is a1.asset.com.

1. From the row for the object in tng_managedobject, obtain the asset_uuid which is the connection between this table and the ca_asset_source table.

   select m.name, hex(m.asset_uuid) asset_uuid from tng_managedobject m where m.name = 'a1.asset.com'

   The asset_uuid of the managed object is obtained.

   name	asset_uuid
   a1.asset.com	8C8D071A9E42FA468673FBB7C8527ACC

2. Obtain the logical_asset_uuid instead of the asset_source_uuid.

   select m.name, hex(sm.logical_asset_uuid) logical_asset_uuid from tng_managedobject m, ca_asset_source sm where sm.asset_source_uuid = m.asset_uuid and m.name = 'a1.asset.com'

   The logical_asset_uuid of the managed object is obtained.

   name	logical_asset_uuid
   a1.asset.com	EAB75B15E125BC4F8A196FD450BB9225

3. Find all the asset_source rows that point to that logical_asset.

   select m.name, hex(so.asset_source_uuid) source_uuid from tng_managedobject m, ca_asset_source sm, ca_asset_source so where sm.asset_source_uuid = m.asset_uuid and so.logical_asset_uuid = sm.logical_asset_uuid and m.name = 'a1.asset.com'

   Note: In this query are two references to the ca_asset_source table: 'sm' and 'so'. 'sm' is used to match from the tng_managedobject table, and 'so' is used to match to all rows in the table that match the 'sm' row by having the same logical_asset_uuid.

   Every source_uuid of all asset sources is obtained.

   name	source_uuid
   a1.asset.com	8C8D071A9E42FA468673FBB7C8527ACC
   a1.asset.com	FEF877E52C6001DAACCF001143E6A0DC

   Though not part of building this query, you could see what the source for each row is, by accessing the ca_asset_subschema table.

   select m.name, b.subschema_name from tng_managedobject m, ca_asset_source sm, ca_asset_source so, ca_asset_subschema b where sm.asset_source_uuid = m.asset_uuid and so.logical_asset_uuid = sm.logical_asset_uuid and so.subschema_id = b.subschema_id and m.name = 'a1.asset.com'

   The source names are obtained.

   name	subschema_name
   a1.asset.com	SUB1
   a1.asset.com	DB-2

4. To obtain the resource name from the ca_owned_resource table, find the rows that match the rows in 'so'.

   select m.name, o.resource_name from tng_managedobject m, ca_asset_source sm, ca_asset_source so, ca_owned_resource o where sm.asset_source_uuid = m.asset_uuid and so.logical_asset_uuid = sm.logical_asset_uuid and o.asset_source_uuid = so.asset_source_uuid and m.name = 'a1.asset.com'

   The resource name of the object is obtained.

   name	resource_name
   a1.asset.com	ASSET01

Create a View

If you have constructed a cross product query, you can build a view that displays information from that query without worrying about how the query was built.

To build a view named managed_owned:

Create view managed_owned (mng_name, own_resource_name) as select m.name, o.resource_name from tng_managedobject m, ca_asset_source sm, ca_asset_source so, ca_owned_resource o where sm.asset_source_uuid = m.asset_uuid and so.logical_asset_uuid = sm.logical_asset_uuid and o.asset_source_uuid = so.asset_source_uuid

With this view created, you can get the owned_resource_name for a tng_managedobject by simply issuing:

select * from managed_owned where mng_name = 'a1.asset.com'

to obtain:

Or you can get the tng_managedobject name for a ca_owned_resource by issuing:

select * from managed_owned where own_resource_name = 'ASSET01'

to obtain:

If you don't add a where clause to the select on the view, you get information for all the assets that have information from both products. For example:

select * from managed_owned

might obtain:

Note: In a live MDB environment, execution of this query will result in retrieval of a larger number of rows.

Copyright © 2006 CA. All rights reserved.