Evaluating CMDB distribution by using database queries

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;

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.

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 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, ClassId

SELECT 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 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 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.

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 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 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.