Send an email to users that they are going to be removed from the group,if they have not logged in

Kaustubh k
Tera Contributor

Hi All,

 

We have a schedule job which should give us the users who are part of the particular group and have not logged in for 2 months,so we need to send an email to users 1 week prior to the removal.

Here is my script, and event,but event is not working.Event is ion sys_user table.

 

 
        var date1 = new GlideDateTime();
        date1.addDaysUTC(-54);// cut off date to pull th users who are not logged in before 54 days
        var usersSysIds = '';
        var getUserId = new GlideRecord('sys_user');// Query on user table to get users
        getUserId.addEncodedQuery('sys_class_name=sys_user^active=true^last_login_time<=javascript&colon;gs.beginningOfLast30Days()^sys_created_on<=javascript&colon;gs.beginningOfLast30Days()');
        getUserId.query();
        while (getUserId.next()) {
            var date2 = new GlideDateTime();
            date2.setValue(getUserId.getValue('last_login_time'));
            var createdDate = new GlideDateTime();
            createdDate.setValue(getUserId.getValue('sys_created_on'));
        if ((GlideDateTime.subtract(date2, date1).getDayPart() > 54) && (GlideDateTime.subtract(createdDate, date1).getDayPart() > 54)) {
                var grpMembership = new GlideRecord('sys_user_grmember');
                grpMembership.addEncodedQuery('user=' + getUserId.getUniqueValue() + '^group=db1424f2dbf74c105e676d6bd39619f8'); //
                grpMembership.query();
                if (grpMembership.next()){
                    usersSysIds+=getUserId.getUniqueValue()+',';
                }
            }
        }
        gs.print(usersSysIds);
gs.eventQueue("notify_users", usersSysIds, usersSysIds.user, usersSysIds.email);
 
Thanks in Advance
    
 
1 ACCEPTED SOLUTION

Kieran Anson
Kilo Patron

Hi,

You can improve the performance of this script by using a subquery and avoid the need for nested GlideRecord queries which are resource intensive.

 

(function() {

	var LAST_LOGIN_DAYS_PRIOR = 54;
	var USER_GROUP_ID = 'sys_id_of_group_here';
	var users = [];

	var applicableUsersGR = new GlideRecord('sys_user');
	applicableUsersGR.addQuery('sys_class_name' , 'sys_user');
	applicableUsersGR.addActiveQuery();
	applicableUsersGR.addQuery('last_login_time' , 'RELATIVELT' , gs.daysAgo(LAST_LOGIN_DAYS_PRIOR)); //Not logged in prior to the date limit const

	applicableUsersGR.addEncodedQuery('RLQUERYsys_user_grmember.user,>=1,m2m^group=' + USER_GROUP_ID + '^ENDRLQUERY'); //RL query to onl pull users who are a member of the desired group;
	applicableUsersGR.query();
	while(applicableUsersGR.next()){
		users.push({
			'user' : applicableUsersGR.getUniqueValue(),
			'email' : applicableUsersGR.getValue('email')
		});
	}

	gs.eventQueue('notify_users' , applicableUsersGR, JSON.stringify(users));

})()

 

Note, the second parameter of gs.eventQueue needs to be a gliderecord value

View solution in original post

1 REPLY 1

Kieran Anson
Kilo Patron

Hi,

You can improve the performance of this script by using a subquery and avoid the need for nested GlideRecord queries which are resource intensive.

 

(function() {

	var LAST_LOGIN_DAYS_PRIOR = 54;
	var USER_GROUP_ID = 'sys_id_of_group_here';
	var users = [];

	var applicableUsersGR = new GlideRecord('sys_user');
	applicableUsersGR.addQuery('sys_class_name' , 'sys_user');
	applicableUsersGR.addActiveQuery();
	applicableUsersGR.addQuery('last_login_time' , 'RELATIVELT' , gs.daysAgo(LAST_LOGIN_DAYS_PRIOR)); //Not logged in prior to the date limit const

	applicableUsersGR.addEncodedQuery('RLQUERYsys_user_grmember.user,>=1,m2m^group=' + USER_GROUP_ID + '^ENDRLQUERY'); //RL query to onl pull users who are a member of the desired group;
	applicableUsersGR.query();
	while(applicableUsersGR.next()){
		users.push({
			'user' : applicableUsersGR.getUniqueValue(),
			'email' : applicableUsersGR.getValue('email')
		});
	}

	gs.eventQueue('notify_users' , applicableUsersGR, JSON.stringify(users));

})()

 

Note, the second parameter of gs.eventQueue needs to be a gliderecord value