This documentation supports the 21.05 version of BMC Helix CMDB.

To view an earlier version, select the version from the Product version menu

Evaluating CMDB distribution by using database queries

To find 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 findDatabase query
Number of CIs per DatasetIdSELECT DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseElement GROUP BY DatasetId;
Number of relationships by DatasetSELECT DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship GROUP BY DatasetId;
Number of CIs by Dataset, ClassIdSELECT ClassId, DatasetId, COUNT(*) FROM BMC_CORE_BMC_BaseElement GROUP BY ClassId, DatasetId ORDER BY DatasetId;
Number of relationships by DatasetId, ClassIdSELECT ClassId, DatasetId, COUNT(*) FROM BMC_Core_BMC_BaseRelationship GROUP BY ClassId, DatasetId ORDER BY DatasetId;

To find unidentified CIs by DatasetID, ClassId

To findQueryReasons 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;

Presence of unidentified CIs in the golden dataset 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 in the golden dataset, 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 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 previous query for number of unidentified CIs in a specified source DatasetId.

To find CIs which are soft-deleted or orphans

To findQueryReasons for this query
Number of soft-deleted CIs by DatasetId, ClassIdSELECT ClassId, Datasetid, COUNT(*) FROM BMC_CORE_BMC_BaseElement where MarkAsDeleted = 1 GROUP BY ClassId, DatasetId ORDER BY DatasetId;This query helps you understand the impact of not purging data. Retaining data in the production dataset is not considered a 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, ClassIdSELECT ClassId, Datasetid, COUNT(*) FROM BMC_CORE_BMC_BaseRelationship where MarkAsDeleted = 1 GROUP BY ClassId, DatasetId ORDER BY DatasetId;This query helps you understand the impact of not purging data. Same as above.
Query of identification attribute values for soft-deleted hosts in golden datasetSELECT Status, HostName, Domain, SerialNumber, TokenId, isVirtual, Name FROM BMC_CORE_BMC_ComputerSystem where MarkAsDeleted = 1 AND DatasetId = 'BMC.ASSET' ORDER BY HostName

This query returns 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.

To find CIs in golden dataset but not in any source dataset

It is a best practice to merge the data in the golden dataset with a source dataset. For some features such as CMDB Explorer, the changes are made through a sandbox dataset which is then removed.  

To findQueryReasons 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 that were not merged from a sandbox or source datasetSELECT ClassId, COUNT( * ) FROM BMC_CORE_BMC_BaseElement WHERE DatasetId = 'BMC.ASSET' AND AttributeDatasourceList IS NULL GROUP BY ClassIdThe result indicates cases where CIs are directly created in BMC.ASSET. This indicates a need to enable the sandbox or follow the recommended procedure to import data to a source dataset and reconcile into the golden dataset. This differs from the query to find computer systems which exist in the BMC.ASSET dataset as it also returns results that were created properly and then purged from the source dataset.
Was this page helpful? Yes No Submitting... Thank you

Comments