Pls advise the transform map script:

[Deepa12]

Please advise the transform map script, If computer exists in both source and target table, update these fields in CMDB:
Update the field:  Computer exists = yes
last login date.

If computer exists only source but does not exist in target (CMDB):
No action

If computer does not exist in source, but exists in CMDB, update this field in CMDB.

Computer exists in AD = no. Thanks.

[Community Alums · Accepted solution]

Hi @Deepa12 ,

Scenario Overview

1. Source Table: Data coming from the source (e.g., Active Directory, discovery tool).
2. Target Table: CMDB (e.g., cmdb_ci_computer).
3. 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:
     • No action.
   • 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.

[Keshav72]

Hi @Deepa12 
For this requirement you have to write two transform map script
1. onBefore 
2. onComplete

try with below code.

If you find my response helpful in resolving your query, please mark it as helpful.
Thank you!

Regards,
Keshav

onBefore script

var comp = new GlideRecord("cmdb_ci_computer");
	comp.addQuery('asset_tag',source.asset_tag); 
	comp.addQuery("name",source.name);
	comp.query();
	if(comp.next()){  // computer present in source and target
		comp.computer_exit_in_ad="Yes";
		comp.last_login_date = source.last_login_date;
		comp.update();
	}
	else{ // computer present source but not in target

		ignore = true; // no action required
	}

onComplete

//Computer present in target and but not in source

	var computer = new GlideRecord("cmdb_ci_computer"); // exiting data available in target table
	computer.query();
	while(next()){
		var comp = new GlideRecord("souce_table"); // source table data
		comp.addQuery("asset_tag",computer.asset_tage);
		comp.addQuery("name",computer.name);
		comp.query();
		if(!comp.next()){
			computer.computer_exit_in_ad="no"; // Computer exists in AD = no
			computer.update();
		}
	}

[Ankur Bawiskar]

@Deepa12 

please share what field maps have you configured and what transform script you have used

you need to use onBefore to ignore the insert operation

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
    if (action == 'insert')
        ignore = true;

})(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Deepa12]

I have attached field transform map.