Update group members count in group(sys_user_group) table

[Devi D]

Hi Everyone ,

I have got a  requirement where i need to update group members count in one of the field in group table ,

for this i have created one field -u_member_count  and updated the below script in fix script .

and i have around 30k groups in the DEV instance , when i run the below script only one record is getting updated and that to it is taking more time . can you please correct the script or advise me what can be done .

var groupGR = new GlideRecord('sys_user_group');

groupGR.query();

while (groupGR.next()) {

   var memberGR = new GlideRecord('sys_user_grmember');

   memberGR.addQuery('group', groupGR.sys_id);

   memberGR.query();

   var count = 0;

   while (memberGR.next()) {

       count++;

   }

   groupGR.u_member_count = count;

   groupGR.update();

[Anurag Tripathi · Accepted solution]

Hi,

Try this

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('u_member_count ', ''); //to ensure it will not work for groups that are already calculated.
groupGR.setLimit(10); //do 10 groups at a time, you can increase this based on performance
groupGR.setWorflow(false);
groupGR.query();
while (groupGR.next()) {
   var memberGR = new GlideRecord('sys_user_grmember');
   memberGR.addQuery('group', groupGR.sys_id);
   memberGR.query();
      groupGR.u_member_count = memberGR.getRowCount(); //no need to loop through all records
   groupGR.update();
}

[Anurag Tripathi · Accepted solution]

Hi,

Try this

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('u_member_count ', ''); //to ensure it will not work for groups that are already calculated.
groupGR.setLimit(10); //do 10 groups at a time, you can increase this based on performance
groupGR.setWorflow(false);
groupGR.query();
while (groupGR.next()) {
   var memberGR = new GlideRecord('sys_user_grmember');
   memberGR.addQuery('group', groupGR.sys_id);
   memberGR.query();
      groupGR.u_member_count = memberGR.getRowCount(); //no need to loop through all records
   groupGR.update();
}

[Ankur Bawiskar · Accepted solution]

@Devi D 

this should work fine and you should use GlideAggregate for performance improvement

Test for 10/20 groups using setLimit(20); and if it works then run for all

updateCount();

function updateCount() {
    try {
        var groupGR = new GlideRecord('sys_user_group');
        groupGR.addNotNullQuery('u_member_count');
        groupGR.setLimit(20); // test for 20 groups and then comment this line
        groupGR.query();
        while (groupGR.next()) {
            var memberCount = getGroupMemberCount(groupGR.sys_id);
            groupGR.u_member_count = memberCount;
            groupGR.update();
        }
    } catch (ex) {
        gs.info(ex);
    }
}

function getGroupMemberCount(groupId) {
    var agg = new GlideAggregate('sys_user_grmember');
    agg.addQuery('group', groupId);
    agg.addAggregate('COUNT');
    agg.query();
    if (agg.next()) {
        return agg.getAggregate('COUNT');
    }
    return 0;
}

If my response helped please mark it correct and close the thread so that it benefits future readers.