Scheduled job importing data but not updating the existing record

[varma2]

Hi All,

When ever in production instance newly record created   or updated extisting record in ( user table or group table ) it should be copied in lower instance 

Which mean replica of data when new record created or existing record got updated those change move automativally prod to our lower instance.

Below script in working for newly created records but not updating the existing recoed. please suggest below script should update the existing records as well

Below url is different i have provided for example .

(function () {
	var tableName = "sys_user";
	var RESOURCE_PATH = "/api/now/table/" + tableName;
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint("https://<YOUR_PROD_INSTNCE>.service-now.com/"+RESOURCE_PATH );
	request.setHttpMethod('GET');
		request.setQueryParameter("sysparm_query", "sys_created_onONToday@javascript&colon;gs.beginningOfToday()@javascript&colon;gs.endOfToday()^ORsys_updated_onONLast 2 hours@javascript&colon;gs.beginningOfLast2Hours()@javascript&colon;gs.endOfLast2Hours()");

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

	//gs.print(JSON.stringify(requestBody));
	//request.setRequestBody(JSON.stringify(requestBody));

	request.setRequestHeader("Accept","application/json");
	var response = request.execute();
	var responseBody = response.getBody();
	gs.print(responseBody);
	var jsonResponse = JSON.parse(responseBody);
	var responseResult = jsonResponse["result"];
	for (var record in responseResult) {
		var cmdbServer = new GlideRecord(sys_user);
		cmdbServer.newRecord();
		var insertRecord = false;
		var recordData = responseResult[record];
		for (var data in recordData) {
			var fieldName = "";
			var fieldValue = "";
			if (fieldName == "sys_id") {
				cmdbServer.setNewGuidValue(fieldValue);
			} else if (typeof recordData[data] == "object") {
				fieldName = data;
				fieldValue = recordData[data]["value"];
				gs.print(fieldName + " : " + fieldValue);
			} else {
				fieldName = data;
				fieldValue = recordData[data];
				gs.print(fieldName + " : " + fieldValue);
			}
			if (fieldName && fieldValue) {
				insertRecord = true;
				cmdbServer.setValue(fieldName, fieldValue);
			}
		}
		if (insertRecord) {
			cmdbServer.insert()
		}
	}
})();

Please suggest.

Thanks,

Varma 

[AnveshKumar M]

Hi @varma2 ,

We will fall back to GlideQuery instead of GlideRecord to get the more meaningful error message.

Replace the code in first for loop with the below one, and run the job. Please observe the log and find the error which is causing this issue.

	var recordData = responseResult[record];
	
	var usrGr = new GlideRecord('sys_user');
	
	if(usrGr.get('sys_id', recordData.sys_id)){
		try {
			new GlideQuery('sys_user')
				.where('sys_id', recordData.sys_id)
				.update(recordData);
		} catch (ex) {
			gs.info('VARMA GQ UPDATE ERROR: ' + ex);
		}
	}else{
		try {
			new GlideQuery('sys_user')
				.insert(recordData);
		} catch (ex) {
			gs.info('VARMA GQ INSERT ERROR: ' + ex);
		}
	}

[varma2 · Accepted solution]

Hi @AnveshKumar M ,

Thanks you so much your support. I run the job No logs are captured.

request.setRequestHeader("Accept","application/json");
var response = request.execute();
var responseBody = response.getBody();
gs.print(responseBody);
var jsonResponse = JSON.parse(responseBody);
var responseResult = jsonResponse["result"];
var recordData = responseResult[record];

var usrGr = new GlideRecord('sys_user');

if(usrGr.get('sys_id', recordData.sys_id)){
try {
new GlideQuery('sys_user')
.where('sys_id', recordData.sys_id)
.update(recordData);
} catch (ex) {
gs.info('VARMA GQ UPDATE ERROR: ' + ex);
}
}else{
try {
new GlideQuery('sys_user')
.insert(recordData);
} catch (ex) {
gs.info('VARMA GQ INSERT ERROR: ' + ex);
}

}
})();

[varma2]

Hi @AnveshKumar M ,

Thank you so much for your Help,I can't thank you enough the way you helped me personally.

Yes the code you shared with me working as expected.

(function() {
var tableName = "sys_user_grmember";
var RESOURCE_PATH = "/api/now/table/" + tableName;
var request = new sn_ws.RESTMessageV2();
request.setEndpoint("https://Developement.service-now.com/" + RESOURCE_PATH);
request.setHttpMethod('GET');
// request.setQueryParameter('sysparm_query', 'sys_created_onRELATIVEGT@hour@ago@24^NQsys_updated_onRELATIVEGT@hour@ago@24');
//request.setQueryParameter('sysparm_query', 'sys_created_onRELATIVEGT@hour@ago@24');
request.setQueryParameter('sysparm_query', 'sys_created_onRELATIVEGT@hour@ago@24');

request.setQueryParameter('sysparm_exclude_reference_link', 'true');
request.setQueryParameter('sysparm_query_no_domain', 'true');
//Eg. UserName="admin", Password="admin" for this code sample.
var user = 'ADMIN';
var password = '12345';
request.setBasicAuth(user, password);

request.setRequestHeader("Accept", "application/json");
var response = request.execute();

var responseBody = response.getBody();
//gs.print(responseBody);
var jsonResponse = JSON.parse(responseBody);

var responseResult = jsonResponse["result"];
//gs.info('HMPRA ' + JSON.stringify(responseResult[1]));
/*for (var key in responseResult){
gs.info('HMPRA: ' + key);
}*/

//var recordsData = responseResult[record];
for (var rkey in responseResult) {
var recordData = responseResult[rkey];
//gs.info('HMPRA2 :' + JSON.stringify(recordData));
var usrGr = new GlideRecord('sys_user_grmember');

if (usrGr.get('sys_id', recordData.sys_id)) {
try {
for (var attr in recordData) {
usrGr.setValue(attr, recordData[attr]);
}
if (usrGr.update()) {
gs.info('VARMA1: User record updated' + recordData['user_name']);
} else {
gs.info('VARMA1: User record update failed' + recordData['user_name']);
}
} catch (ex) {
gs.info('VARMA GQ UPDATE ERROR: ' + ex);
}
} else {
try {
usrGr.newRecord();
usrGr.setNewGuidValue(recordData.sys_id);
for (var attr in recordData) {
usrGr.setValue(attr, recordData[attr]);
}
if (usrGr.insert()) {
gs.info('VARMA1: User record inserted' + recordData['user_name']);
} else {
gs.info('VARMA1: User record insert failed' + recordData['user_name']);
}
} catch (ex) {
gs.info('VARMA GQ INSERT ERROR: ' + ex);
}

}

}
})();

Thanks,

Varma

[Amit Gujarathi]

HI @varma2 ,
I trust you are doing good.

To make the above script work for updating existing records, you can modify the sysparm_query parameter in the REST message to include records updated in the last two hours. Currently, the query only retrieves records created today or updated in the last two hours.

To modify the query to include records updated in the last two hours, you can replace the OR condition in the sysparm_query parameter with an AND condition, like this:

sys_created_onONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()^sys_updated_onONLast 2 hours@javascript:gs.beginningOfLast2Hours()@javascript:gs.endOfLast2Hours()

[varma2]

Hi @Amit Gujarathi 

I have tried above condition it not replicated in lower instence whence the scheduled job runs.

Even it not copy new records as well updated existing records.

Please suggest.

Thanks,

varma