Notify users after removing ITIL groups

[bhargavi9]

Hi All,

We need to remove ITIL groups for users who have not logged in for the last 45 days and need to need to send notifications after ITIL groups removal. I attempted to use userGroup.deleteMultiple() and deleteRecord() methods, but they are not functioning correctly for multiple users. Any assistance would be greatly appreciated.

[Sandeep Rajput]

@bhargavi9 Could you please share the script which you are using to delete the records?

[bhargavi9]

Hi @Sandeep Rajput 
Please find the below script

var gr = new GlideRecord('sys_user');

gr.addEncodedQuery('active=true^last_loginRELATIVELT@dayofweek@ago@45^sys_created_by!=admin^ORsys_created_by!=system^roles=itil^web_service_access_only=false^vip=false');

gr.query();

while (gr.next()) {

    var userid = gr.getUniqueValue();

    var groupIds = [];

    var groupGr = new GlideRecord('sys_user_grmember');

    groupGr.addQuery('user', userid);

    groupGr.query();

    while (groupGr.next()) {

        groupIds.push(groupGr.getValue('group'));

    }

    // Remove user from groups with the ITIL role

    var itilGroupGr = new GlideRecord('sys_user_group');

    itilGroupGr.addQuery('roles', 'itil');

    itilGroupGr.addQuery('sys_id', 'IN', groupIds.join(','));

    itilGroupGr.query();

    while (itilGroupGr.next()) {

        var groupId = itilGroupGr.getUniqueValue();

        if (groupId != 'null') {

            var userGroup = new GlideRecord('sys_user_grmember');

            userGroup.addQuery('user', userid);

            userGroup.addQuery('group', groupId);

            userGroup.query();

            if (userGroup.next() != 'null') {

                gs.eventQueue('sys_itil_role_removed', gr, gr.email);

                userGroup.deleteMultiple();

            }

        }

    }

}

[Sandeep Rajput]

@bhargavi9 Please see if the following script works for you.

var gr = new GlideRecord('sys_user');
gr.addEncodedQuery('active=true^last_loginRELATIVELT@dayofweek@ago@45^sys_created_by!=admin^ORsys_created_by!=system^roles=itil^web_service_access_only=false^vip=false');
gr.query();
while (gr.next()) {
    var userid = gr.getUniqueValue();
   
    var groupIds = [];
    var groupGr = new GlideRecord('sys_user_grmember');
    groupGr.addQuery('user', userid);
    groupGr.query();
    while (groupGr.next()) {
        groupIds.push(groupGr.getValue('group'));
       
    }
    // Remove user from groups with the ITIL role
    var itilGroupGr = new GlideRecord('sys_group_has_role');
    itilGroupGr.addQuery('role.name', 'itil');
    itilGroupGr.addQuery('group.sys_id', 'IN', groupIds.join(','));
    itilGroupGr.query();
   
    while (itilGroupGr.next()) {
        var groupId = itilGroupGr.getValue('group');
        if (groupId != 'null') {
            var userGroup = new GlideRecord('sys_user_grmember');
            userGroup.addQuery('user', userid);
            userGroup.addQuery('group', groupId);
            userGroup.query();
            userGroup.deleteMultiple();
            gs.eventQueue('sys_itil_role_removed', gr, gr.email);

        }
    }
}

[PrashantLearnIT]

Hi @bhargavi9 

Step 1: Identify Users Who Haven't Logged In

You can query the sys_user table to find users who haven't logged in within the last 45 days. Here's an example script using GlideRecord to find those users:

var lastLoginDate = new GlideDateTime();
lastLoginDate.addDaysUTC(-45); // 45 days ago

var userGr = new GlideRecord('sys_user');
userGr.addQuery('last_login_time', '<', lastLoginDate);
userGr.query();

var usersToRemove = []; // Array to hold user sys_ids for logging purposes

while (userGr.next()) {
// Add each user sys_id to the array
usersToRemove.push(userGr.sys_id.toString());
}

Step 2: Remove ITIL Group Memberships

Next, you can loop through each user to remove their ITIL group membership by querying the sys_user_grmember table.

for (var i = 0; i < usersToRemove.length; i++) {
var groupGr = new GlideRecord('sys_user_grmember');
groupGr.addQuery('user', usersToRemove[i]); // Find group memberships for the user
groupGr.addQuery('group.name', 'ITIL'); // Only target ITIL group
groupGr.query();

while (groupGr.next()) {
groupGr.deleteRecord(); // Remove the group membership
}
}

Step 3: Send Notifications

After the group removal, you can send a notification to the users. The gs.eventQueue() method can be used to trigger an event, and you can create a notification tied to that event.

for (var j = 0; j < usersToRemove.length; j++) {
gs.eventQueue('itil.group.removal.notification', userGr, usersToRemove[j], null);
}

• Create an Event: Go to System Policy > Events > Event Registry and register the event (itil.group.removal.notification).
• Create a Notification: Go to System Notification > Email > Notifications, and configure a notification for this event.