ItemID value in CA Performance Management does not match the one returned in the Bulk Data Export data

Document ID : KB000032123
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The ItemID for elements seen in the CA Performance Management (CAPM) MySql netqosportal DB do not match those in the Bulk Data Export (BDE) 
data files.

The data from the BDE is derived from the Data Repository (DR) Vertica DB via the Data Aggregator (DA).

This is expected behavior. Elements contributed to the CAPM system can come from any number of other systems such as a DA, Spectrum, eHealth, NFA, etc.

Each system will have its own concept of an ItemID that shows up in CAPM.

CAPM maintains an internal mapping in the netqosportal DB that shows the elements ItemID from the contributing data source along with its mapped ItemID for the elements representation in the CAPM inventory.

Solution:

The process for identifying the necessary mapping data all begins and ends in the netqosportal MySql DB on the CAPM host.

In overview we need to:

  • Determine the SourceID of the Data Source involved, in this example the DA
  • Query the ds_items table in the netqosportal DB for ItemID mapping

Start by connecting to MySql on CAPM. Launch the MySql prompt on the CAPM host as the root user, or sudo user that owns the installation.

By default it is located in $CAPM_HOME/CA/MySql/bin 
Default path for install results in /opt/CA/MySql/bin

Enter the /opt/CA/MySql/bin directory and run:

./mysql

Set it to use the netqosportal DB with this command:

use netqosportal

Run this query:

select sourceid,consolename from data_sources2\G

The output will look something like this:

mysql> select sourceid,consolename from data_sources2\G 
*************************** 1. row *************************** 
sourceid: 0 
consolename: CA Performance Center 
*************************** 2. row *************************** 
sourceid: 2 
consolename: EventManager@HOSTNAME.ca.com 
*************************** 3. row *************************** 
sourceid: 3 
consolename: Data Aggregator@HOSTNAME

Note the DA entry and its SourceID value, in the above example it is 3.

Now that we know that, we can query the ds_items table to get a list of only those elements linked to the DA using its SourceID.

Run this command to do so:

select * from ds_items where sourceid="<DA_ID_from_data_sources2_table>";

To direct the output to a text file run:

select * from ds_items where sourceid="<DA_ID_from_data_sources2_table>" INTO OUTFILE '<fileName>' ;

When using the file redirect option, ensure the path specified allows the user to write a file, and has sufficient disk space available to write the file.

For example the command might look like this:

select * from ds_items where sourceid="3" INTO OUTFILE '/tmp/DA_ItemID_Map.out' ;

In the output we examine the CAPC ItemID (the LocalID column) and the DA ItemID (the ItemID column).

In this situation the ItemID from the DA should match that seen in the BDE export data.

Looking at a single device as an example, the element at localID 9288 is a Router device element in a lab system.

If I run this query I get the following output:

mysql> select * from ds_items where localID="9288"\G 
*************************** 1. row *************************** 
SourceID: 3 
LocalIndex: 7437 
ItemTypeName: device 
LocalID: 9288 
ItemSubTypeName: router 
ItemFlags: 0 
ItemID: 4779 
ItemType: 4 
ItemSubType: 1 
UpdatedOn: 1409103503 
1 row in set (0.12 sec)

Note to run that as a sample, take an ItemID from a random element from the full output listing of all devices in the ds_items table that are associated to the DA at SourceID 3.

When reviewing the output from the ds_items table make sure that:

  • The correct Data Source SourceID was used for accurate comparison
  • When mapping the ItemID values from the ds_items table remember that:

    • The CAPM ID is the LocalID column
    • The Data Sources ID is the ItemID column

This should provide the necessary mapping between Data Source ItemID and CAPM Inventory ItemID (LocalID) values.