Dynamic Filter Script / Script Include for Child Groups that are part of Parent Group for Report

[WazzaJC]

Dynamic Filter Script / Script Include for Child Groups that are part of Parent Group for Report

Hi Team - I would really appreciate any help/guidance on how to achieve this.

I need to set up a Dynamic Filter so that I can filter an Incident Report to only show Open Incidents for the Assignment

Groups that are children of a parent Group that I am the Manager of (this parent Group is not itself an Assignment Group, but its sub-groups are and they are linked via a child-parent mapping).

These are child Assignment Groups that are mapped to a parent Group - I am set as the Manager of that parent Group.

What would be the Script Include I need to write to dynamically filter on those Open Incidents, associated to all Assignment Groups that are part of the parent Group that I am the Manager of.

I need to build this as a Dynamic Filter that I can reference within a Report that points to the Incident table.

Many thanks for any advice/guidance.

[Bert_c1 · Accepted solution]

Hi,

I see that script needs to be extended, to get the child groups of the groups returned. To meet your needs

function getMyManagedGroups() {
    var selected = [];
    var grpManager = gs.getUserID();
    // Instead of dynamic for current user, I will use a specific manager that I know has groups
    grpManager='c092a629db66ae00580ed211ce9619a0';


    //Get the groups where grpManager is the 'manager'
    var grps = new GlideRecord('sys_user_group');
    grps.addQuery('manager', grpManager);
    grps.query();

    while (grps.next()) {
        //Add the group sys_id values to the returned array
		selected.push(grps.getValue('sys_id'));
    }

	// now get the list of child groups
	var childGroups = [];
	for (i=0; i< selected.length; i++) {
		var childGrps = new GlideRecord('sys_user_group');
		childGrps.addQuery('parent', selected[i]);
		childGrps.query();
		while (childGrps.next()) {
			childGroups.push(childGrps.getValue('sys_id'));
		}
	}

    return childGroups;
}

var grpList = getMyManagedGroups();
gs.info("Managed child groups: " + grpList);

[Bert_c1 · Accepted solution]

Hi WazzJC,

I have modified the script logic to be like what was in the post I referred to, tested in scripts background.

function ManagerChildGroupsUsers() {
		var selected = [];
		var grpManager = gs.getUserID();
//		gs.info('getMyManagedChildGroups: Manager = ' +grpManager);
		// Instead of dynamic for current user, I will use a specific manager that I know has groups
		grpManager='c092a629db66ae00580ed211ce9619a0';		// Rosie Matthews - Demo data

		//Get the groups where grpManager is the 'manager'
		var grps = new GlideRecord('sys_user_group');
		grps.addQuery('manager', grpManager);
		grps.query();
		while (grps.next()) {
			//Add the group sys_id values to the returned array
			selected.push(grps.getValue('sys_id'));
		}

		// next get the list of child groups
		var childGroups = [];
		for (i=0; i< selected.length; i++) {
			var childGrps = new GlideRecord('sys_user_group');
			childGrps.addQuery('parent', selected[i]);
			childGrps.query();
//			gs.info("Found " + childGrps.getRowCount() + " record where parent = " + selected[i]);
			while (childGrps.next()) {
				childGroups.push(childGrps.getValue('sys_id'));
			}
		}

		// now get the users in the child groups
		var users = [];
		for (i=0; i< childGroups.length; i++) {
			var userGrps = new GlideRecord('sys_user_grmember');
			userGrps.addQuery('group', childGroups[i]);
			userGrps.query();
//			gs.info("Found " + userGrps.getRowCount() + " record where group = " + childGroups[i]);
			while (userGrps.next()) {
//				gs.info("Adding user = " + userGrps.user);
				users.push(userGrps.getValue('user'));
			}
		}
		
		return users;
}

And Dynamic Filter Option:

Debug shows the script include is create the 'users' array correctly, but testing I get "(empty)" when I select that for "opened by", "is (dymanic)" and the script include.  I don't have time to see what is going on today, hopefully it'll work for you or someone else here will respond on that aspect.

[Bert_c1 · Accepted solution]

Hi WazzJC,

I have modified the script logic to be like what was in the post I referred to, tested in scripts background.

function ManagerChildGroupsUsers() {
		var selected = [];
		var grpManager = gs.getUserID();
//		gs.info('getMyManagedChildGroups: Manager = ' +grpManager);
		// Instead of dynamic for current user, I will use a specific manager that I know has groups
		grpManager='c092a629db66ae00580ed211ce9619a0';		// Rosie Matthews - Demo data

		//Get the groups where grpManager is the 'manager'
		var grps = new GlideRecord('sys_user_group');
		grps.addQuery('manager', grpManager);
		grps.query();
		while (grps.next()) {
			//Add the group sys_id values to the returned array
			selected.push(grps.getValue('sys_id'));
		}

		// next get the list of child groups
		var childGroups = [];
		for (i=0; i< selected.length; i++) {
			var childGrps = new GlideRecord('sys_user_group');
			childGrps.addQuery('parent', selected[i]);
			childGrps.query();
//			gs.info("Found " + childGrps.getRowCount() + " record where parent = " + selected[i]);
			while (childGrps.next()) {
				childGroups.push(childGrps.getValue('sys_id'));
			}
		}

		// now get the users in the child groups
		var users = [];
		for (i=0; i< childGroups.length; i++) {
			var userGrps = new GlideRecord('sys_user_grmember');
			userGrps.addQuery('group', childGroups[i]);
			userGrps.query();
//			gs.info("Found " + userGrps.getRowCount() + " record where group = " + childGroups[i]);
			while (userGrps.next()) {
//				gs.info("Adding user = " + userGrps.user);
				users.push(userGrps.getValue('user'));
			}
		}
		
		return users;
}

And Dynamic Filter Option:

Debug shows the script include is create the 'users' array correctly, but testing I get "(empty)" when I select that for "opened by", "is (dymanic)" and the script include.  I don't have time to see what is going on today, hopefully it'll work for you or someone else here will respond on that aspect.

[WazzaJC]

Hi Bert_c1,

Again thank you ever so much - I do greatly appreciate your help and expertise and you took the time to help me, so much appreciated kind sir.

Yes I have tried the above script and set up but I get 'Empty' when trying this on the 'Opened By' field in my Incident Report (it doesn't filter on the Opened By Users, it just filters on Opened By = Empty) it just doesn't seem to work (for now) on the 'Opened By' field, hopefully we may find a solution or another user may know why, but thank you for trying I really appreciate that, kind sir 🙂

Kindest Regards

[Bert_c1 · Accepted solution]

@WazzaJC ,

I got my last example to work for 'opened by' on incident list, I had a mis-spelling in the dynamic filter definition Script should be "new ManagerChildGroupsUsers();". Please test. And comment-out line 6 where I have a test user.

[WazzaJC]

Hello Bert_c1,

You are brilliant, wow this works perfectly!

It is all up and running now on my instance.

Thank you again for the effort and for continuing to follow through to a successful conclusion.

I am very grateful for your knowledge and help kind sir 🙂

Have a lovely evening, you are a star.

Kindest Regards.