Email body doesn't contains user details

[Sarah Bouil]

Hi,

My requirement is below.

If I am removing itil role from users(if user is not logged in 60 days) so the email should be sent to ex: test1@example.com and email body should contains with user(all the removed itil role users) details(like Name, User ID, Email)

Code:

var ITIL_ROLE_ID = '282bf1fac6112285017366cb5f867469'; 

// 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('cristina.sharper@example.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:

The body doesn't contains itil removal users list.

Kindly help me what is the issue?

[Runjay Patel]

Hi @Sarah Bouil ,

You have already removed the itil role in starting but for making subject body you are putting the conditions that user should have itil role. This will give you empty result.

you are already running a loop to remove the role from user , in same loop you can capture removed user details in one string variable.

and while sending the email just use that card I able in you email body.

Mark helpful and accept the solution if it helped.

[Sarah Bouil]

Hi Runjay, Could you please help me in which line of code need to be updated or added, please help me.

[Runjay Patel]

Hi @Sarah Bouil ,

You can do below changes.

1.

 removedUsers.push({
            name: userGr.name,
            userId: userGr.user_name',
            email: userGr.'email',
 last_login: userGr.'last_login'
        });

2.

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>';
    
    for(var i=0; i<removedUsers.length; i++){
	gs.print(removedUsers[i].name);
        emailBody += "Name: " + removedUsers[i].name+ ", User ID: " + removedUsers[i].userId+ ", Email: " + removedUsers[i].email+ ", Last login: " + removedUsers[i].last_login+ "\n" + '<br>';
    }
 

    var email = new GlideEmailOutbound();
    email.setSubject('ITIL access removed users list');
    email.addRecipient('cristina.sharper@example.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');
}

[Aniket Chavan]

Hello @Sarah Bouil ,

Please give a try to the script below and see how it works for you.

var ITIL_ROLE_ID = '282bf1fac6112285017366cb5f867469'; 

// Function to find 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 have not logged in within 60 days, excluding admin and svc accounts
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 the 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 inherited
    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) {
    // Construct the email body using the removedUsers array
    var emailBody = "Hi Team,\n\n" + '<br>' + '<br>' + "The following users had their 'itil' role removed because they have not logged in for 60 days:\n\n" + '<br>' + '<br>';
    
    removedUsers.forEach(function(user) {
        emailBody += "Name: " + user.name + ", User ID: " + user.userId + ", Email: " + user.email + "<br>";
    });

    // Append footer details
    emailBody += '<br>' + 'Regards,' + '<br>' + 'Support Team.';

    // Send the email
    var email = new GlideEmailOutbound();
    email.setSubject('ITIL access removed users list');
    email.addRecipient('test1@example.com'); // Replace with the desired recipient email
    email.setBody(emailBody);
    email.save();
    email.send();

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