Transforming data on the cmdb tables and setting coalesces best practices

[mballinger]

Hello,

I've recently been asked to work on cmdb related items at work and don't have much familiarity with processes. One of the items I am confused about is the cmdb_ci and then the child tables outside of it. When you create a transform map for a particular table such as cmdb_ci_netgear, and coaelesce on a particular field map, it prevents duplicates on the cmdb_ci_netgear table. But, if someone were to take another transform map and coaelsce on the same field, you will now see 2 records on the cmdb_ci table. Each table will have 1 unique record, but the cmdb_ci table will have 2 possible duplicate records. 

How can I prevent duplicates from going to the cmdb_ci table? would it be something as simple as setting the target table to the cmdb_ci table instead of using one of the child tables?

Thanks!

[Tony Chatfield1 · Accepted solution]

Hi,  a single before insert/update BR on cmdb_ci to abort your insert\update action if the 'current' values already exist would cover off transforms and UI additions\updates;
Or you could add a beforeTransform script to each transform map to check and abort\ignore if the values already exist in a query of cmdb_ci.

[cassieb_]

First, I recommend that any integration that is creating/updating records in the CMDB should use the Identification & Reconciliation Engine (IRE). Service Graph connectors and the IntegrationHub ETL do this automatically. You will need to know what you are licensed to use.

If using a transform map, check out this document:

https://docs.servicenow.com/bundle/sandiego-servicenow-platform/page/product/configuration-managemen...

If your CI's from a import cross multiple classes pay special attention to the section "Specify multiple target tables for an import set"

Second, unless the integration is coming from SCCM, do not use the "correlation_id" field. The SCCM Service Graph connector is now free, and I would recommend using it. Instead, create a new field in the base CI form to capture a unique ID that doesn't change from the source being imported.

• Example: I have an integration from one ServiceNow instance for a Managed Service Provider to a Client's ServiceNow instance where we capture the sys_id of the CI from the Managed Service Provider in a custom field "u_provider_sys_id"
• Each integration should have it's own custom field to capture that unchanging unique id if not using a Service Graph connector.

Third, create identification rules in the Class Manager to ensure that duplicates are not created.

• Example: On the cmdb_ci_hardware table create an identification rule for your custom field with a priority that puts it before Serial number. 
• There should be an identification rule for each source's custom unique id field

Fourth, create reconciliation rules so that precedence can be set from the various sources (e.g. ServiceNow Discovery, SCCM, custom integrations)

Before you start this, I would highly recommend going through the NowLearning training for the CMDB there is a fundamentals path you can find at https://nowlearning.service-now.com/lxp?id=learning_path_prev&path_id=c120bb5bdbd0c8103e3aaca2ca9619.... The training also provides a simulator which I found to be very useful.