ETL Issue for Asset Sync with child tables (Need Urgent solution)

[siddheshagn]

Currently ,the ETL process syncs asset tags from third party to ServiceNow. For certain classes like Kubernetes clusters, it matches only on name, but the Identification and Reconciliation Engine (IRE) needs both name and correlation_id. Since correlation_id isn’t populated, IRE treats the asset as new, causing duplicate records.

I am excluding specific classes like Kubernetes clusters and their subclasses from ETL matching using system properties:

• isac_inka.do_not_map.subclasses (value - cmdb_ci_vm_object,cmdb_ci_kuberntes
• isac_inka.do_not_map.classes - blank

So, this should prevent updates to these classes and avoids duplication

I have attached existing script along with the modified script seems like still it is not working as expected.

[AJ-TechTrek · Accepted solution]

Hi @siddheshagn ,

As per my understanding the the Core Problem is -

You’re reading the system properties:
* isac_inka.do_not_map.subclasses = cmdb_ci_vm_object,cmdb_ci_kuberntes
* isac_inka.do_not_map.classes = (blank)
…and building an exclusion list for sys_class_nameNOT IN... in your modified script.


However

1. Spelling mismatch
* The correct class name is cmdb_ci_kubernetes (not cmdb_ci_kuberntes).
* If the class name is misspelled in the property, GlideDBObjectManager.get().getTableExtensions() will return an empty list, so no exclusion is applied.

2. Exclusion query formatting
* In the encoded query string, ServiceNow expects a space after NOT IN:
sys_class_nameNOT INcmdb_ci_server,cmdb_ci_application

* won’t work reliably.
It should be:
sys_class_nameNOT IN cmdb_ci_server,cmdb_ci_application

3. Multiple class handling
* The current logic builds excludedClasses but doesn’t ensure unique values. If your list contains duplicates or the parent and child class are both present, it can behave inconsistently.

4. Execution context
* If this is an ETL transform script, you must ensure this logic runs before IRE tries to identify the CI. If it runs after IRE’s identification stage, it won’t prevent duplicates.

2 – Here is the Recommended Fix as per my understanding , you can try to use this -

Here’s a corrected and more reliable version of your modified script:

(function(batch, output) {
// Read system properties
var subclassProp = gs.getProperty('isac_inka.do_not_map.subclasses', '');
var classProp = gs.getProperty('isac_inka.do_not_map.classes', '');

var excludedClasses = [];

// Add subclasses
if (subclassProp) {
subclassProp.split(',').forEach(function(cls) {
cls = cls.trim();
if (!cls) return;
var extensions = GlideDBObjectManager.get().getTableExtensions(cls);
if (extensions) {
excludedClasses = excludedClasses.concat(extensions.toArray());
}
});
}

// Add specific classes
if (classProp) {
classProp.split(',').forEach(function(cls) {
cls = cls.trim();
if (cls) excludedClasses.push(cls);
});
}

// Remove duplicates
excludedClasses = Array.from(new Set(excludedClasses));

// Build exclusion query string
var exclusionQuery = '';
if (excludedClasses.length > 0) {
exclusionQuery = 'sys_class_nameNOT IN ' + excludedClasses.join(',');
}

for (var i = 0; i < batch.length; i++) {
var serialNumber = batch[i].u_nds_serial_number;
var sysName = batch[i].u_nds_sys_name;

var autoInkaNumber = 50000000;
var autoInka = new GlideRecord('sys_number');
autoInka.addQuery('category', 'cmdb_ci');
autoInka.query();
if (autoInka.next()) {
autoInkaNumber = parseInt(autoInka.number, 10);
}

var asset = 0;
var out = '';
var ciGR = new GlideRecord('cmdb_ci');

// Match by serial number + name
if (serialNumber && sysName) {
ciGR.addQuery('serial_number', serialNumber);
ciGR.addQuery('name', sysName);
if (exclusionQuery) ciGR.addEncodedQuery(exclusionQuery);
ciGR.query();
if (ciGR.next()) {
if (ciGR.asset_tag) {
asset = Number(ciGR.asset_tag);
if (asset > autoInkaNumber) out = ciGR.sys_class_name + ',serial_number';
} else {
out = ciGR.sys_class_name + ',serial_number';
}
}
}

// Match by name only
if (sysName && out === '') {
ciGR = new GlideRecord('cmdb_ci');
ciGR.addQuery('name', sysName);
if (exclusionQuery) ciGR.addEncodedQuery(exclusionQuery);
ciGR.query();
if (ciGR.next()) {
if (ciGR.asset_tag) {
asset = Number(ciGR.asset_tag);
if (asset > autoInkaNumber) out = ciGR.sys_class_name + ',name';
} else {
out = ciGR.sys_class_name + ',name';
}
}
}

output[i] = out;
}
})(batch, output);

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.
 

Thank You
AJ - TechTrek with AJ - ITOM Trainer
LinkedIn:- https://www.linkedin.com/in/ajay-kumar-66a91385/
YouTube:- https://www.youtube.com/@learnitomwithaj
Topmate:- https://topmate.io/aj_techtrekwithaj (Connect for 1-1 Session)
ServiceNow Community MVP 2025