Script to check who has ever approved or been assigned to tasks

[yjaques]

If you're like us, you're always struggling to keep the number of licenses you need under control. I was recently tasked with this since we were so far over our license limits. I made a quick script in order to figure out who were just the "ceremonial" approvers who wanted the honor of an approval role, same thing for the ITIL roles. What this background script does is iterate through change requests, change tasks, incidents, problems and approvals and grab the assigned_to and approver names, before deduping and printing them to the log.

WARNING: if you have a huge amount of rows in those tables you could bring your instance to its knees, in which case be sure and get a clone to your test instance first, or add some query parameters to just grab the last weeks or months of records.

var tables = ['change_request','change_task','incident','problem','sysapproval_approver'];

var licenses = [];

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

var rec = new GlideRecord(tables[i]);

  rec.query();

  while (rec.next()) {

        if(typeof rec.assigned_to != "undefined" && rec.assigned_to != '')

                licenses.push(rec.assigned_to.getDisplayValue());

        if(typeof rec.approver != "undefined" && rec.approver != '')

                licenses.push(rec.approver.getDisplayValue());

            }

}

var arrayUtil = new ArrayUtil();

licenses = arrayUtil.unique(licenses);

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

      logMe(licenses[i]);

}

function logMe(message) {

      if(gs) {

          gs.addInfoMessage(message);

      } else {

          log.info(message);

      }      

}

[yjaques]

So today I decided to alter the script a bit so that now it gathers everyone who has any active role in the system at all and thus requires a license, and I subtracted from this group the people that have actually done something in the system (in terms of change requests, change tasks, incidents, problems and approvals) so it's not perfect in that it maybe won't show admins for example or other specialized roles, it's very change management oriented, but can be easily tuned to your needs by altering the tables that are searched for actions (you might also need to look at a different attribute to get the user depending on the table. here it is:

var tables = ['change_request','change_task','incident','problem','sysapproval_approver'];

var activeUsers = [];

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

var rec = new GlideRecord(tables[i]);

  rec.query();

  while (rec.next()) {

        if(tables[i] != 'sysapproval_approver') {

                              if(rec.assigned_to != '')

                                      activeUsers.push(rec.assigned_to.getDisplayValue());

        } else {

                              if(rec.approver != '')

                                      activeUsers.push(rec.approver.getDisplayValue());

              }

            }

}

//dedupe

var arrayUtil = new ArrayUtil();

activeUsers = arrayUtil.unique(activeUsers);

var users = [];

//get list of all users that have a role and thus incur a license

var rec = new GlideRecord('sys_user_role');

  rec.query();

  while (rec.next()) {

                      var rec2 = new GlideRecord('sys_user_has_role');

                      rec2.addQuery('role', rec.sys_id);

                      rec2.addQuery('state', 'active');

                      rec2.query();

                      while (rec2.next()) {

                              users.push(rec2.user.getDisplayValue());

                      }

            }

//dedupe

users = arrayUtil.unique(users);

//intersect

var inactiveUsers = arrayUtil.diff(users, activeUsers);

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

      gs.addInfoMessage(inactiveUsers[i]);

}