Script: PPM Resource Management -- Fix duplicated capacity

[Alex240]

The following script determines and fixes users with duplicated record in resource_aggregate_daily and resource_aggregate_monthly tables. The script filter the records by category=capacity, and group it by user and period of time to determine whether there are or not duplicated records.

It also accepts a list of users (sys_id) to be analysed instead of analysing all the users (better performance and also allows to exclude determined users you might would like to).

It has two different execution modes: debug and fixing:

• The debug mode only determines and prints an inform with the duplicated record.
• The fixing mode determines the duplicated records, deletes the duplicated records in both mentioned tables, and runs an out-of-the-box script to re-populate the user capacity for that period of time.

How to use:

1. Pass in the list of users or an empty string as first argument
2. Pass in the mode (true = debug, false = fixing) as second argument
3. Pass in the start date and end date as third and fourth arguments respectively. Format: yyyy-mm-dd
4. Execute as a background script / fix script

(function (users, debug, start, end) {
  var startDate = gs.dateGenerate(start, 'start');
  var endDate = gs.dateGenerate(end, 'end');
  var arrayUsers = [];
  var logger = '\n-- START OF THE SCRIPT --';

  var gaAggregateMonthly = new GlideAggregate('resource_aggregate_monthly');

  !gs.nil(users) ? gaAggregateMonthly.addQuery('user', 'IN', users) : void[0];

  gaAggregateMonthly.addQuery('category', 'capacity');
  gaAggregateMonthly.addQuery('month_starts_on', '>=', startDate);
  gaAggregateMonthly.addQuery('month_starts_on', '<=', endDate);

  gaAggregateMonthly.addAggregate('COUNT', 'month_starts_on');
  gaAggregateMonthly.addHaving('COUNT', '>', '1');
  gaAggregateMonthly.groupBy('user');
  gaAggregateMonthly.groupBy('month_starts_on');
  
  gaAggregateMonthly.query();

  while (gaAggregateMonthly.next()) {
      arrayUsers.push(gaAggregateMonthly.getValue('user'));
  }

  var uniqueUsers = arrayUsers.filter(function (element, index) {
    return arrayUsers.indexOf(element) === index;
  });

  uniqueUsers.forEach(function(userID) {
    debug && (logger += '\nUser ID: ' + userID);

    var grAggregateMonthly = new GlideRecord('resource_aggregate_monthly');
    
    grAggregateMonthly.addQuery('user', userID);
    grAggregateMonthly.addQuery('category', 'IN', 'capacity,availability');
    grAggregateMonthly.addQuery('month_starts_on', '>=', startDate);
    grAggregateMonthly.addQuery('month_starts_on', '<=', endDate);
    
    grAggregateMonthly.query();

    // Fixing mode
    if (!debug) {
      grAggregateMonthly.deleteMultiple();

      var grAggregateDaily = new GlideAggregate('resource_aggregate_daily');
      
      grAggregateDaily.addQuery('user', userID);
      grAggregateDaily.addQuery('category', 'IN', 'capacity,availability');
      grAggregateDaily.addQuery('month_starts_on', '>=', startDate);
      grAggregateDaily.addQuery('month_starts_on', '<=', endDate);      
      
      grAggregateDaily.query();
      
      logger += '\nUser: ' + userID + ' - Aggregate daily: ' + grAggregateDaily.getRowCount();

      grAggregateDaily.deleteMultiple();

      // Recalculate user capacity
      var rmDailyAggregate = new RMDailyAggregate(userID, startDate, endDate);
      rmDailyAggregate.populateCapacity();
    
    } else {
      logger += '\nMonth capacity: ' + grAggregateMonthly.getRowCount();
    }

  });

  logger += '\n-- END OF THE SCRIPT --';

  gs.log(logger);

})('<sys_ids>', true, '2022-01-01', '2022-12-31');

‼️ Disclaimer: Please, always test the script in non-productive environments before running in a productive instance. Script was reviewed at the creation time but may break/get unexpected results due to instance versioning and SN API upgrades.