Filter Users by Group on sys_user in ServiceNow

Platform Analytics is great because it allows regular end users to explore data in ServiceNow. However, it also evokes questions like “How do I filter users by assignment group on the sys_user table?”. The user who asked the question was using sys_user as a filter and only wanted to see users in a single assignment group. Hmm, I told them I’d have to think about that one.

The solution I came up with is the create a Groups list field on the sys_user table and keep this field updated with a business rule on the sys_user_grmember table. This way, users can easily search for members of a any group right from the sys_user table. Here are the steps I took:

1. Create a u_groups field on the sys_user table. Type = list, reference table is sys_user_group.
2. Add the following business rule to the sys_user_grmember table

(function executeRule(current, previous /*null when async*/ ) {

    // updates the sys_user.u_groups field when a record from this table is inserted or deleted

    if (current.operation() == 'insert') {
        addGroupToUser(current);
    } else if (current.operation() == 'delete') {
        removeGroupFromUser(current);
    }

    function addGroupToUser(current) {
        var userGR = new GlideRecord('sys_user');
        userGR.get(current.getValue('user'));
        if (!userGR.isValidRecord()) {
            return;
        }

        //get the current value of the u_groups field
        var groupsArr = [];
        if (userGR.getValue('u_groups')) {
            groupsArr = userGR.getValue('u_groups').split(',');
        }

        //check if the group is already added.  If not, add the group.
        if (groupsArr.indexOf(current.getValue('group')) == -1) {
            groupsArr.push(current.getValue('group'));
            userGR.setValue('u_groups', groupsArr.join(','));
        }

        userGR.autoSysFields(false);
        userGR.setWorkflow(false);
        userGR.update();



    }

    function removeGroupFromUser(current) {
        var userGR = new GlideRecord('sys_user');
        userGR.get(current.getValue('user'));
        if (!userGR.isValidRecord()) {
            return;
        }

        //get the current value of the u_groups field
        var groupsArr = [];
        if (userGR.getValue('u_groups')) {
            groupsArr = userGR.getValue('u_groups').split(',');
        }

        //there shouldn't be repeat values in the array but in case there are, remove them all
        for (var i in groupsArr) {
            if (groupsArr[i] == current.getValue('group')) {
                gs.info(i);
                groupsArr.splice(i);
            }
        }

        userGR.setValue('u_groups', groupsArr.join(','));
        userGR.autoSysFields(false);
        userGR.setWorkflow(false);
        userGR.update();


    }

})(current, previous);

The end result is the ability to filter users by groups right from the sys_user table: