Pull CMDB data from one instance to another instance

[Gowtham29]

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.

[Gowtham29]

Hi Mahendra, Code is working. The record is created in UAT when I insert a record in PROD, but when I update the same record in PROD, in UAT, a new record is created with the update I have made. This means duplicate records are getting created on the update operation. May you please help me with this? 

NOTE: sys_id is different in both regions for the same record. sys_id should be the same, isn't it?

[Mahendra RC]

Hello Gowtham,

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.addQuery("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") {
				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

[Gowtham29]

Hi Mahendra, Still the same, duplicate records are getting created on the update operation. Kindly please help

[Mahendra RC]

Could you please share your script you are using?

[Mahendra RC · Accepted solution]

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