This documentation applies to the 8.1 version of BMC Atrium Core, which is in "End of Version Support." You will not be able to leave comments.

To view the latest version, select the version from the Product version menu.

Database queries for evaluating CMDB data distribution

This topic was edited by a BMC Contributor and has not been approved.  More information.

Total number of CIs and relationships

Before looking at data that needs to be corrected, run these queries to determine the total amount of data distribution within the datasets grouped by classes.

To find Database query
Number of CIs per DatasetId SELECT DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseElement GROUP BY DatasetId;
Number of relationships by Dataset SELECT DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship GROUP BY DatasetId;
Number of CIs by Dataset, ClassId SELECT ClassId, DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseElement GROUP BY ClassId, DatasetId ORDER BY DatasetId;
Number of relationships by DatasetId, ClassId SELECT ClassId, DatasetId, COUNT(*) FROM BMC_Core_BMC_BaseRelationship GROUP BY ClassId, DatasetId ORDER BY DatasetId;

Unidentified CIs by DatasetID, ClassId

To find Query Reasons for this query
Number of unidentified Cis in the golden dataset, BMC.ASSET

SELECT ClassId, COUNT(*) FROM BMC_CORE_BMC_BaseElement WHERE ReconciliationIdentity = '0'  AND DatasetId= 'BMC.ASSET'

    GROUP BY ClassId ORDER BY ClassId;

There should never be any unidentified CIs in the golden dataset. 
This can cause unpredictable behavior. This typically means the CIs were created directly in BMC.ASSET instead of going through Reconciliation.

Investigate how they were created and correct them if the query returns more than zero,

Number of unidentified relationships in the golden dataset, BMC.ASSET

SELECT ClassId, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship WHERE ReconciliationIdentity = '0'  AND DatasetId= 'BMC.ASSET'

  GROUP BY ClassId ORDER BY ClassId;

There should never be any unidentified relationships in the golden dataset.  Check whether the related CIs are identified as described above, then correct the relationships.
This condition is extremely rare, and would only happen if relationships were created directly in BMC.ASSET. 

Number of unidentified Cis in a specified source DatasetId

In this example, we used DatasetId = 'BMC.ADDM'

SELECT ClassId, COUNT(*) FROM BMC_CORE_BMC_BaseElement

  WHERE ReconciliationIdentity = '0'  AND DatasetId = 'BMC.ADDM'

    GROUP BY ClassId ORDER BY ClassId;

Investigate identification errors during reconciliation. Always begin with investigating the BMC_ComputerSystem class because unidentifed computers will also prevent components from being identified. If the results do not include BMC_ComputerSystem, then investigate identification rules from classes where the computer would be a host. For example: BMC_IPEndpoint, BMC_Processor, etc.

Number of unidentified relationships in a specified source DatasetId

In this example, we used DatasetId = 'BMC.ADDM'

SELECT ClassId, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship WHERE ReconciliationIdentity = '0'  AND DatasetId= 'BMC.ADDM'

    GROUP BY ClassId ORDER BY ClassId;

Investigate identification errors during reconciliation. Results of this query will reflect the results of the above query for CIs.

CIs which are soft-deleted or orphans

To find Query Reasons for this query
Number of soft-deleted CIs by DatasetId, ClassId SELECT ClassId, Datasetid, COUNT(*) FROM BMC_CORE_BMC_BaseElement where MarkAsDeleted = 1 GROUP BY ClassId, DatasetId ORDER BY DatasetId; This will help you understand the impact of not purging data. Retaining data in the production dataset is not considered best practice, and can cause unexpected behavior if the Hostname, Domain, or SerialNumber are reused by another computer system that is replacing the original resource.
Number of soft-deleted relationships by DatasetId, ClassId SELECT ClassId, Datasetid, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship where MarkAsDeleted = 1 GROUP BY ClassId, DatasetId ORDER BY DatasetId; This will help you understand the impact of not purging data. Same as above.
Query of identification attribute values for soft-deleted hosts in golden dataset SELECT Status, HostName, Domain, SerialNumber, TokenId, isVirtual, Name FROM BMC_CORE_BMC_ComputerSystem where MarkAsDeleted = 1 AND DatasetId = 'BMC.ASSET' ORDER BY HostName

This will return a list of areas of risk for reused hostnames which remain as purged data in BMC.ASSET. Check if any of the Hostname or SerialNumbers for these soft-deleted computer systems will be re-used by new hosts in the environment.  If so, purge the data in golden dataset.

CIs in golden dataset but not in any source dataset

It is a best practice that data in the golden dataset should be merged from a source dataset. For some features such as Atrium Explorer, the changes are made through a sandbox dataset which is then removed.  

To find Query Reasons for this query
Computer Systems which exist in the BMC.ASSET dataset but not in any source dataset.
Note: This query can consume a lot of database resources and may not finish.
SELECT Name, InstanceId, ReconciliationIdentity, DatasetId FROM BMC_CORE_BMC_ComputerSystem WHERE DatasetId = 'BMC.ASSET' AND ReconciliationIdentity NOT IN (SELECT ReconciliationIdentity FROM BMC_CORE_BMC_ComputerSystem where DatasetId != 'BMC.ASSET') The result indicates ComputerSystems which exist in BMC.ASSET but are not presently in any other dataset. This can occur if either: the data was created directly in BMC.ASSET, or if the data was created in another dataset and purged from the source dataset or sandbox dataset.
CIs in golden dataset which were not merged from a sandbox or source dataset SELECT ClassId, COUNT( * ) FROM BMC_CORE_BMC_BaseElement WHERE DatasetId = 'BMC.ASSET' AND AttributeDatasourceList IS NULL GROUP BY ClassId The result indicates cases where CIs are directly created in BMC.ASSET. This indicates a need a enable the sandbox or following recommended procedure to import data to a source dataset and reconcile into the golden dataset. This differs from the above query because the above query also returns results that were created properly and then purged from the source dataset.

This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.

Comments

  1. Young So

    Here is query I found on BMC community that looks for orphan records.

    SELECT count(*) Orphans FROM BMC_CORE_BMC_BaseElement be WHERE DatasetId = 'BMC.ADDM' and Company like 'NORTH%' and ClassId not in ('BMC_PERSON','BMC_BUSINESSSERVICE','BMC_BUSINESSPROCESS','BMC.CORE:BMC_BUSINESSPROCESS','BMC_COMPUTERSYSTEM','BMC_CLUSTER','BMC_IPCONNECTIVITYSUBNET') and ( (MarkAsDeleted is null) or (MarkAsDeleted = 0) ) and InstanceId not in (select Destination_InstanceId from bmc_core_bmc_baserelationship where DatasetId = 'BMC.ADDM' and ( (MarkAsDeleted is null) or (MarkAsDeleted = 0) ))

    Jul 08, 2014 05:00