Report that shows any incidents/prb/chg/request assigned to active Groups with zero members

[Suvedha Vignesh]

Hi all,

When I create a report for any of the ITSM Tables (e.g: Incident, Problem, Request or Change).Then I can use the script to show any records that are assigned to active groups with zero members.

I have written the script in task table and grpmember table to retrieve the data.

var record =[];

 var tsk = new GlideAggregate('task');

        tsk.addEncodedQuery('sys_class_name=incident^ORsys_class_name=problem^ORsys_class_name=change_request^ORsys_class_name=sc_task^assignment_groupISNOTEMPTY^active=true^assignment_group.active=true');

        tsk.groupBy('assignment_group');

        tsk.addAggregate("COUNT");

        tsk.query();

        while (tsk.next()) {

        //  gs.print(tsk.getAggregate('COUNT')+' ' + tsk.getDisplayValue('assignment_group'));

        var team = new GlideAggregate("sys_user_grmember");

        team.addEncodedQuery("group=" + tsk.assignment_group);

        team.addAggregate("COUNT");

        team.query();

        if(team.next())

        {

    // gs.print(team.getAggregate("COUNT"));

      var counts =team.getAggregate("COUNT") ;

      if(counts == 0)

      {

        var task = new GlideRecord('task');

       task.addEncodedQuery('sys_class_name=incident^ORsys_class_name=problem^ORsys_class_name=change_request^ORsys_class_name=sc_task^assignment_groupISNOTEMPTY^active=true^assignment_group.active=true');

        task.addQuery('assignment_group',tsk.assignment_group);

        task.query();

        if(task.next())

        {

        record.push(task.number);

      }

        }

        }

        }

         gs.print(record);

I am getting the count correctly, the problem i am facing is after getting zero members, then i need to glide the task table to get the record, which it is not working. Any one could help me

Thanks,

Suvedha.

[luffy3478 · Accepted solution]

var groupsWithoutMembers = [];
var list = new GlideRecord("sys_user_group");
list.addEncodedQuery("active=true^RLQUERYsys_user_grmember.group,=0,m2m^ENDRLQUERY");
list.query();
while (list.next()) {
    groupsWithoutMembers.push(list.sys_id + "");
}

var taskGR = new GlideRecord("task");
taskGR.addQuery("sys_class_name", "incident");
taskGR.addQuery("assignment_groupIN" + groupsWithoutMembers);
taskGR.query();

[Suvedha Vignesh]

Hi luffy,

Thank you very much.

It is working as expected.

Thanks,

Suvedha.