Transform Map to Compare Loaded Data with CI's IP address
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
I am loading some data into our CMDB from a third party application that has a server name, IP and some other information that isn't in our CMDB. I am using the IP address as the coalesce field. The IP addresses for these items in the CMDB are not held in the main CI's IP address field, but in the cmdb_ci_ip_address table. What is the best way to set up the field map to compare the loaded data's IP address with the IP address for our CIs so it doesn't make doubles of the items? I tried to set it in the field map, but when I run the transform it skips everything.
On field map screen. I have the target lookup being the ip address table and selecting the IP address. I would think it should look there, make a match and then update any additional information OR add any CIs that are missing in our CMDB vs what is in the data export.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Monday
If you would like to use a simple tool then look into IntegrationHub ETL.
Then you can map data to related tables in a samt and easy way
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thursday
Hi @bharris,
please follow the below steps to achieve( it worked for me on this use case)
During transformation, records are skipped instead of updating existing CIs or inserting new ones.
Step 1: Review the existing Transform Map configuration
Navigate to:
System Import Sets → Administration → Transform Maps
Open the existing Transform Map.
Verify the source table containing the imported server information.
Verify the target table. For example:
cmdb_ci_server, if importing server CIs.
cmdb_ci, if importing multiple CI types.
Review the existing field maps.
Remove the direct coalesce configuration against cmdb_ci_ip_address.
Important: The cmdb_ci_ip_address table is a related table, not the parent CI table. A normal field map lookup does not automatically resolve the related IP address record to its parent CI.
Step 2: Verify the IP address relationship
Navigate to the cmdb_ci_ip_address table and verify how the IP address records are associated with the parent CI.
You can use the following background script for testing:
var ip = '192.168.1.10';
var ipGR = new GlideRecord('cmdb_ci_ip_address');
ipGR.addQuery('ip_address', ip);
ipGR.query();
while (ipGR.next()) {
gs.info('IP Address: ' + ipGR.getValue('ip_address'));
gs.info('Parent CI Sys ID: ' + ipGR.getValue('cmdb_ci'));
gs.info('Parent CI Name: ' + ipGR.cmdb_ci.getDisplayValue());
}
Replace the IP address with an actual value from the import file.
This will help verify whether the incoming IP address exists and identify its associated CI.
Step 3: Recommended approach – Use CMDBTransformUtil
ServiceNow provides CMDBTransformUtil to process Import Set records through the CMDB Identification and Reconciliation Engine (IRE).
This allows ServiceNow to identify existing CIs and determine whether to update an existing CI or create a new CI based on the configured identification rules.
Navigate to:
System Import Sets → Administration → Transform Maps → Your Transform Map → Transform Scripts
Create an onBefore Transform Script.
Example:
(function runTransformScript(source, map, log, target) {
var cmdbUtil = new CMDBTransformUtil();
cmdbUtil.setDataSource('ImportSet');
cmdbUtil.identifyAndReconcile(source, map, log);
// Prevent the standard transform from inserting
// an additional CI after IRE processing.
ignore = true;
if (cmdbUtil.hasError()) {
log.error('IRE Processing Error: ' +
cmdbUtil.getError());
} else {
log.info('IRE Processing Successful: ' +
cmdbUtil.getOutputPayload());
}
})(source, map, log, target);
Step 4: Configure CI Identification Rules
Navigate to:
Configuration → Identification/Reconciliation → CI Identifiers
Identify the appropriate CI class, such as Server.
Review the existing identification rules.
Verify whether the identification rules support matching the incoming IP address against the IP address associated with the existing CI.
If required, configure an appropriate identification rule or identification section.
Ensure the identification process does not incorrectly identify different servers as the same CI.
The identification criteria should be based on reliable attributes. An IP address alone may not always be unique or stable because IP addresses can be reassigned or shared in certain network configurations.
Step 5: Test using Identification Simulation
Before executing the full import:
Navigate to the CI Identification Simulation functionality.
Provide a test payload containing the server name and IP address.
Execute the identification simulation.
Verify whether ServiceNow identifies the existing CI.
Review the identification result and confirm the correct CI is selected.
Test with an IP address that does not exist in the CMDB to verify the expected insertion behavior.
Step 6: Validate the Transform Results
Execute the Transform Map against a small set of test records.
Validate the following scenarios:
IP exists in CMDB | Existing CI is identified and updated |
IP does not exist | New CI is created, subject to identification rules |
Multiple CIs have the same IP | Identification ambiguity is handled; no arbitrary CI update |
Invalid IP address | Record is rejected or handled according to validation rules |
Also review:
Import Set Run history.
Transform History.
Transform Script logs.
IRE processing results.
CI records in the target table.
Related records in cmdb_ci_ip_address.
💡If my response helped, please mark it as correct ✅and close the thread 🔒— this helps future readers find the solution faster! 🙏
thankyou
Chiranjeevi
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Friday - last edited Friday
Thanks for the assistance. I was getting the following error messages following your test script:
*** Script: Parent CI Sys ID: null
*** Script: Parent CI Name: undefined
I have confirmed that the IP I set in the IP value is in the cmdb_ci_ip_address table. After some troubleshooitng I found that the script was incorrect because the "cmdb_ci" field was actually "nic.cmdb_ci" as the reference is made on the NIC card. In addition, it needs to be getUniqueValue.
On Step 4 I do not see any base cmdb_ci_server or Windows / Linux related CI tables as those are the OS that will be imported.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sunday
Hi @bharris ,
Thank you for sharing your findings and for identifying the issue in the test script.
You are correct regarding the IP address relationship. The cmdb_ci_ip_address table maintains its association with the parent CI through the NIC reference (nic.cmdb_ci), rather than through a direct cmdb_ci reference.
The corrected approach is to retrieve the associated NIC record first and then follow its cmdb_ci reference to identify the parent CI.
Corrected validation script:
var ip = '192.168.1.10';
var ipGR = new GlideRecord('cmdb_ci_ip_address');
ipGR.addQuery('ip_address', ip);
ipGR.query();
while (ipGR.next()) {
gs.info('IP Address: ' + ipGR.getValue('ip_address'));
var nicGR = ipGR.nic.getRefRecord();
if (nicGR && nicGR.isValidRecord()) {
var ciGR = nicGR.cmdb_ci.getRefRecord();
if (ciGR && ciGR.isValidRecord()) {
gs.info('Parent CI Sys ID: ' +
ciGR.getUniqueValue());
gs.info('Parent CI Name: ' +
ciGR.getDisplayValue());
} else {
gs.info('No associated parent CI found.');
}
} else {
gs.info('No associated NIC record found.');
}
}
Regarding Step 4 – CI Identification Rules:
You do not necessarily need to configure separate identification rules for every operating system class.
Windows Server and Linux Server CIs are represented by specific CI classes within the CMDB class hierarchy. Identification rules may be inherited from their parent classes, depending on the class configuration.
Please perform the following checks:
Navigate to Configuration → Identification/Reconciliation → CI Class Manager.
Search for the relevant Windows Server and Linux Server CI classes.
Review their class hierarchy and identify their parent classes.
Navigate to the identification rules for the relevant CI classes.
Verify whether the identification rules are inherited from the parent class or explicitly configured for the child class.
Confirm that the identification criteria include reliable attributes that can uniquely identify the incoming server CI.
Important: The identification rule should be configured against the actual CI class being populated by the Transform Map. Avoid introducing IP address-only identification criteria, as IP addresses can be reassigned or shared across different systems.
One additional point to verify:
Please confirm whether your Transform Map targets cmdb_ci_server or a more specific Windows/Linux server CI class.
This distinction is important because IRE evaluates the identification rules associated with the target CI class and its applicable class hierarchy.
Once the correct CI class and identification rules are established, validate the incoming records using Identification Simulation before executing the complete import.
Thank you again for sharing the correction. It helps clarify the relationship between IP address records, NIC records, and their parent CIs in the CMDB.
💡If my response helped, please mark it as correct ✅and close the thread 🔒— this helps future readers find the solution faster! 🙏
thankyou
Chiranjeevi R
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Monday - last edited Monday
Thanks for your notes and suggestions. I have set up the onBefore as described in Step 3 and it seems working with a test import of one server. It marks it "ignored" by onBefore script and does not add an additional CI. If I used an IP not in the CMDB, it also marks it ignored by the onBefore but actually adds it as a CI. I noticed it added the IP on the CI entry itself and not as an entry on the cmdb_ci_ip_address.