Database view

[Rahulkalra]

I have a requirement to create a scheduled report for Any tasks  which are assigned to some group and it checks if that group has no active members . Which means only those tickets should be visible in the report those have assignment group with no active members . I know it has to be done through database view . Can anyone suggest?

[PavanBV]

Hi @Rahulkalra ,

I'm not sure if we can achieve that with a single DB view. One approach that i can think of is to have a script include that returns the names of the groups with no active users.

In your report you can have the conditions like Active is True AND AssignmentGroup.Name is one of "javascript: new SIName().function in your SI"

Have your tried implementing this approach. Please let me know if you if you face any issues.

Regards,

Pavan BV

[Bert_c1]

I agree with PavanBV, a script include follows:

script contents:

var getGroupsWithNoUsers = Class.create();
getGroupsWithNoUsers.prototype = Object.extendsObject(AbstractAjaxProcessor, {

	getEmptyGroups : function() {
		var grpid = [];
		var grp = new GlideRecord('sys_user_group');
		grp.query();
		while (grp.next()) {
			grpid.push(grp.sys_id.toString());
		}

		var result = [];
		for (i = 0; i < grpid.length; i++) {
			var grpUser = new GlideRecord('sys_user_grmember');
			grpUser.addQuery('group', grpid[i]);
			grpUser.query();
			if (!grpUser.next()) {
				result.push(grpid[i]);
			}
		}
		return result;
	},

    type: 'getGroupsWithNoUsers'
});

Dynamic filter, for use on 'Assignment Group' field (any reference field to sys_user_group).