Email body issue it contains duplicate details

[Sarah Bouil]

Hi, I am trying to delete the itil role from users who are no logged in system last 60 days. I am able to delete the itil role but I am having issue while sending email with itil users removal list in email body. The email boday contains duplicate users list.

Code:

var ITIL_ROLE_ID = '282bf1fac6112285017366cb5f867469';

function getContainingRoles(roleSysId, rolesToRemove) {

    var grRoleContains = new GlideRecord('sys_user_role_contains');

    grRoleContains.addQuery('contains', roleSysId);

    grRoleContains.query();

    while (grRoleContains.next()) {

        var containingRoleId = grRoleContains.role.toString();

        if (!rolesToRemove.includes(containingRoleId)) {

            rolesToRemove.push(containingRoleId);

            getContainingRoles(containingRoleId, rolesToRemove);

        }

    }

}

var rolesToRemove = [ITIL_ROLE_ID];

getContainingRoles(ITIL_ROLE_ID, rolesToRemove);

var removedUsers = [];

var userGr = new GlideRecord('sys_user');

userGr.addEncodedQuery('active=true^last_login<=javascript&colon;gs.beginningOfLast60Days()^roles=itil^web_service_access_only=false^ORinternal_integration_user=false^user_nameNOT LIKEadmin^user_nameNOT LIKEsvc^nameNOT LIKEsvc');

userGr.query();

gs.log('User count: ' + userGr.getRowCount());

while (userGr.next()) {

    gs.print('Processing User: ' + userGr.getDisplayValue('user_name'));

    var groupList = [];

    var userRoleGr = new GlideRecord('sys_user_has_role');

    userRoleGr.addQuery('user', userGr.sys_id);

    userRoleGr.addQuery('role', 'IN', rolesToRemove.join(','));

    userRoleGr.query();

    while (userRoleGr.next()) {

        gs.log('Removing explicit ITIL role for user: ' + userGr.user_name);

        //userRoleGr.deleteRecord();

        removedUsers.push({

            name: userGr.getDisplayValue('name'),

            userId: userGr.getDisplayValue('user_name'),

            email: userGr.getDisplayValue('email')

        });

    }

    gs.print('Test01-removedUsers: ' + removedUsers.length);

    var groupMemberGr = new GlideRecord("sys_user_grmember");

    groupMemberGr.addQuery("user", userGr.sys_id);

    groupMemberGr.query();

    while (groupMemberGr.next()) {

        var groupRoleGr = new GlideRecord("sys_group_has_role");

        groupRoleGr.addQuery("group", groupMemberGr.group);

        groupRoleGr.addQuery("role", 'IN', rolesToRemove.join(','));

        groupRoleGr.query();

        if (groupRoleGr.next()) {

            gs.log('User ' + groupMemberGr.getDisplayValue('user') + ' removed from group ' + groupMemberGr.group.name + ' due to License Optimization');

            groupList.push(groupMemberGr.group.name.toString());

            //groupMemberGr.deleteRecord();

        }

    }

    gs.print('Test01-groupList: ' + groupList.length);

}

if (removedUsers.length > 0) {

    var emailBody = "The ITIL role has been removed from the following users due to inactivity:\n\n";

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

        var user = removedUsers[i];

        emailBody += "Name: " + user.name + ", User ID: " + user.userId + ", Email: " + user.email + "\n";

    }

    var email = new GlideEmailOutbound();

    email.setSubject('ITIL access removed users list');

    email.addRecipient('survey.user@email.com');

    email.addRecipient('test1@example.com');

    email.setBody(emailBody);

    email.save();

    email.send();

    gs.log('Notification email sent to user');

}

Email body: email body contains the user details multiple times, highlighted below.

It should be like below.

What is the issue in my code? kindly help on it.

[Moin Kazi]

Hi @Sarah Bouil ,

for your 4th use case I have shared the updated code - 

Kindly replace these five lines in your code.

//Replace These 5 lines from you code -

var emailBody = "The ITIL role has been removed from the following users due to inactivity:\n\n";
    for (var i = 0; i < removedUsers.length; i++) {
        var user = removedUsers[i];
        emailBody += "Name: " + user.name + ", User ID: " + user.userId + ", Email: " + user.email + "\n";  
}
   

//Replace the above with the below code

var daysAgo = gs.daysAgoStart(60); // Get date for 60 days ago
    var emailBody = "The following users had their 'itil' role removed because they have not logged in for 60 days:\n\n";
    // Query sys_user for users who haven't logged in for 60 days and have the 'itil' role
    var userGr = new GlideRecord('sys_user');
    userGr.addEncodedQuery('last_login_time<=' + daysAgo + '^roles=itil'); // Users who haven't logged in for 60 days and have 'itil' role
    userGr.addActiveQuery(); // Only active users
    userGr.query();
    while (userGr.next()) {
    emailBody += "Name: " + userGr.name.toString() + ", User ID: " + userGr.userId.toString() + ", Email: " + userGr.email.toString() + "\n";
}

This code will validate any duplicate user detail is not added in your email body.

Hope this help you.

Regards

Moin

[Sarah Bouil]

Hi Moin,

I have updated the code as you mentioned but the email body doesn't contains any data, it is showing as empty body with user details.

Code:

var ITIL_ROLE_ID = '282bf1fac6112285017366cb5f867469'; // sys_id of the itil role

// To find out all roles containing the given role

function getContainingRoles(roleSysId, rolesToRemove) {

    var grRoleContains = new GlideRecord('sys_user_role_contains');

    grRoleContains.addQuery('contains', roleSysId);

    grRoleContains.query();

    while (grRoleContains.next()) {

        var containingRoleId = grRoleContains.role.toString();

        if (!rolesToRemove.includes(containingRoleId)) {

            rolesToRemove.push(containingRoleId);

            getContainingRoles(containingRoleId, rolesToRemove);

        }

    }

}

// Array to keep track of roles to be removed

var rolesToRemove = [ITIL_ROLE_ID];

getContainingRoles(ITIL_ROLE_ID, rolesToRemove);

var removedUsers = [];

// Query to get itil role users who are not logged in 60 days, excluding admin and svc account users

var userGr = new GlideRecord('sys_user');

userGr.addEncodedQuery('active=true^last_login<=javascript&colon;gs.beginningOfLast60Days()^ORlast_login_timeISEMPTY^roles=itil^web_service_access_only=false^ORinternal_integration_user=false^user_nameNOT LIKEadmin^user_nameNOT LIKEsvc^nameNOT LIKEsvc^nameNOT LIKEDQM');

userGr.query();

gs.log('Total user count: ' + userGr.getRowCount());

while (userGr.next()) {

    gs.log('Processing User: ' + userGr.getDisplayValue('user_name'));

    var groupList = []; // Store groups details from which the user will be removed

    // Removing itil role if itil is explicitly added to user

    var userRoleGr = new GlideRecord('sys_user_has_role');

    userRoleGr.addQuery('user', userGr.sys_id);

    userRoleGr.addQuery('role', 'IN', rolesToRemove.join(','));

    userRoleGr.query();

    while (userRoleGr.next()) {

        gs.log('Removing explicit ITIL role for user: ' + userGr.user_name);

        userRoleGr.deleteRecord();

        removedUsers.push({

            name: userGr.getDisplayValue('name'),

            userId: userGr.getDisplayValue('user_name'),

            email: userGr.getDisplayValue('email')

        });

    }

    gs.log('Test01: ' + removedUsers.length);

    // Removing a user from itil groups if the itil role is inheriting

    var groupMemberGr = new GlideRecord("sys_user_grmember");

    groupMemberGr.addQuery("user", userGr.sys_id);

    groupMemberGr.query();

    while (groupMemberGr.next()) {

        var groupRoleGr = new GlideRecord("sys_group_has_role");

        groupRoleGr.addQuery("group", groupMemberGr.group);

        groupRoleGr.addQuery("role", 'IN', rolesToRemove.join(','));

        groupRoleGr.query();

        if (groupRoleGr.next()) {

            gs.log('User ' + groupMemberGr.getDisplayValue('user') + ' removed from group ' + groupMemberGr.group.name + ' due to License Optimization');

            groupList.push(groupMemberGr.group.name.toString());

            groupMemberGr.deleteRecord();

        }

    }

    gs.log('Test02: ' + groupList.length);

}

if (removedUsers.length > 0) {

    var emailBody = "Hi Team,\n\n" + '<br>' + '<br>' + "The following users had their 'itil' role removed because they have not logged in system for 60 days:\n\n" + '<br>' + '<br>';

    var userGr1 = new GlideRecord('sys_user');

    userGr1.addEncodedQuery('active=true^last_login<=javascript&colon;gs.beginningOfLast60Days()^ORlast_login_timeISEMPTY^roles=itil^web_service_access_only=false^ORinternal_integration_user=false^user_nameNOT LIKEadmin^user_nameNOT LIKEsvc^nameNOT LIKEsvc^nameNOT LIKEDQM');

    userGr1.query();

    while (userGr1.next()) {

        emailBody += "Name: " + userGr1.name.toString() + ", User ID: " + userGr1.user_name.toString() + ", Email: " + userGr1.email.toString() + ", Last login: " + userGr1.last_login.toString() + "\n" + '<br>';

    }

    var email = new GlideEmailOutbound();

    email.setSubject('ITIL access removed users list');

    email.addRecipient('test@gmail.com');

    email.setBody(emailBody);

    email.setBody('<br>' + 'Regards' + ',' + '<br>' + 'Support Team.');

    email.save();

    email.send();

    gs.log('Email sent with list of itil role removed users');

}

Notification Email body:

Could you please help me to fix the issue?