Need to send email to team with itil removal users list

[Sarah Bouil]

Hi, I am able to remove itil role from user if they are not logged in 60 days, but I need to send an email to team members ex: test1@example.com, test2@example.com (in excel like: User ID, Name, Email, Last Login)before itil removal or after itil removal, I mean from what and all users we have removed the itil role.

I could not be able to send an email, there is an issue. Kindly help me on it.

var ITIL_ROLE_ID = '282bf1fac6112285017366cb5f867469';
var excelFileName = "ITIL_users_not_logged_in_60_days.xlsx";

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 userGr = new GlideRecord('sys_user');
//userGr.get('83de4b0e9731e118c9b930b6f053af5e');
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.addEncodedQuery('active=true^last_loginRELATIVELT@dayofweek@ago@61^roles=itil^web_service_access_only=false^ORinternal_integration_user=false^user_nameNOT LIKEadmin^user_nameNOT LIKEsvc^nameNOT LIKEsvc');
userGr.setLimit(2);
userGr.query();
gs.log('User count: ' + userGr.getRowCount());

var excel = new sn_impex.GlideExcelParser();
excel.addSheet("ITIL Users");

excel.addRow(["Name", "Email", "Last Login Date"]);

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

var name = userGr.getValue('name');
var email = userGr.getValue('email');
var lastLogin = userGr.getValue('last_login_time');
excel.addRow([name, email, lastLogin]);

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();
}

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('groupList: ' + groupList.length);

// Save the Excel file to an attachment record
var attachment = new GlideSysAttachment();
var fileContent = excel.getExcelData();
var attachmentSysId = attachment.write('sys_user', gs.getUserID(), excelFileName, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', fileContent);

// Send email notification if the user is removed from ITIL group
if (groupList.length > 0) {
var email = new GlideEmailOutbound();
email.setSubject('ITIL Users Not Logged in for 60 Days');
//email.addRecipient(userGr.email);
email.setTo('MS-ServiceNow-Admin@equinix.com');
email.setBody('Attached is the list of ITIL users who havent logged in for 60 days.');
email.addAttachment(attachmentSysId);
email.save();
email.send();
//gs.print('emailBody: ' + emailBody);
//gs.email(userGr.email, 'no-reply@yourdomain.com', 'ITIL Access Revocation Notification', emailBody);
gs.log('Notification email sent to user: ' + userGr.user_name);
}
}

[AndersBGS]

Hi @Sarah Bouil ,

You can just create a scheduled script containing:

var user =new GlideRecord("sys_user");
user.addEncodedQuery("nameINjavascript: new getInactiveUserCountUtils().inactiveUsers()^user_nameISNOTEMPTY");
user.query();
while(user.next())
	{
gs.eventQueue("inactive.servicenow.users",user, user.sys_id);
	}

This is calling a script include:

    inactiveUsers: function() {

		var array = [];
		
        var gr = new GlideRecord("sys_user_has_license");
        gr.addEncodedQuery('user.last_loginRELATIVELT@dayofweek@ago@90^user.last_loginRELATIVEGT@dayofweek@ago@97^user.active=true^user.web_service_access_only=false');
        gr.query();
        while (gr.next()) {

            array += gr.user.name + ",";
		}
		return array;
    },

 So basic, your notification is triggered by an event which is fired by the scheduled job. 

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

best regards

Anders 

[Sarah Bouil]

Hi AndersBGS,

can we send an email(by using GlideEmailOutbound()) with attachment through the above Scheduled Job?

My requirements are: if user not logged into the system in 60 days.

1. itil role should remove from user if itil role explictly added - working fine

2. itil and parent role should remove from user if itil role is inherited from any other role(if itil is inherited from knowledge_manager, so both itil and knowledge_manager should be removed from user) - working fine

3. Group should be removed from user if itil role is inherited from group - working fine

4. if I am removing itil role from 5 users so the email should be sent to test1@example.com or test2@example.com with attachment or the user details(like Name, User ID, Email) should contains in email body - it is not working

First 3 requirements are working,

I have an issue with 4th requirement, kindly suggest.