How to get inactive members in each group and an email should trigger to individual manager

[shabbir5]

Hi All,

I am working on a requirement to get inactive members in each group and to send an email to managers with the inactive user details in their group

--> i am trying with below code :

var gr=new GlideAggregate('sys_user_grmember');

gr.addQuery('user.active',false);

gr.addQuery('manager.active',true);

gr.groupBy('group');

gr.query();

while(gr.next())

{

    gs.log(gr.group.name+':::'+gr.group.manager.user_name);

}

I am getting the groups and managers list with this , but not getting how to send email to these managers with inactive user details ??

this should trigger every 6 months , so i am trying this code in a schedule job

please help how to send inactive user details in the email , i have event and notification already .

@Ankur Bawiskar  please provide your inputs

Thank you,

Shabbir Shaik

[Mark Manders]

I'm not Ankur, so you can ignore the answer if you are just wanting his input (tagging really is the 2nd annoying thing)...

Create a flow for it. You can trigger flows on a schedule as well. From there you can easily do a lookup to the groups with inactive user and use the send email action to trigger it to be received by the group manager. If you use a 'for each' to check on all inactive users, you can put them in the email. You could even make it one email per manager if you don't want multiple emails for one person managing more groups.

[Akshay Gupta2]

Hi @shabbir5 

To achieve this, you need to perform the following steps:

1. Query Inactive Members: Retrieve inactive users in each group and their managers.
2. Aggregate Data: Collect inactive user details for each group and prepare the data for email.
3. Send Email: Use the gs.eventQueue to trigger an event that will send an email to the managers with the collected data.
4. Schedule the Job: Create a scheduled job to run this script every 6 months.

Here is the complete solution to your requirement: 

Steps -

Create Notification:

• Go to System Notification > Email > Notifications.
• Create a new notification with:
  • Name: Inactive User Notification
  • Table: Global
  • When to send: Event is fired
  • Event Name: custom.inactive.user.notification

Set Email Subject and Body:

• Subject: Inactive Users in Your Group
• Email Body: Use the emailBody passed in the event.Who will receive: Users/Groups as needed (or use a script to send to the dynamic recipientCreate Scheduled Job

Create a Scheduled Job:

• Navigate to System Definition > Scheduled Jobs.
• Create a new Scheduled Script Execution with:
  • Name: Send Inactive User Notifications
  • Run: Periodically
  • Repeat Interval: Every 6 months

Script for Scheduled Job:

(function() {
    var gr = new GlideAggregate('sys_user_grmember');
    gr.addQuery('user.active', false);
    gr.groupBy('group');
    gr.query();

    var groupDetails = {};

    while (gr.next()) {
        var groupID = gr.getValue('group');
        var groupName = gr.group.name.toString();
        var groupManager = gr.group.manager.user_name.toString();

        if (!groupDetails[groupID]) {
            groupDetails[groupID] = {
                name: groupName,
                manager: groupManager,
                users: []
            };
        }

        var inactiveUserGR = new GlideRecord('sys_user_grmember');
        inactiveUserGR.addQuery('group', groupID);
        inactiveUserGR.addQuery('user.active', false);
        inactiveUserGR.query();

        while (inactiveUserGR.next()) {
            groupDetails[groupID].users.push({
                name: inactiveUserGR.user.name.toString(),
                email: inactiveUserGR.user.email.toString()
            });
        }
    }

    for (var groupID in groupDetails) {
        var groupInfo = groupDetails[groupID];
        var emailBody = 'Dear ' + groupInfo.manager + ',\n\n';
        emailBody += 'The following users in your group "' + groupInfo.name + '" are inactive:\n\n';
        
        groupInfo.users.forEach(function(user) {
            emailBody += '- ' + user.name + ' (' + user.email + ')\n';
        });

        emailBody += '\nPlease review and take necessary action.\n\nBest Regards,\nYour Company';

        // Trigger the email event
        gs.eventQueue('custom.inactive.user.notification', null, groupInfo.manager, emailBody);
    }
})();

This script will collect inactive users for each group and send an email to the respective group managers every 6 months.

Please mark as helpful if this helps

Regards

Akshay

[vishnumaaya]

1. This is helpful. But when we are writing everything in the scheduled job script why do we require email notification?

2. Only to send out to managers or particular group we are create a notification.

3. how to link notification n scheduled job script here?

If you can respond on this please?

[VenkataNaru]

Hi ,

Please use this  Script  :

var grGroup = new GlideRecord("sys_user_grmember");

grGroup.addQuery("user.active", false);

grGroup.addQuery("manager.active", true);

grGroup.query();

while(grGroup.next()){

    gs.info("UserName "+" :"+ grGroup.user.name +":"+" Group Name "+ " :"+grGroup.group.name +"Group Manager Name"+" :"+ grGroup.group.manager.name);

    gs.info("Manager sysID"+":"+grGroup.group.manager);

var grUser = new GlideRecord("sys_user");

grUser.addEncodedQuery("sys_id="+grGroup.group.manager);

gs.info(grUser.getEncodedQuery());

grUser.query();

if (grUser.next()){

    gs.info(grUser.email);

    gs.eventQueue("eventname",current,grUser.email,"");

    //or

        gs.eventQueue("eventname",current,"","");// set the manager name in whom to send in the notification

}

}