Remove Inactive users from knowledge base table

Amrutha K V · ‎08-26-2023

Hi Team,

I have a requirement for removing the inactive managers[kb_managers] from knowledge base[kb_knowledge_base] table using scheduled job.

1. If multiple users[inactive and active users] are present in the Managers[kb_managers] field of Knowledge base [kb_knowledge_base] table, then we have to remove the inactive managers from kb_managers field from knowledge base table and keep the active managers as it is[not need to remove the active managers].

2. If inactive user is an Owner of Knowledge Base, then needs to send an email notification to KB managers that owner is terminated.

This is my Requirement. Could you please suggest and help me with the script for scheduled job if possible.

Thank you!!

Bert_c1 · ‎08-26-2023

Hi @Amrutha K V

Try the following in scripts background:

// Remove in-acvite managers from knowledge base
var kbase = new GlideRecord('kb_knowledge_base');
kbase.addQuery('kb_managers','!=', '');
kbase.query();
gs.info("inactiveManagers: Found " + kbase.getRowCount() + " records to process.");
while (kbase.next()) {
	gs.info("inactiveManagers: Title: " + kbase.title + " have managers: " + kbase.kb_managers);
	var managers = kbase.kb_managers.split(',');
	if (managers.length > 0) {
		var activeMgrs = [];
		var updateMgrs = false;
		for (i = 0; i < managers.length; i++) {
//			gs.info("inactiveManagers: Checking Manager: " + managers[i]);
			var usr = new GlideRecord('sys_user');
			usr.addQuery('sys_id', managers[i]);
			usr.addQuery('active', true);
			usr.query();
			if (usr.next()) {
//				gs.info("inactiveManagers: Manager: " + managers[i] + ", active = " + usr.active);
				activeMgrs.push(managers[i].toString());
			}
			else {
				// Account for in-active manager in list
				updateMgrs = true;
			}
		}
		// update knowledge base record with active managers
		if ((activeMgrs.length > 0) || (updateMgrs)) {
			kbase.kb_managers = activeMgrs.toString();
			gs.info("inactiveManagers: For: " + kbase.title + ", Setting new Managers = " + activeMgrs.toString() + ".");
//			kbase.update();
		}
	}
}

and after testing, uncomment the 'kbase.update();' line near the bottom. If that works for you, you can use that in a Scheduled Job script.

Update: added logic for when there is a single manager that is not active and should be removed.

Harish Bainsla · ‎08-26-2023

var inactiveUserCriteria = "active=false";

var kbGr = new GlideRecord("kb_knowledge_base");
kbGr.query();

while (kbGr.next()) {
var kbManagers = kbGr.kb_managers.toString();
var managersArray = kbManagers.split(',');

var activeManagersArray = [];
for (var i = 0; i < managersArray.length; i++) {
var manager = managersArray[i].trim();
var managerGr = new GlideRecord("sys_user");
if (managerGr.get("user_name", manager) && managerGr.getValue("active") == true) {
activeManagersArray.push(manager);
}
}

kbGr.kb_managers = activeManagersArray.join(", ");
kbGr.update();

var owner = kbGr.owner.toString();
var ownerGr = new GlideRecord("sys_user");
if (ownerGr.get("user_name", owner) && ownerGr.getValue("active") == false) {

gs.eventQueue("kb.owner.terminated", kbGr, ownerGr.email, activeManagersArray.join(","), kbGr.number);
}
}

SwarnadeepNandy · ‎08-27-2023

Hello @Amrutha K V,

To achieve your requirement of removing the inactive managers from the knowledge base table using a scheduled job, you can use a script that runs a query on the kb_knowledge_base table and checks the active status of the managers and owners. A scheduled job is a script that runs at a specified time or interval to perform a task or action.

To create a script for the scheduled job, you can follow these steps:

Navigate to System Definition > Scheduled Jobs and click New.
In the Name field, enter a name for the scheduled job, such as “Remove Inactive Managers from Knowledge Base”.
In the Run field, select “Script” from the drop-down list. This will enable you to write a custom script for the scheduled job.
In the Script field, enter the following code:

var kb = new GlideRecord('kb_knowledge_base'); // create a GlideRecord object for the kb_knowledge_base table
kb.query(); // query all the records in the table
while (kb.next()) { // loop through each record
  var managers = kb.kb_managers.getDisplayValue().split(','); // get the managers field value and split it by comma
  var activeManagers = []; // create an empty array to store the active managers
  for (var i = 0; i < managers.length; i++) { // loop through each manager
    var user = new GlideRecord('sys_user'); // create a GlideRecord object for the sys_user table
    user.addQuery('name', managers[i]); // add a query to find the user by name
    user.query(); // execute the query
    if (user.next()) { // if the user is found
      if (user.active == true) { // check if the user is active
        activeManagers.push(managers[i]); // add the user name to the active managers array
      }
    }
  }
  kb.kb_managers = activeManagers.join(','); // set the managers field value to the active managers array joined by comma
  kb.update(); // update the record

  var owner = kb.owner.getDisplayValue(); // get the owner field value
  var ownerUser = new GlideRecord('sys_user'); // create a GlideRecord object for the sys_user table
  ownerUser.addQuery('name', owner); // add a query to find the user by name
  ownerUser.query(); // execute the query
  if (ownerUser.next()) { // if the user is found
    if (ownerUser.active == false) { // check if the user is inactive
      var email = new GlideEmailOutbound(); // create a GlideEmailOutbound object to send an email notification
      email.setFrom('noreply@servicenow.com'); // set the sender address
      email.addTo(kb.kb_managers); // set the recipients to the active managers
      email.setSubject('Knowledge Base Owner Terminated'); // set the subject line
      email.setBody('The owner of the knowledge base ' + kb.name + ' has been terminated. Please assign a new owner as soon as possible.'); // set the body content
      email.send(); // send the email notification
    }
  }
}

In the Run section, select “Periodically” from the drop-down list and specify how often you want to run the scheduled job, such as daily, weekly, or monthly.
Click Submit to save the scheduled job.

I hope this helps.

Kind Regards,

Swarnadeep Nandy

Amrutha K V · ‎09-01-2023

Hi Team,

The above script is useful. But the above script is mentioned based on active user and finally it is adding active users to the manager field of KB and working fine.

Actually i wanted to do the script based on inactive users who updated on last week and finally remove the inactive users from managerfield of KB.

I tried the below script but not sure how to remove the inactive users from the array after this line :if (inactiveMgrs.length > 0) {. Thank you!

// Remove in-acvite managers from knowledge base

var kbase = new GlideRecord('kb_knowledge_base'); // create a GlideRecord object for the kb_knowledge_base table

kbase.addQuery('kb_managers', '!=', ''); //query the list of managers which is not empty

kbase.query();

while (kbase.next()) {

var managers = kbase.kb_managers.split(','); //get the kb_managers field value and split it by comma

if (managers.length > 0) {

var inactiveMgrs = []; //create an empty array to store the inactive managers

for (i = 0; i < managers.length; i++) { // loop through each manager

var usr = new GlideRecord('sys_user'); // create a GlideRecord object for the sys_user table

usr.addEncodedQuery('active=false^sys_updated_onONLast week@javascript&colon;gs.beginningOfLastWeek()@javascript&colon;gs.endOfLastWeek()');

usr.addQuery('sys_id', managers[i]); //add a query to find the user by sys_id ,mngr sysid

usr.query(); // execute the query

if (usr.next()) {

inactiveMgrs.push(managers[i].toString()); // add the user to the inactive managers array

}

if (inactiveMgrs.length > 0) {

// Remove the inactive users from array

// Needed help

kbase.update();

}