Hi @Deepa12 ,
Scenario Overview
- Source Table: Data coming from the source (e.g., Active Directory, discovery tool).
- Target Table: CMDB (e.g., cmdb_ci_computer).
- Logic:
- If the computer exists in both source and target:
- Update Computer exists = Yes.
- Update the Last login date.
- If the computer exists only in the source:
- If the computer exists only in the CMDB:
- Update Computer exists in AD = No.
try this transformation script
(function executeMapScript(source, target, map, log, isUpdate) {
// Check if the computer exists in the target table (CMDB)
var cmdbRecord = new GlideRecord('cmdb_ci_computer'); // Replace with the correct table name
cmdbRecord.addQuery('name', source.name); // Replace 'name' with the unique identifier field
cmdbRecord.query();
if (cmdbRecord.next()) {
// Computer exists in both source and target (CMDB)
target.u_computer_exists = 'Yes'; // Update the "Computer exists" field
target.u_last_login_date = source.u_last_login_date; // Update "Last login date" from source field
} else {
// Computer does not exist in the target (CMDB), no action
return;
}
// Check if the computer does not exist in the source but exists in CMDB
var sourceExists = false;
var sourceCheck = new GlideRecord('source_table'); // Replace with your source table name
sourceCheck.addQuery('name', cmdbRecord.name); // Match on unique field
sourceCheck.query();
if (!sourceCheck.hasNext()) {
// Computer exists in CMDB but not in source
target.u_computer_exists_in_ad = 'No'; // Update "Computer exists in AD"
}
})();
Source Table Query:
- Ensures you compare records between source and target based on a unique field (e.g., name, serial_number).
Target Table Query:
- Uses a GlideRecord query to determine if the computer exists in the CMDB.
Field Updates:
- Updates fields such as u_computer_exists, u_last_login_date, and u_computer_exists_in_ad only if the required conditions are met.
No Action:
- For cases where the computer exists only in the source, the script exits early with no updates.
Field Names: Update field names (e.g., name, u_computer_exists, u_last_login_date, u_computer_exists_in_ad) as per your table schema.
Table Names:
- Replace cmdb_ci_computer with your CMDB table name.
- Replace source_table with your actual source table name.
Transform Map Execution Timing:
- Use onBefore to prevent unnecessary writes to the target table.
- Use onAfter if updates should only occur after the record is inserted.
