how to restrict incident creation from calendar invites for DL

RudhraKAM · ‎12-05-2025

As of now we are creating an Incidents via Inbound action , but when there is a calendar invite , still it is creating Incident , How to restrict that ?

I am not able to restrict subject , as it can be any

pratyusha11 · ‎06-25-2026

var DRY_RUN = true; // Change to false only when approved to send emails

var overDueRecords = [];
var existingChangeEmails = [];
var existingTaskEmails = [];

// ============================================================
// STEP 1: Get breached CHG sys_ids from task_sla
// ============================================================
var breachedChgIds = [];

var chgSlaGR = new GlideRecord('task_sla');
chgSlaGR.addQuery('active', true);
chgSlaGR.addQuery('has_breached', true);
chgSlaGR.addQuery('sla.name', 'WF CHG Resolution');
chgSlaGR.addQuery('task.sys_class_name', 'change_request');
chgSlaGR.query();

while (chgSlaGR.next()) {
    var taskId = chgSlaGR.getValue('task');
    if (taskId && breachedChgIds.indexOf(taskId) < 0) {
        breachedChgIds.push(taskId);
    }
}

gs.print('Breached CHG IDs found: ' + breachedChgIds.length);

// ============================================================
// STEP 2: Aggregate on change_request using breached sys_ids
// ============================================================
if (breachedChgIds.length > 0) {
    var chgRecords = new GlideAggregate('change_request');
    chgRecords.addQuery('sys_id', 'IN', breachedChgIds.join(','));
    chgRecords.addQuery('production_system', true);
    chgRecords.addQuery('state', 'NOT IN', '3,4');
    chgRecords.addAggregate('COUNT', 'requested_by');
    chgRecords.orderByAggregate('COUNT', 'requested_by');
    chgRecords.orderBy('requested_by');
    chgRecords.orderBy('assignment_group');
    chgRecords.query();

    gs.print('CHG aggregate rows: ' + chgRecords.getRowCount());

    while (chgRecords.next()) {
        var chgCount = chgRecords.getAggregate('COUNT', 'requested_by');
        var chgOverDueObj = {};
        chgOverDueObj.is_user_active = false;
        chgOverDueObj.has_valid_email = false;
        chgOverDueObj.is_manager_active = false;
        chgOverDueObj.group = chgRecords.getValue('assignment_group');

        var requestedBy = chgRecords.requested_by.getRefRecord();
        if (requestedBy && requestedBy.isValidRecord() && isActiveUser(requestedBy)) {
            chgOverDueObj.is_user_active = true;
            chgOverDueObj.assigned_user_email = requestedBy.getValue('email');
            chgOverDueObj.assigned_name = requestedBy.getValue('u_preferred_name');
            setManagerDetails(requestedBy, chgOverDueObj);
        } else {
            setGroupManagerDetails(chgOverDueObj.group, chgOverDueObj);
        }

        chgOverDueObj.overdue_chg_count = chgCount;

        if (chgOverDueObj.is_user_active && chgOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(chgOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingChangeEmails.push(chgOverDueObj.assigned_user_email);
        } else if ((!chgOverDueObj.is_user_active || !chgOverDueObj.has_valid_email) &&
                   existingTaskEmails.indexOf(chgOverDueObj.group_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingTaskEmails.push(chgOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 3: Get breached Change Task sys_ids from task_sla
// ============================================================
var breachedCtaskIds = [];

var ctaskSlaGR = new GlideRecord('task_sla');
ctaskSlaGR.addQuery('active', true);
ctaskSlaGR.addQuery('has_breached', true);
ctaskSlaGR.addQuery('sla.name', 'WF CHG Resolution');
ctaskSlaGR.addQuery('task.sys_class_name', 'change_task');
ctaskSlaGR.query();

while (ctaskSlaGR.next()) {
    var ctaskId = ctaskSlaGR.getValue('task');
    if (ctaskId && breachedCtaskIds.indexOf(ctaskId) < 0) {
        breachedCtaskIds.push(ctaskId);
    }
}

gs.print('Breached Change Task IDs found: ' + breachedCtaskIds.length);

// ============================================================
// STEP 4: Aggregate on change_task using breached sys_ids
// ============================================================
if (breachedCtaskIds.length > 0) {
    var ctaskRecords = new GlideAggregate('change_task');
    ctaskRecords.addQuery('sys_id', 'IN', breachedCtaskIds.join(','));
    ctaskRecords.addQuery('change_request.production_system', true);
    ctaskRecords.addQuery('change_request.state', '!=', '5');
    ctaskRecords.addAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderByAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderBy('assigned_to');
    ctaskRecords.orderBy('assignment_group');
    ctaskRecords.query();

    gs.print('Change Task aggregate rows: ' + ctaskRecords.getRowCount());

    while (ctaskRecords.next()) {
        var ctaskCount = ctaskRecords.getAggregate('COUNT', 'assigned_to');
        var ctaskOverDueObj = {};
        ctaskOverDueObj.is_user_active = false;
        ctaskOverDueObj.has_valid_email = false;
        ctaskOverDueObj.is_manager_active = false;
        ctaskOverDueObj.group = ctaskRecords.getValue('assignment_group');

        var assignedTo = ctaskRecords.assigned_to.getRefRecord();
        if (assignedTo && assignedTo.isValidRecord() && isActiveUser(assignedTo)) {
            ctaskOverDueObj.is_user_active = true;
            ctaskOverDueObj.assigned_user_email = assignedTo.getValue('email');
            ctaskOverDueObj.assigned_name = assignedTo.getValue('u_preferred_name');
            setManagerDetails(assignedTo, ctaskOverDueObj);
        } else {
            setGroupManagerDetails(ctaskOverDueObj.group, ctaskOverDueObj);
        }

        ctaskOverDueObj.overdue_ctask_count = ctaskCount;

        if (ctaskOverDueObj.is_user_active && ctaskOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(ctaskOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingChangeEmails.push(ctaskOverDueObj.assigned_user_email);
        } else if ((!ctaskOverDueObj.is_user_active || !ctaskOverDueObj.has_valid_email) &&
                   existingTaskEmails.indexOf(ctaskOverDueObj.group_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingTaskEmails.push(ctaskOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 5: DRY RUN output / or fire notifications
// ============================================================
gs.print('--- Total overDueRecords: ' + overDueRecords.length + ' ---');

if (DRY_RUN) {
    for (var i = 0; i < overDueRecords.length; i++) {
        var rec = overDueRecords[i];
        gs.print('Would notify =>' +
                 ' | Email: ' + rec.assigned_user_email +
                 ' | Group Email: ' + rec.group_email +
                 ' | Name: ' + rec.assigned_name +
                 ' | CHG Count: ' + rec.overdue_chg_count +
                 ' | Task Count: ' + rec.overdue_ctask_count +
                 ' | User Active: ' + rec.is_user_active);
    }
    gs.print('DRY RUN complete - no emails sent');
} else {
    // Your existing notification logic goes here
    // e.g. gs.eventQueue(...) or direct email sending
}

pratyusha11 · ‎06-25-2026

var DRY_RUN = true; // Change to false only when approved

var overDueRecords = [];
var existingChangeEmails = [];
var existingTaskEmails = [];

// ============================================================
// HELPER: Get group details inline
// ============================================================
function getGroupDetails(groupSysId) {
    var groupObj = { group_email: '', manager_email: '', manager_name: '' };
    if (!groupSysId) return groupObj;

    var grpGR = new GlideRecord('sys_user_group');
    if (grpGR.get(groupSysId)) {
        groupObj.group_email = grpGR.getValue('email') || '';
        var grpManager = grpGR.manager.getRefRecord();
        if (grpManager && grpManager.isValidRecord() && grpManager.getValue('active') == 'true') {
            groupObj.manager_email = grpManager.getValue('email') || '';
            groupObj.manager_name = grpManager.getValue('u_preferred_name') || grpManager.getValue('name') || '';
        }
    }
    return groupObj;
}

// ============================================================
// HELPER: Get manager details inline
// ============================================================
function getManagerDetails(userGR) {
    var managerObj = { manager_email: '', manager_name: '', is_manager_active: false };
    if (!userGR || !userGR.isValidRecord()) return managerObj;

    var manager = userGR.manager.getRefRecord();
    if (manager && manager.isValidRecord() && manager.getValue('active') == 'true') {
        managerObj.is_manager_active = true;
        managerObj.manager_email = manager.getValue('email') || '';
        managerObj.manager_name = manager.getValue('u_preferred_name') || manager.getValue('name') || '';
    }
    return managerObj;
}

// ============================================================
// STEP 1: Get breached CHG sys_ids from task_sla
// ============================================================
var breachedChgIds = [];

var chgSlaGR = new GlideRecord('task_sla');
chgSlaGR.addQuery('active', true);
chgSlaGR.addQuery('has_breached', true);
chgSlaGR.addQuery('sla.name', 'WF CHG Resolution');
chgSlaGR.addQuery('task.sys_class_name', 'change_request');
chgSlaGR.query();

while (chgSlaGR.next()) {
    var taskId = chgSlaGR.getValue('task');
    if (taskId && breachedChgIds.indexOf(taskId) < 0) {
        breachedChgIds.push(taskId);
    }
}

gs.print('Breached CHG IDs found: ' + breachedChgIds.length);

// ============================================================
// STEP 2: Aggregate on change_request using breached sys_ids
// ============================================================
if (breachedChgIds.length > 0) {
    var chgRecords = new GlideAggregate('change_request');
    chgRecords.addQuery('sys_id', 'IN', breachedChgIds.join(','));
    chgRecords.addQuery('production_system', true);
    chgRecords.addQuery('state', 'NOT IN', '3,4');
    chgRecords.addAggregate('COUNT', 'requested_by');
    chgRecords.orderByAggregate('COUNT', 'requested_by');
    chgRecords.orderBy('requested_by');
    chgRecords.orderBy('assignment_group');
    chgRecords.query();

    gs.print('CHG aggregate rows: ' + chgRecords.getRowCount());

    while (chgRecords.next()) {
        var chgCount = chgRecords.getAggregate('COUNT', 'requested_by');
        var chgOverDueObj = {};
        chgOverDueObj.is_user_active = false;
        chgOverDueObj.has_valid_email = false;
        chgOverDueObj.is_manager_active = false;
        chgOverDueObj.group = chgRecords.getValue('assignment_group');
        chgOverDueObj.overdue_chg_count = chgCount;
        chgOverDueObj.assigned_user_email = '';
        chgOverDueObj.group_email = '';

        var requestedBy = chgRecords.requested_by.getRefRecord();
        if (requestedBy && requestedBy.isValidRecord() && requestedBy.getValue('active') == 'true') {
            chgOverDueObj.is_user_active = true;
            chgOverDueObj.assigned_user_email = requestedBy.getValue('email') || '';
            chgOverDueObj.assigned_name = requestedBy.getValue('u_preferred_name') || requestedBy.getValue('name') || '';
            chgOverDueObj.has_valid_email = (chgOverDueObj.assigned_user_email != '');

            var managerDetails = getManagerDetails(requestedBy);
            chgOverDueObj.is_manager_active = managerDetails.is_manager_active;
            chgOverDueObj.manager_email = managerDetails.manager_email;
            chgOverDueObj.manager_name = managerDetails.manager_name;
        } else {
            var chgGroupDetails = getGroupDetails(chgOverDueObj.group);
            chgOverDueObj.group_email = chgGroupDetails.group_email;
            chgOverDueObj.manager_email = chgGroupDetails.manager_email;
            chgOverDueObj.manager_name = chgGroupDetails.manager_name;
        }

        if (chgOverDueObj.is_user_active && chgOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(chgOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingChangeEmails.push(chgOverDueObj.assigned_user_email);
        } else if ((!chgOverDueObj.is_user_active || !chgOverDueObj.has_valid_email) &&
                   chgOverDueObj.group_email &&
                   existingTaskEmails.indexOf(chgOverDueObj.group_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingTaskEmails.push(chgOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 3: Get breached Change Task sys_ids from task_sla
// ============================================================
var breachedCtaskIds = [];

var ctaskSlaGR = new GlideRecord('task_sla');
ctaskSlaGR.addQuery('active', true);
ctaskSlaGR.addQuery('has_breached', true);
ctaskSlaGR.addQuery('sla.name', 'WF CHG Resolution');
ctaskSlaGR.addQuery('task.sys_class_name', 'change_task');
ctaskSlaGR.query();

while (ctaskSlaGR.next()) {
    var ctaskId = ctaskSlaGR.getValue('task');
    if (ctaskId && breachedCtaskIds.indexOf(ctaskId) < 0) {
        breachedCtaskIds.push(ctaskId);
    }
}

gs.print('Breached Change Task IDs found: ' + breachedCtaskIds.length);

// ============================================================
// STEP 4: Aggregate on change_task using breached sys_ids
// ============================================================
if (breachedCtaskIds.length > 0) {
    var ctaskRecords = new GlideAggregate('change_task');
    ctaskRecords.addQuery('sys_id', 'IN', breachedCtaskIds.join(','));
    ctaskRecords.addQuery('change_request.production_system', true);
    ctaskRecords.addQuery('change_request.state', '!=', '5');
    ctaskRecords.addAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderByAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderBy('assigned_to');
    ctaskRecords.orderBy('assignment_group');
    ctaskRecords.query();

    gs.print('Change Task aggregate rows: ' + ctaskRecords.getRowCount());

    while (ctaskRecords.next()) {
        var ctaskCount = ctaskRecords.getAggregate('COUNT', 'assigned_to');
        var ctaskOverDueObj = {};
        ctaskOverDueObj.is_user_active = false;
        ctaskOverDueObj.has_valid_email = false;
        ctaskOverDueObj.is_manager_active = false;
        ctaskOverDueObj.group = ctaskRecords.getValue('assignment_group');
        ctaskOverDueObj.overdue_ctask_count = ctaskCount;
        ctaskOverDueObj.assigned_user_email = '';
        ctaskOverDueObj.group_email = '';

        var assignedTo = ctaskRecords.assigned_to.getRefRecord();
        if (assignedTo && assignedTo.isValidRecord() && assignedTo.getValue('active') == 'true') {
            ctaskOverDueObj.is_user_active = true;
            ctaskOverDueObj.assigned_user_email = assignedTo.getValue('email') || '';
            ctaskOverDueObj.assigned_name = assignedTo.getValue('u_preferred_name') || assignedTo.getValue('name') || '';
            ctaskOverDueObj.has_valid_email = (ctaskOverDueObj.assigned_user_email != '');

            var ctaskManagerDetails = getManagerDetails(assignedTo);
            ctaskOverDueObj.is_manager_active = ctaskManagerDetails.is_manager_active;
            ctaskOverDueObj.manager_email = ctaskManagerDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskManagerDetails.manager_name;
        } else {
            var ctaskGroupDetails = getGroupDetails(ctaskOverDueObj.group);
            ctaskOverDueObj.group_email = ctaskGroupDetails.group_email;
            ctaskOverDueObj.manager_email = ctaskGroupDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskGroupDetails.manager_name;
        }

        if (ctaskOverDueObj.is_user_active && ctaskOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(ctaskOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingChangeEmails.push(ctaskOverDueObj.assigned_user_email);
        } else if ((!ctaskOverDueObj.is_user_active || !ctaskOverDueObj.has_valid_email) &&
                   ctaskOverDueObj.group_email &&
                   existingTaskEmails.indexOf(ctaskOverDueObj.group_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingTaskEmails.push(ctaskOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 5: DRY RUN output / or fire notifications
// ============================================================
gs.print('--- Total overDueRecords: ' + overDueRecords.length + ' ---');

if (DRY_RUN) {
    for (var i = 0; i < overDueRecords.length; i++) {
        var rec = overDueRecords[i];
        gs.print('Would notify =>' +
                 ' | User Active: ' + rec.is_user_active +
                 ' | Name: ' + rec.assigned_name +
                 ' | User Email: ' + rec.assigned_user_email +
                 ' | Group Email: ' + rec.group_email +
                 ' | Manager Email: ' + rec.manager_email +
                 ' | CHG Count: ' + rec.overdue_chg_count +
                 ' | Task Count: ' + rec.overdue_ctask_count);
    }
    gs.print('DRY RUN complete - no emails sent');
} else {
    // Your existing notification firing logic goes here
}

pratyusha11 · ‎06-25-2026

var DRY_RUN = true; // Change to false only when approved

var overDueRecords = [];
var existingChangeEmails = [];
var existingTaskEmails = [];

// ============================================================
// HELPER: Get group details inline
// ============================================================
function getGroupDetails(groupSysId) {
    var groupObj = { group_email: '', manager_email: '', manager_name: '' };
    if (!groupSysId) return groupObj;

    var grpGR = new GlideRecord('sys_user_group');
    if (grpGR.get(groupSysId)) {
        groupObj.group_email = grpGR.getValue('email') || '';
        var grpManager = grpGR.manager.getRefRecord();
        if (grpManager && grpManager.isValidRecord() && grpManager.getValue('active') == 'true') {
            groupObj.manager_email = grpManager.getValue('email') || '';
            groupObj.manager_name = grpManager.getValue('u_preferred_name') ||
                                    (grpManager.getValue('first_name') + ' ' + grpManager.getValue('last_name')) || '';
        }
    }
    return groupObj;
}

// ============================================================
// HELPER: Get manager details inline
// ============================================================
function getManagerDetails(userGR) {
    var managerObj = { manager_email: '', manager_name: '', is_manager_active: false };
    if (!userGR || !userGR.isValidRecord()) return managerObj;

    var manager = userGR.manager.getRefRecord();
    if (manager && manager.isValidRecord() && manager.getValue('active') == 'true') {
        managerObj.is_manager_active = true;
        managerObj.manager_email = manager.getValue('email') || '';
        managerObj.manager_name = manager.getValue('u_preferred_name') ||
                                  (manager.getValue('first_name') + ' ' + manager.getValue('last_name')) || '';
    }
    return managerObj;
}

// ============================================================
// STEP 1: Get breached CHG sys_ids from task_sla
// ============================================================
var breachedChgIds = [];

var chgSlaGR = new GlideRecord('task_sla');
chgSlaGR.addQuery('active', true);
chgSlaGR.addQuery('has_breached', true);
chgSlaGR.addQuery('sla.name', 'WF CHG Resolution');
chgSlaGR.addQuery('task.sys_class_name', 'change_request');
chgSlaGR.query();

while (chgSlaGR.next()) {
    var taskId = chgSlaGR.getValue('task');
    if (taskId && breachedChgIds.indexOf(taskId) < 0) {
        breachedChgIds.push(taskId);
    }
}

gs.print('Breached CHG IDs found: ' + breachedChgIds.length);

// ============================================================
// STEP 2: Aggregate on change_request using breached sys_ids
// ============================================================
if (breachedChgIds.length > 0) {
    var chgRecords = new GlideAggregate('change_request');
    chgRecords.addQuery('sys_id', 'IN', breachedChgIds.join(','));
    chgRecords.addQuery('production_system', true);
    chgRecords.addQuery('state', 'NOT IN', '3,4');
    chgRecords.addAggregate('COUNT', 'requested_by');
    chgRecords.orderByAggregate('COUNT', 'requested_by');
    chgRecords.orderBy('requested_by');
    chgRecords.orderBy('assignment_group');
    chgRecords.query();

    gs.print('CHG aggregate rows: ' + chgRecords.getRowCount());

    while (chgRecords.next()) {
        var chgCount = chgRecords.getAggregate('COUNT', 'requested_by');
        var chgOverDueObj = {};
        chgOverDueObj.is_user_active = false;
        chgOverDueObj.has_valid_email = false;
        chgOverDueObj.is_manager_active = false;
        chgOverDueObj.group = chgRecords.getValue('assignment_group');
        chgOverDueObj.overdue_chg_count = chgCount;
        chgOverDueObj.assigned_user_email = '';
        chgOverDueObj.group_email = '';
        chgOverDueObj.assigned_name = '';
        chgOverDueObj.manager_email = '';
        chgOverDueObj.manager_name = '';

        var requestedBy = chgRecords.requested_by.getRefRecord();
        if (requestedBy && requestedBy.isValidRecord() && requestedBy.getValue('active') == 'true') {
            chgOverDueObj.is_user_active = true;
            chgOverDueObj.assigned_user_email = requestedBy.getValue('email') || '';
            chgOverDueObj.assigned_name = requestedBy.getValue('u_preferred_name') ||
                                          (requestedBy.getValue('first_name') + ' ' + requestedBy.getValue('last_name')) || '';
            chgOverDueObj.has_valid_email = (chgOverDueObj.assigned_user_email != '');

            var chgManagerDetails = getManagerDetails(requestedBy);
            chgOverDueObj.is_manager_active = chgManagerDetails.is_manager_active;
            chgOverDueObj.manager_email = chgManagerDetails.manager_email;
            chgOverDueObj.manager_name = chgManagerDetails.manager_name;
        } else {
            var chgGroupDetails = getGroupDetails(chgOverDueObj.group);
            chgOverDueObj.group_email = chgGroupDetails.group_email;
            chgOverDueObj.manager_email = chgGroupDetails.manager_email;
            chgOverDueObj.manager_name = chgGroupDetails.manager_name;
        }

        if (chgOverDueObj.is_user_active && chgOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(chgOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingChangeEmails.push(chgOverDueObj.assigned_user_email);
        } else if ((!chgOverDueObj.is_user_active || !chgOverDueObj.has_valid_email) &&
                   chgOverDueObj.group_email &&
                   existingTaskEmails.indexOf(chgOverDueObj.group_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingTaskEmails.push(chgOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 3: Get breached Change Task sys_ids from task_sla
// ============================================================
var breachedCtaskIds = [];

var ctaskSlaGR = new GlideRecord('task_sla');
ctaskSlaGR.addQuery('active', true);
ctaskSlaGR.addQuery('has_breached', true);
ctaskSlaGR.addQuery('sla.name', 'WF CTask Resolution');
ctaskSlaGR.addQuery('task.sys_class_name', 'change_task');
ctaskSlaGR.query();

while (ctaskSlaGR.next()) {
    var ctaskId = ctaskSlaGR.getValue('task');
    if (ctaskId && breachedCtaskIds.indexOf(ctaskId) < 0) {
        breachedCtaskIds.push(ctaskId);
    }
}

gs.print('Breached Change Task IDs found: ' + breachedCtaskIds.length);

// ============================================================
// STEP 4: Aggregate on change_task using breached sys_ids
// ============================================================
if (breachedCtaskIds.length > 0) {
    var ctaskRecords = new GlideAggregate('change_task');
    ctaskRecords.addQuery('sys_id', 'IN', breachedCtaskIds.join(','));
    ctaskRecords.addQuery('change_request.production_system', true);
    ctaskRecords.addQuery('change_request.state', '!=', '5');
    ctaskRecords.addAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderByAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderBy('assigned_to');
    ctaskRecords.orderBy('assignment_group');
    ctaskRecords.query();

    gs.print('Change Task aggregate rows: ' + ctaskRecords.getRowCount());

    while (ctaskRecords.next()) {
        var ctaskCount = ctaskRecords.getAggregate('COUNT', 'assigned_to');
        var ctaskOverDueObj = {};
        ctaskOverDueObj.is_user_active = false;
        ctaskOverDueObj.has_valid_email = false;
        ctaskOverDueObj.is_manager_active = false;
        ctaskOverDueObj.group = ctaskRecords.getValue('assignment_group');C

pratyusha11 · ‎06-25-2026

var DRY_RUN = true; // Change to false only when approved

var overDueRecords = [];
var existingChangeEmails = [];
var existingTaskEmails = [];

// ============================================================
// HELPER: Get group details inline
// ============================================================
function getGroupDetails(groupSysId) {
    var groupObj = { group_email: '', manager_email: '', manager_name: '' };
    if (!groupSysId) return groupObj;

    var grpGR = new GlideRecord('sys_user_group');
    if (grpGR.get(groupSysId)) {
        groupObj.group_email = grpGR.getValue('email') || '';
        var grpManager = grpGR.manager.getRefRecord();
        if (grpManager && grpManager.isValidRecord() && grpManager.getValue('active') == 'true') {
            groupObj.manager_email = grpManager.getValue('email') || '';
            groupObj.manager_name = grpManager.getValue('u_preferred_name') ||
                                    (grpManager.getValue('first_name') + ' ' + grpManager.getValue('last_name')) || '';
        }
    }
    return groupObj;
}

// ============================================================
// HELPER: Get manager details inline
// ============================================================
function getManagerDetails(userGR) {
    var managerObj = { manager_email: '', manager_name: '', is_manager_active: false };
    if (!userGR || !userGR.isValidRecord()) return managerObj;

    var manager = userGR.manager.getRefRecord();
    if (manager && manager.isValidRecord() && manager.getValue('active') == 'true') {
        managerObj.is_manager_active = true;
        managerObj.manager_email = manager.getValue('email') || '';
        managerObj.manager_name = manager.getValue('u_preferred_name') ||
                                  (manager.getValue('first_name') + ' ' + manager.getValue('last_name')) || '';
    }
    return managerObj;
}

// ============================================================
// STEP 1: Get breached CHG sys_ids from task_sla
// ============================================================
var breachedChgIds = [];

var chgSlaGR = new GlideRecord('task_sla');
chgSlaGR.addQuery('active', true);
chgSlaGR.addQuery('has_breached', true);
chgSlaGR.addQuery('sla.name', 'WF CHG Resolution');
chgSlaGR.addQuery('task.sys_class_name', 'change_request');
chgSlaGR.query();

while (chgSlaGR.next()) {
    var taskId = chgSlaGR.getValue('task');
    if (taskId && breachedChgIds.indexOf(taskId) < 0) {
        breachedChgIds.push(taskId);
    }
}

gs.print('Breached CHG IDs found: ' + breachedChgIds.length);

// ============================================================
// STEP 2: Aggregate on change_request using breached sys_ids
// ============================================================
if (breachedChgIds.length > 0) {
    var chgRecords = new GlideAggregate('change_request');
    chgRecords.addQuery('sys_id', 'IN', breachedChgIds.join(','));
    chgRecords.addQuery('production_system', true);
    chgRecords.addQuery('state', 'NOT IN', '3,4');
    chgRecords.addAggregate('COUNT', 'requested_by');
    chgRecords.orderByAggregate('COUNT', 'requested_by');
    chgRecords.orderBy('requested_by');
    chgRecords.orderBy('assignment_group');
    chgRecords.query();

    gs.print('CHG aggregate rows: ' + chgRecords.getRowCount());

    while (chgRecords.next()) {
        var chgCount = chgRecords.getAggregate('COUNT', 'requested_by');
        var chgOverDueObj = {};
        chgOverDueObj.is_user_active = false;
        chgOverDueObj.has_valid_email = false;
        chgOverDueObj.is_manager_active = false;
        chgOverDueObj.group = chgRecords.getValue('assignment_group');
        chgOverDueObj.overdue_chg_count = chgCount;
        chgOverDueObj.assigned_user_email = '';
        chgOverDueObj.group_email = '';
        chgOverDueObj.assigned_name = '';
        chgOverDueObj.manager_email = '';
        chgOverDueObj.manager_name = '';

        var requestedBy = chgRecords.requested_by.getRefRecord();
        if (requestedBy && requestedBy.isValidRecord() && requestedBy.getValue('active') == 'true') {
            chgOverDueObj.is_user_active = true;
            chgOverDueObj.assigned_user_email = requestedBy.getValue('email') || '';
            chgOverDueObj.assigned_name = requestedBy.getValue('u_preferred_name') ||
                                          (requestedBy.getValue('first_name') + ' ' + requestedBy.getValue('last_name')) || '';
            chgOverDueObj.has_valid_email = (chgOverDueObj.assigned_user_email != '');

            var chgManagerDetails = getManagerDetails(requestedBy);
            chgOverDueObj.is_manager_active = chgManagerDetails.is_manager_active;
            chgOverDueObj.manager_email = chgManagerDetails.manager_email;
            chgOverDueObj.manager_name = chgManagerDetails.manager_name;
        } else {
            var chgGroupDetails = getGroupDetails(chgOverDueObj.group);
            chgOverDueObj.group_email = chgGroupDetails.group_email;
            chgOverDueObj.manager_email = chgGroupDetails.manager_email;
            chgOverDueObj.manager_name = chgGroupDetails.manager_name;
        }

        if (chgOverDueObj.is_user_active && chgOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(chgOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingChangeEmails.push(chgOverDueObj.assigned_user_email);
        } else if ((!chgOverDueObj.is_user_active || !chgOverDueObj.has_valid_email) &&
                   chgOverDueObj.group_email &&
                   existingTaskEmails.indexOf(chgOverDueObj.group_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingTaskEmails.push(chgOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 3: Get breached Change Task sys_ids from task_sla
// ============================================================
var breachedCtaskIds = [];

var ctaskSlaGR = new GlideRecord('task_sla');
ctaskSlaGR.addQuery('active', true);
ctaskSlaGR.addQuery('has_breached', true);
ctaskSlaGR.addQuery('sla.name', 'WF CTask Resolution');
ctaskSlaGR.addQuery('task.sys_class_name', 'change_task');
ctaskSlaGR.query();

while (ctaskSlaGR.next()) {
    var ctaskId = ctaskSlaGR.getValue('task');
    if (ctaskId && breachedCtaskIds.indexOf(ctaskId) < 0) {
        breachedCtaskIds.push(ctaskId);
    }
}

gs.print('Breached Change Task IDs found: ' + breachedCtaskIds.length);

// ============================================================
// STEP 4: Aggregate on change_task using breached sys_ids
// ============================================================
if (breachedCtaskIds.length > 0) {
    var ctaskRecords = new GlideAggregate('change_task');
    ctaskRecords.addQuery('sys_id', 'IN', breachedCtaskIds.join(','));
    ctaskRecords.addQuery('change_request.production_system', true);
    ctaskRecords.addQuery('change_request.state', '!=', '5');
    ctaskRecords.addAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderByAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderBy('assigned_to');
    ctaskRecords.orderBy('assignment_group');
    ctaskRecords.query();

    gs.print('Change Task aggregate rows: ' + ctaskRecords.getRowCount());

    while (ctaskRecords.next()) {
        var ctaskCount = ctaskRecords.getAggregate('COUNT', 'assigned_to');
        var ctaskOverDueObj = {};
        ctaskOverDueObj.is_user_active = false;
        ctaskOverDueObj.has_valid_email = false;
        ctaskOverDueObj.is_manager_active = false;
        ctaskOverDueObj.group = ctaskRecords.getValue('assignment_group');
        ctaskOverDueObj.overdue_ctask_count = ctaskCount;
        ctaskOverDueObj.assigned_user_email = '';
        ctaskOverDueObj.group_email = '';
        ctaskOverDueObj.assigned_name = '';
        ctaskOverDueObj.manager_email = '';
        ctaskOverDueObj.manager_name = '';

        var assignedTo = ctaskRecords.assigned_to.getRefRecord();
        if (assignedTo && assignedTo.isValidRecord() && assignedTo.getValue('active') == 'true') {
            ctaskOverDueObj.is_user_active = true;
            ctaskOverDueObj.assigned_user_email = assignedTo.getValue('email') || '';
            ctaskOverDueObj.assigned_name = assignedTo.getValue('u_preferred_name') ||
                                            (assignedTo.getValue('first_name') + ' ' + assignedTo.getValue('last_name')) || '';
            ctaskOverDueObj.has_valid_email = (ctaskOverDueObj.assigned_user_email != '');

            var ctaskManagerDetails = getManagerDetails(assignedTo);
            ctaskOverDueObj.is_manager_active = ctaskManagerDetails.is_manager_active;
            ctaskOverDueObj.manager_email = ctaskManagerDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskManagerDetails.manager_name;
        } else {
            var ctaskGroupDetails = getGroupDetails(ctaskOverDueObj.group);
            ctaskOverDueObj.group_email = ctaskGroupDetails.group_email;
            ctaskOverDueObj.manager_email = ctaskGroupDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskGroupDetails.manager_name;
        }

        if (ctaskOverDueObj.is_user_active && ctaskOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(ctaskOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingChangeEmails.push(ctaskOverDueObj.assigned_user_email);
        } else if ((!ctaskOverDueObj.is_user_active || !ctaskOverDueObj.has_valid_email) &&
                   ctaskOverDueObj.group_email &&
                   existingTaskEmails.indexOf(ctaskOverDueObj.group_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingTaskEmails.push(ctaskOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 5: DRY RUN output / or fire notifications
// ============================================================
gs.print('--- Total overDueRecords: ' + overDueRecords.length + ' ---');

if (DRY_RUN) {
    for (var i = 0; i < overDueRecords.length; i++) {
        var rec = overDueRecords[i];
        gs.print('Would notify =>' +
                 ' | User Active: ' + rec.is_user_active +
                 ' | Name: ' + rec.assigned_name +
                 ' | User Email: ' + rec.assigned_user_email +
                 ' | Group Email: ' + rec.group_email +
                 ' | Manager Email: ' + rec.manager_email +
                 ' | Manager Name: ' + rec.manager_name +
                 ' | CHG Count: ' + rec.overdue_chg_count +
                 ' | Task Count: ' + rec.overdue_ctask_count);
    }
    gs.print('DRY RUN complete - no emails sent');
} else {
    // Your existing notification firing logic goes here
}

pratyusha11 · ‎06-25-2026

var DRY_RUN = true; // Change to false only when approved

var overDueRecords = [];
var existingChangeEmails = [];
var existingTaskEmails = [];

// ============================================================
// HELPER: Get group details inline
// ============================================================
function getGroupDetails(groupSysId) {
    var groupObj = { group_email: '', manager_email: '', manager_name: '' };
    if (!groupSysId) return groupObj;

    var grpGR = new GlideRecord('sys_user_group');
    if (grpGR.get(groupSysId)) {
        groupObj.group_email = grpGR.getValue('email') || '';
        groupObj.group_name = grpGR.getValue('name') || '';
        var grpManager = grpGR.manager.getRefRecord();
        if (grpManager && grpManager.isValidRecord() && grpManager.getValue('active') == 'true') {
            groupObj.manager_email = grpManager.getValue('email') || '';
            groupObj.manager_name = grpManager.getValue('u_preferred_name') ||
                                    (grpManager.getValue('first_name') + ' ' + grpManager.getValue('last_name')) || '';
        }
    }
    return groupObj;
}

// ============================================================
// HELPER: Get manager details inline
// ============================================================
function getManagerDetails(userGR) {
    var managerObj = { manager_email: '', manager_name: '', is_manager_active: false };
    if (!userGR || !userGR.isValidRecord()) return managerObj;

    var manager = userGR.manager.getRefRecord();
    if (manager && manager.isValidRecord() && manager.getValue('active') == 'true') {
        managerObj.is_manager_active = true;
        managerObj.manager_email = manager.getValue('email') || '';
        managerObj.manager_name = manager.getValue('u_preferred_name') ||
                                  (manager.getValue('first_name') + ' ' + manager.getValue('last_name')) || '';
    }
    return managerObj;
}

// ============================================================
// STEP 1: Get breached CHG sys_ids from task_sla
// ============================================================
var breachedChgIds = [];

var chgSlaGR = new GlideRecord('task_sla');
chgSlaGR.addQuery('active', true);
chgSlaGR.addQuery('has_breached', true);
chgSlaGR.addQuery('sla.name', 'WF CHG Resolution');
chgSlaGR.addQuery('task.sys_class_name', 'change_request');
chgSlaGR.query();

while (chgSlaGR.next()) {
    var taskId = chgSlaGR.getValue('task');
    if (taskId && breachedChgIds.indexOf(taskId) < 0) {
        breachedChgIds.push(taskId);
    }
}

gs.print('Breached CHG IDs found: ' + breachedChgIds.length);

// ============================================================
// STEP 2: Aggregate on change_request using breached sys_ids
// ============================================================
if (breachedChgIds.length > 0) {
    var chgRecords = new GlideAggregate('change_request');
    chgRecords.addQuery('sys_id', 'IN', breachedChgIds.join(','));
    chgRecords.addQuery('production_system', true);
    chgRecords.addQuery('state', 'NOT IN', '3,4');
    chgRecords.addAggregate('COUNT', 'requested_by');
    chgRecords.orderByAggregate('COUNT', 'requested_by');
    chgRecords.orderBy('requested_by');
    chgRecords.orderBy('assignment_group');
    chgRecords.query();

    gs.print('CHG aggregate rows: ' + chgRecords.getRowCount());

    while (chgRecords.next()) {
        var chgCount = chgRecords.getAggregate('COUNT', 'requested_by');
        var chgOverDueObj = {};
        chgOverDueObj.is_user_active = false;
        chgOverDueObj.has_valid_email = false;
        chgOverDueObj.is_manager_active = false;
        chgOverDueObj.group = chgRecords.getValue('assignment_group');
        chgOverDueObj.overdue_chg_count = chgCount;
        chgOverDueObj.overdue_ctask_count = '';
        chgOverDueObj.assigned_user_email = '';
        chgOverDueObj.group_email = '';
        chgOverDueObj.assigned_name = '';
        chgOverDueObj.manager_email = '';
        chgOverDueObj.manager_name = '';

        var requestedBy = chgRecords.requested_by.getRefRecord();
        if (requestedBy && requestedBy.isValidRecord() && requestedBy.getValue('active') == 'true') {
            chgOverDueObj.is_user_active = true;
            chgOverDueObj.assigned_user_email = requestedBy.getValue('email') || '';
            chgOverDueObj.assigned_name = requestedBy.getValue('u_preferred_name') ||
                                          (requestedBy.getValue('first_name') + ' ' + requestedBy.getValue('last_name')) || '';
            chgOverDueObj.has_valid_email = (chgOverDueObj.assigned_user_email != '');

            var chgManagerDetails = getManagerDetails(requestedBy);
            chgOverDueObj.is_manager_active = chgManagerDetails.is_manager_active;
            chgOverDueObj.manager_email = chgManagerDetails.manager_email;
            chgOverDueObj.manager_name = chgManagerDetails.manager_name;
        } else {
            var chgGroupDetails = getGroupDetails(chgOverDueObj.group);
            chgOverDueObj.group_email = chgGroupDetails.group_email;
            chgOverDueObj.group_name = chgGroupDetails.group_name;
            chgOverDueObj.manager_email = chgGroupDetails.manager_email;
            chgOverDueObj.manager_name = chgGroupDetails.manager_name;
        }

        if (chgOverDueObj.is_user_active && chgOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(chgOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingChangeEmails.push(chgOverDueObj.assigned_user_email);
        } else if ((!chgOverDueObj.is_user_active || !chgOverDueObj.has_valid_email) &&
                   chgOverDueObj.group_email &&
                   existingTaskEmails.indexOf(chgOverDueObj.group_email) < 0) {
            overDueRecords.push(chgOverDueObj);
            existingTaskEmails.push(chgOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 3: Get breached Change Task sys_ids from task_sla
// ============================================================
var breachedCtaskIds = [];

var ctaskSlaGR = new GlideRecord('task_sla');
ctaskSlaGR.addQuery('active', true);
ctaskSlaGR.addQuery('has_breached', true);
ctaskSlaGR.addQuery('sla.name', 'WF CTask Resolution');
ctaskSlaGR.addQuery('task.sys_class_name', 'change_task');
ctaskSlaGR.query();

while (ctaskSlaGR.next()) {
    var ctaskId = ctaskSlaGR.getValue('task');
    if (ctaskId && breachedCtaskIds.indexOf(ctaskId) < 0) {
        breachedCtaskIds.push(ctaskId);
    }
}

gs.print('Breached Change Task IDs found: ' + breachedCtaskIds.length);

// ============================================================
// STEP 4: Aggregate on change_task using breached sys_ids
// ============================================================
if (breachedCtaskIds.length > 0) {

    // DEBUG: Check why aggregate returns 0
    var ctaskCheck = new GlideRecord('change_task');
    ctaskCheck.addQuery('sys_id', 'IN', breachedCtaskIds.slice(0, 10).join(','));
    ctaskCheck.query();
    while (ctaskCheck.next()) {
        gs.print('CTask Check =>' +
                 ' | Number: ' + ctaskCheck.getValue('number') +
                 ' | State: ' + ctaskCheck.getValue('state') +
                 ' | CHG State: ' + ctaskCheck.getValue('change_request.state') +
                 ' | Production: ' + ctaskCheck.getValue('change_request.production_system') +
                 ' | Assigned To: ' + ctaskCheck.getDisplayValue('assigned_to') +
                 ' | Group: ' + ctaskCheck.getDisplayValue('assignment_group'));
    }

    var ctaskRecords = new GlideAggregate('change_task');
    ctaskRecords.addQuery('sys_id', 'IN', breachedCtaskIds.join(','));
    // Removed production_system and state filters to see all records first
    ctaskRecords.addAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderByAggregate('COUNT', 'assigned_to');
    ctaskRecords.orderBy('assigned_to');
    ctaskRecords.orderBy('assignment_group');
    ctaskRecords.query();

    gs.print('Change Task aggregate rows (no filter): ' + ctaskRecords.getRowCount());

    while (ctaskRecords.next()) {
        var ctaskCount = ctaskRecords.getAggregate('COUNT', 'assigned_to');
        var ctaskOverDueObj = {};
        ctaskOverDueObj.is_user_active = false;
        ctaskOverDueObj.has_valid_email = false;
        ctaskOverDueObj.is_manager_active = false;
        ctaskOverDueObj.group = ctaskRecords.getValue('assignment_group');
        ctaskOverDueObj.overdue_ctask_count = ctaskCount;
        ctaskOverDueObj.overdue_chg_count = '';
        ctaskOverDueObj.assigned_user_email = '';
        ctaskOverDueObj.group_email = '';
        ctaskOverDueObj.assigned_name = '';
        ctaskOverDueObj.manager_email = '';
        ctaskOverDueObj.manager_name = '';

        var assignedTo = ctaskRecords.assigned_to.getRefRecord();
        if (assignedTo && assignedTo.isValidRecord() && assignedTo.getValue('active') == 'true') {
            ctaskOverDueObj.is_user_active = true;
            ctaskOverDueObj.assigned_user_email = assignedTo.getValue('email') || '';
            ctaskOverDueObj.assigned_name = assignedTo.getValue('u_preferred_name') ||
                                            (assignedTo.getValue('first_name') + ' ' + assignedTo.getValue('last_name')) || '';
            ctaskOverDueObj.has_valid_email = (ctaskOverDueObj.assigned_user_email != '');

            var ctaskManagerDetails = getManagerDetails(assignedTo);
            ctaskOverDueObj.is_manager_active = ctaskManagerDetails.is_manager_active;
            ctaskOverDueObj.manager_email = ctaskManagerDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskManagerDetails.manager_name;
        } else {
            var ctaskGroupDetails = getGroupDetails(ctaskOverDueObj.group);
            ctaskOverDueObj.group_email = ctaskGroupDetails.group_email;
            ctaskOverDueObj.group_name = ctaskGroupDetails.group_name;
            ctaskOverDueObj.manager_email = ctaskGroupDetails.manager_email;
            ctaskOverDueObj.manager_name = ctaskGroupDetails.manager_name;
        }

        if (ctaskOverDueObj.is_user_active && ctaskOverDueObj.assigned_user_email &&
            existingChangeEmails.indexOf(ctaskOverDueObj.assigned_user_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingChangeEmails.push(ctaskOverDueObj.assigned_user_email);
        } else if ((!ctaskOverDueObj.is_user_active || !ctaskOverDueObj.has_valid_email) &&
                   ctaskOverDueObj.group_email &&
                   existingTaskEmails.indexOf(ctaskOverDueObj.group_email) < 0) {
            overDueRecords.push(ctaskOverDueObj);
            existingTaskEmails.push(ctaskOverDueObj.group_email);
        }
    }
}

// ============================================================
// STEP 5: DRY RUN output / or fire notifications
// ============================================================
gs.print('--- Total overDueRecords: ' + overDueRecords.length + ' ---');

if (DRY_RUN) {
    for (var i = 0; i < overDueRecords.length; i++) {
        var rec = overDueRecords[i];
        gs.print('Would notify =>' +
                 ' | User Active: ' + rec.is_user_active +
                 ' | Name: ' + rec.assigned_name +
                 ' | User Email: ' + rec.assigned_user_email +
                 ' | Group: ' + rec.group_name +
                 ' | Group Email: ' + rec.group_email +
                 ' | Manager Name: ' + rec.manager_name +
                 ' | Manager Email: ' + rec.manager_email +
                 ' | CHG Count: ' + rec.overdue_chg_count +
                 ' | Task Count: ' + rec.overdue_ctask_count);
    }
    gs.print('DRY RUN complete - no emails sent');
} else {
    // Your existing notification firing logic goes here
}