Remove InActive Users from Tables

[Amrutha K V]

Hi Team,

I have to remove the inactive users from the tables[Scheduled Email of Report (sysauto_report),Notifications
(sysevent_email_action) and Knowledge Base(kb_knowledge_base)] via Scheduled Job.

Note: If multiple users are present[inactive user and active user] in a table, then remove the inactive user and keep the active user as it is.

Scheduled Email of Report (sysauto_report)
1. If multiple users are receiving the email report, then just remove the inactive user (identified on the basis of active flag field on user table) from the Users field.
2. If the email report contains only inactive user as recipient i.e. Users field, remove the user from 'Users' field and deactivate the Scheduled Email of Report record. 
Notifications(sysevent_email_action)

1. If multiple users are receiving the email (applicable for non-event triggered emails), just remove the deactivated user
2. If email (applicable for non-event triggered emails) is getting sent just to inactive user, remove the inactive user from 'Users' field and deactive the notification.
Knowledge Base(kb_knowledge_base)

1. If inactive user is one of the manager, it needs to be removed from the manager list.
2. If inactive user is an Owner of KB, send an email to KB managers that owner is terminated.

This is my requirement. Could you please suggest and help that how to achieve this?

Thank you!!

[jMarshal · Accepted solution]

You will want to set up a scheduled job to run a script. Here is a link to documentation that describes how to do that:
 Automatically run a script of your choosing (servicenow.com)

Here is a sample script you could use for the first requirement (removing inactive users from the user_list field on sysauto_report and deactivating the scheduled email report, when user_list is empty):

// Query the sysauto_report table and get the user_list array

var grReport = new GlideRecord('sysauto_report');
grReport.query();

while (grReport.next()) {
var userList = grReport.getValue('user_list');

// Check if the user_list field is not empty

if (userList) {
var userListArray = userList.split(',');

var newUsers = [];
var modified = false;

// Loop through the users in the user_list

for (var i = 0; i < userListArray.length; i++) {
var userId = userListArray[i].trim();

// Check if the user is inactive

var grUser = new GlideRecord('sys_user');
if (grUser.get(userId) && !grUser.active) {

gs.info('Removing inactive user ' + userId + ' from report ' + grReport.getDisplayValue());
modified = true;
} else {
newUsers.push(userId);
}

}

// Update user_list if changes were made

if (modified) {

grReport.setValue('user_list', newUsers.join(','));
grReport.update();

}

// Deactivate report if user_list is empty

if (newUsers.length === 0) {

gs.info('Deactivating report ' + grReport.getDisplayValue() + ' due to empty user list.');
grReport.setValue('active', false);
grReport.update();

}

}

}