How to check the MDB for Configuration Items/Assets related to CORA rules.

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

Question: 

How can I check for the existence of CORA-ruled Configuration Items/Assets in the MDB database.

Environment:  

CA Service Desk manager 12.9, 14.1

Answer: 

Included below are a series of MSSQL Queries that can be run on the MDB database to check for Configuration Items(CI, Assets) according to the CORA rules.

In the below queries, '%xx%' stands for (part of) the name of the asset you are looking for; you should to replace it accordingly.
 
1. From Machine name into ca-asset:

select * from ca_asset where label like '%xx%'

2. From ca_asset into ca_logical_asset(using asset_uuid):

select * from ca_logical_asset where asset_uuid in (select asset_uuid from ca_asset where label like '%xx%')

ca_logical-asset_property will show the logical instances of the same asset.
E.G., if the same asset is registered by CORA with different DNS and/or MAC address but same HOST name, CORA recognizes it is the same assets and stores 2 logical instances in this table.

select * from ca_logical_asset_property where logical_asset_uuid in (select logical_asset_uuid from ca_logical_asset where assett_uuid in (select asset_uuid from ca_asset where label like '%xx%'))

3. From ca_logical_asset to ca_asset_source (using loigcal_asset_uuid):

select * from ca_asset_source where logical_asset_uuid in (select logical_asset_uuid from ca_logical_asset where assett_uuid in (select asset_uuid from ca_asset where label like '%xx%'))

4. From ca_asset_source into ca_discovered_hardware
(using asset_source_uuid):

select * from ca_discovered_hardware where asset_source_uuid in (select asset_source_uuid from ca_asset_source where logical_asset_uuid in (select logical_asset_uuid from ca_logical_asset where assett_uuid in (select asset_uuid from ca_asset where label like '%xx%')))

5. From ca_asset_source into UAPM/USD ca_owned_resource
(using asset_source_uuid):

select * from ca_owned_resource where asset_source_uuid in (select asset_source_uuid from ca_asset_source where logical_asset_uuid in (select logical_asset_uuid from ca_logical_asset where assett_uuid in (select asset_uuid from ca_asset where label like '%xx%')))

6. From ca_asset_source into NSM tng_managedobject
(using asset_source_uuid):

select uuid, label, address, asset_uuid from tng_managedobject where asset_uuid in (select asset_source_uuid from ca_asset_source where logical_asset_uuid in (select logical_asset_uuid from ca_logical_asset where assett_uuid in (select asset_uuid from ca_asset where label like '%xx%')))
 
Additional Information:

A more detailed explanation of CORA and the above information can be found at https://support.ca.com/phpdocs/0/common/impcd/r11/MDBMain/Doc/CORA_MDB_and_Assets_SC.pdf

 

File Attachments:
TEC463228.zip