Pull CMDB data from one instance to another instance

Gowtham29 · ‎09-18-2022

Hi Team,

I have to move the cmdb_ci table and cmdb_rel_ci table data from the PROD instance to the UAT instance on a scheduled basis. Daily I need to run a scheduled job to pull data from PROD to UAT instance whenever the records in PROD get updated or created. If the same record exists in UAT and PROD and any update is made in PROD then I need to update the same in UAT instead of creating a new record and if it is a new record then I need to create a record. Please help me to do the coding for this requirement.

Mahendra RC · ‎09-19-2022

Hello Gowtham,

My apologize I pasted the wrong code. Please check with below script:

(function () {
	var TABLE_NAME = "cmdb_ci_server";
	var RESOURCE_PATH = "/api/now/table/" + TABLE_NAME;
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint("https://<INSTANCE_NAME>.service-now.com/"+RESOURCE_PATH );
	request.setHttpMethod('GET');
	request.setQueryParameter("sysparm_query", "name=ApplicationServerHelpdesk");

	//Eg. UserName="admin", Password="admin" for this code sample.
	var user = 'admin';
	var password = 'admin';
	request.setBasicAuth(user,password);

	request.setRequestHeader("Accept","application/json");
	var response = request.execute();
	var responseBody = response.getBody();
	//gs.info(responseBody);
	var jsonResponse = JSON.parse(responseBody);
	var responseResult = jsonResponse["result"];
	for (var record in responseResult) {
		var recordOperation = "";
		var recordGuidValue = responseResult[record]["sys_id"];
		var cmdbServer = new GlideRecord(TABLE_NAME);
		cmdbServer.addEncodedQuery("sys_id=" + recordGuidValue);
		cmdbServer.query();
		if (!cmdbServer.hasNext()) {
		recordOperation = "insert";
		cmdbServer.newRecord();
		} else {
		recordOperation = "update";
		cmdbServer.next();
		}
		var updateRecord = false;
		var recordData = responseResult[record];
		for (var data in recordData) {
			var fieldName = data;
			var fieldValue = "";
			if (fieldName == "sys_id" && recordOperation == "insert") {
				fieldValue = recordData[data];
				cmdbServer.setNewGuidValue(fieldValue);
			} else if (typeof recordData[data] == "object") {
				fieldValue = recordData[data]["value"];
				//gs.info(fieldName + " : " + fieldValue);
			} else {
				if (fieldName != "sys_id") {
					fieldValue = recordData[data];
					//gs.info(fieldName + " : " + fieldValue);
				}
			}
			if (fieldName && fieldValue) {
				updateRecord = true;
				cmdbServer.setValue(fieldName, fieldValue);
			}
		}
		if (updateRecord) {
			if (recordOperation == "insert")
			cmdbServer.insert();
			else if (recordOperation == "update")
			cmdbServer.update();
		}
	}
})();

If it answered your question then please mark it correct and helpful.

Also I would appreciate if you can mark the above responses as well helpful.

Thanks

View solution in original post

Gowtham29 · ‎09-19-2022

Awesome, it now works as expected. I changed the table name to cmdb_ci in the given script. What I need to set in the below line according to my table (cmdb_ci). Can you please suggest? For testing purposes, I gave the static value as you gave the script, but not sure how I need to change this now

request.setQueryParameter("sysparm_query", "name=ApplicationServerHelpdesk");

Mahendra RC · ‎09-19-2022

Hello Gowtham,

That is difficult to answer because you need to set as per your requirement. For example if you want to get the record created/update at or after yesterday you can use the below query sys_created_on>=javascript:gs.beginningOfYesterday()^ORsys_updated_on>=javascript:gs.beginningOfYesterday()

So you can schedule the job to run at 12:05 AM and use this query so it will get all the record that were created/updated at or after yesterday.

If it answered your question then please mark it correct and helpful.

Also I would appreciate if you can mark the above responses as well helpful.

Thanks

Gowtham29 · ‎09-20-2022

Thanks, Mahendra, I will check this with my client and I update you accordingly