Inactive Users are coming, when data imported from Transform Maps.

AbdurRahmanSnow · ‎01-22-2025

Good evening.
We have an application called Access Recertification Audit. There, once we upload data/excel sheet, then inactive users are getting fetched instead of active users.
The requirement is, if Active users come, automatically there Manager will come under Respondent column and with user's correct User ID.

But this is not happening.

We have onBefore Transform Script It is below.

var GLOBAL_grUser = new UARUtil().getUserByEmail(source.getValue('u_email_address'));

var GLOBAL_grUserManager = new GlideRecord('sys_user');

if(GLOBAL_grUser.isValidRecord()) {

GLOBAL_grUserManager = GLOBAL_grUser.manager.getRefRecord();

}
________________________________________________________________________________________
The script include above: UARUtil. It is below:

var UARUtil = Class.create();

UARUtil.prototype = {

initialize: function() {

},

getCurrentYear: function() {

var gdNow = new GlideDate();

return gdNow.getYearNoTZ();

},

getCurrentQuarter: function() {

var gdNow = new GlideDate();

var monthNum = gdNow. getMonthNoTZ();

return ''+Math.ceil(monthNum / 3);

},

canStartAudit: function(grAccessRecertAudit) {

// check to ensure the current state is "Draft" (-5)

if(GlideFilter.checkRecord(grAccessRecertAudit, 'state!=-5')) {

return false;

}

// check to make sure there is at least one one draft user role recertification

var grUserRoleRecert = new GlideRecord('u_user_role_recertification');

grUserRoleRecert.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

grUserRoleRecert.addQuery('u_response', 'draft');

grUserRoleRecert.query();

return grUserRoleRecert.hasNext();

},

startAudit: function(grAccessRecertAudit) {

// update state of Draft User Role Recertifications

var grUserRoleRecert = new GlideRecord('u_user_role_recertification');

grUserRoleRecert.addQuery('u_response', 'draft');

grUserRoleRecert.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

grUserRoleRecert.query();

while(grUserRoleRecert.next()) {

grUserRoleRecert.setValue('u_response', 'awaiting_response');

grUserRoleRecert.update();

}

var gdtNow = new GlideDateTime();

var gdStart = gdtNow.getDate();

var gdEnd = this.addWorkingDays(gdtNow, gs.getProperty('uar_recert_duration'), gs.getProperty('uar_working_days_schedule'));

var gdFirstReminder = this.addWorkingDays(gdtNow, gs.getProperty('uar_recert_first_reminder'), gs.getProperty('uar_working_days_schedule'));

var gdSecondReminder = this.addWorkingDays(gdtNow, gs.getProperty('uar_recert_second_reminder'), gs.getProperty('uar_working_days_schedule'));

grAccessRecertAudit.setValue('u_start_date', gdStart.getValue());

grAccessRecertAudit.setValue('u_end_date', gdEnd.getValue());

grAccessRecertAudit.setValue('u_first_reminder_date', gdFirstReminder.getValue());

grAccessRecertAudit.setValue('u_second_reminder_date', gdSecondReminder.getValue());

grAccessRecertAudit.update();

// update state of Draft Responder Statuses

var grResponderStatus = new GlideRecord('u_responder_status');

grResponderStatus.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

grResponderStatus.addQuery('u_status', 'draft');

grResponderStatus.query();

while(grResponderStatus.next()) {

grResponderStatus.setValue('u_status', 'awaiting_response');

grResponderStatus.update();

gs.eventQueue('uar_responder_initiated', grResponderStatus, null, null);

}

},

addWorkingDays: function(gdStart, numDays, strScheduleId) {

gdStart = new GlideDateTime(gdStart || new GlideDateTime());

var gdtEnd = new GlideDateTime(gdStart);

var schedule = new GlideSchedule(strScheduleId);

gdtEnd.addDaysLocalTime(numDays);

var durDiff = schedule.duration(gdStart, gdtEnd);

var daysDiff = parseInt(durDiff.getDayPart());

while(daysDiff < numDays) {

gdtEnd.addDaysLocalTime(numDays - daysDiff);

durDiff = schedule.duration(gdStart, gdtEnd);

daysDiff = parseInt(durDiff.getDayPart());

}

return gdtEnd.getDate();

},

executeBulkResponse: function(strUserRoleRecertSysIds, strResponseValue, strLeavingDate) {

var result = {

success: false,

message: ''

};

var arrRespondersToRecalculate = [];

var gdLeavingDate = new GlideDate();

if(strResponseValue == 'user_is_leaving') {

// check that the leaveDate is in the future

var gdNow = new GlideDate();

gdLeavingDate.setDisplayValue(strLeavingDate);

if(gdLeavingDate.onOrBefore(gdNow)) {

result.message = 'Leaving date should be after ' + gdNow.getDisplayValue() + ' - It is currently ' + gdLeavingDate.getDisplayValue();

return result;

}

var grUserRoleRecert = new GlideRecord('u_user_role_recertification');

grUserRoleRecert.addQuery('sys_id', 'IN', strUserRoleRecertSysIds);

grUserRoleRecert.query();

var auditId = '';

while(grUserRoleRecert.next()) {

if(!auditId) {

auditId = grUserRoleRecert.getValue('u_access_recertification_audit');

}

if(arrRespondersToRecalculate.indexOf(grUserRoleRecert.getValue('u_responder_status')) == -1) {

arrRespondersToRecalculate.push(grUserRoleRecert.getValue('u_responder_status'));

}

if(strResponseValue == 'user_is_leaving') {

grUserRoleRecert.setValue('u_leave_date', gdLeavingDate.getValue());

}

grUserRoleRecert.setValue('u_response', strResponseValue);

grUserRoleRecert.update();

}

// sync the responder status records

var responderStatusUtil = new UARResponderStatusUtil();

for(var i = 0; i < arrRespondersToRecalculate.length; i++) {

responderStatusUtil.syncResponderStatus(arrRespondersToRecalculate[i]);

}

// update the progress of the access recertification audit

if(auditId) {

this.updateAuditProgress(auditId)

}

result.success = true;

return result;

},

updateAuditProgress: function(auditId) {

var total = 0;

var responded = 0

var grAccessRecertAudit = new GlideRecord('u_access_recertification_audit');

if(grAccessRecertAudit.get(auditId)) {

var gaTotalRecertifications = new GlideAggregate('u_user_role_recertification');

gaTotalRecertifications.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

gaTotalRecertifications.addAggregate('COUNT');

gaTotalRecertifications.query();

if(gaTotalRecertifications.next()) {

total = parseInt(gaTotalRecertifications.getAggregate('COUNT')) || 0;

}

// count of responded recertifications

var gaTotalRespondedRecertifications = new GlideAggregate('u_user_role_recertification');

gaTotalRespondedRecertifications.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

gaTotalRespondedRecertifications.addQuery('u_response', 'IN', 'access_required,access_not_required,user_is_leaving');

gaTotalRespondedRecertifications.addAggregate('COUNT');

gaTotalRespondedRecertifications.query();

if(gaTotalRespondedRecertifications.next()) {

responded = parseInt(gaTotalRespondedRecertifications.getAggregate('COUNT')) || 0;

}

grAccessRecertAudit.setValue('u_progress', parseFloat(((responded / total) * 100).toFixed(1)));

grAccessRecertAudit.update();

}

},

processReminders: function() {

var gdToday = new GlideDate();

// called by scheduled job "UAR Reminders"

var grFirstReminder = new GlideRecord('u_access_recertification_audit');

grFirstReminder.addQuery('u_first_reminder_date', gdToday.getValue());

grFirstReminder.query();

while(grFirstReminder.next()) {

this._sendReminders('uar_first_reminder', grFirstReminder);

}

var grSecondReminder = new GlideRecord('u_access_recertification_audit');

grSecondReminder.addQuery('u_second_reminder_date', gdToday.getValue());

grSecondReminder.query();

while(grSecondReminder.next()) {

this._sendReminders('uar_second_reminder', grSecondReminder);

}

},

_sendReminders: function(strEventName, grAccessRecertAudit) {

var grResponderStatus = new GlideRecord('u_responder_status');

grResponderStatus.addQuery('u_access_recertification_audit', grAccessRecertAudit.getUniqueValue());

grResponderStatus.addQuery('u_status', 'IN', 'awaiting_response,partially_responded');

grResponderStatus.query();

while(grResponderStatus.next()) {

gs.eventQueue(strEventName, grResponderStatus, null, null);

if(strEventName == 'uar_first_reminder') {

grResponderStatus.setValue('u_first_reminder_sent', true);

} else if (strEventName == 'uar_second_reminder') {

grResponderStatus.setValue('u_final_reminder_sent', true);

}

grResponderStatus.update();

}

},

getUserByEmail: function(email_address) {

var grUser = new GlideRecord('sys_user');

if(gs.nil(email_address)) {

return grUser;

}

if((grUser.get('email', email_address))) {

return grUser;

}

if(grUser.get('email', 'zzz_' + email_address)) {

return grUser;

}

if(grUser.get('u_upn', email_address)) {

return grUser;

}

grUser.initialize();

return grUser;

},

getUserByGrantee: function(grantee) {

var grUser = new GlideRecord('sys_user');

if(gs.nil(grantee)) {

return grUser;

}

grUser.addQuery('user_name', 'ENDSWITH', '\\'+ grantee);

grUser.query();

if(grUser.next()) {

return grUser;

}

grUser.initialize();

return grUser;

},

getUserManager: function(userId, asObject) {

var result = {sysId: '', displayValue: ''};

var grUser = new GlideRecord('sys_user');

if(grUser.get(userId)) {

result.sysId = grUser.getValue('manager');

result.displayValue = grUser.getDisplayValue('manager');

}

return asObject ? result : result.sysId;

},

queryCanBeAssociatedWithGRQ: function(encodedQuery) {

encodedQuery = encodedQuery || this._getEncodedQueryFromList();

// Query must contain conditions for the 'access recertification audit' and 'response'

// user must have the uar_user role

// example of a good query u_access_recertification_audit=f3f12f141b3ba5148ffddb5be54bcbca^u_response=awaiting_response^ORDERBYu_user

return encodedQuery.indexOf('u_access_recertification_audit=') != -1 && encodedQuery.indexOf('u_response') != -1 && gs.hasRole('uar_user');

},

_getEncodedQueryFromList: function() {

var uri = gs.action.getGlideURI();

var fixedQuery = uri.get('sysparm_fixed_query');

var dynamicQuery = uri.get('sysparm_query');

var queryArr = [];

if (!gs.nil(fixedQuery) && fixedQuery != 'null') {

queryArr.push(fixedQuery);

}

if (!gs.nil(dynamicQuery) && dynamicQuery != 'null') {

queryArr.push(dynamicQuery);

}

return GlideStringUtil.unEscapeHTML(queryArr.join('^'));

},

associateRequestToRoleRecerts: function(encodedQuery, requestId) {

if(gs.nil(encodedQuery) || !this.queryCanBeAssociatedWithGRQ(encodedQuery)) {

return;

}

var grRoleRecert = new GlideRecord('u_user_role_recertification');

grRoleRecert.addEncodedQuery(encodedQuery);

grRoleRecert.addNullQuery('u_access_removal_task');

grRoleRecert.query();

grRoleRecert.setValue('u_access_removal_task', requestId);

grRoleRecert.updateMultiple();

},

type: 'UARUtil'

};

_____________________________________________________________

The transform has the below configurations. Screenshot attached.

Ankur Bawiskar · ‎01-22-2025

@AbdurRahmanSnow

simply wherever you are querying sys_user use active check

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Ankur Bawiskar · ‎02-11-2025

@AbdurRahmanSnow

Hope you are doing good.

Did my reply answer your question?

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader