How to send mail to group manager when users are inactive?

[shabbir5]

Hi All,

we got a requirement to send an email to all group managers with the inactive members in their groups.

--> I am trying with below code :

var arr=[];

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.name);

    var gr1=new GlideRecord('sys_user_grmember');

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

    gr1.addQuery('group.name',gr.group.name);

    gr1.query();

    gs.log('gr1 count'+gr1.getRowCount());

    while(gr1.next())

    {

    arr.push(gr1.user.name);

    }

    //gs.log('inactive users are'+arr);

    //gs.eventQueue('certify.group.membership',arr,gr.manager);

}

gs.log('inactive users are'+arr);

--> what my requirement is suppose group A has 3 inactive member , group B has 2 inactive members , group C has 10 inactive members 

--> so an email should trigger to all group managers of (A,B,C) with the inactive users list 

can you please help me on that 

Thank you,

Shabbir Shaik

[HIROSHI SATOH]

Not tested. Please use as a reference.

※Email notifications are intended to use the notification function with event triggers.

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

while (gr.next()) {
    var groupSysId = gr.getValue('group');
    var managerEmail = gr.group.manager.email; // Fetch the manager's email

    var inactiveUsers = [];
    var gr1 = new GlideRecord('sys_user_grmember');
    gr1.addQuery('group', groupSysId);
    gr1.addQuery('user.active', false);
    gr1.query();

    while (gr1.next()) {
        inactiveUsers.push(gr1.user.name.toString());
    }

    if (inactiveUsers.length > 0) {
        var emailBody = "Inactive users in your group: " + inactiveUsers.join(', ');
        gs.eventQueue('send_email_event', managerEmail, emailBody); // Replace 'send_email_event' with your event
        gs.log('Email sent to: ' + managerEmail + ' with inactive users: ' + inactiveUsers.join(', '));
    }
}

Explanation:

• GlideAggregate: Groups users by their group.
• GlideRecord: Fetches inactive users in each group.
• inactiveUsers.push(): Collects the names of inactive users.
• gs.eventQueue(): Sends an email to the group manager. You need to configure an event like send_email_event that handles sending emails in ServiceNow.