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.

[Appanna M]

Hello @Suvedha Vignesh ,

You can try the below code once. 

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',task.assignment_group); // Here is the error earlier in your code.
        task.query();
        if(task.next())
        {
        record.push(task.number);
      }
        }
        }
       
        }
         gs.print(record);

Please Mark My Answer as Helpful and Accept as Solution, if you find this article helpful or resolves your issue.

[Suvedha Vignesh]

Hi Appanna,

Thank you for the reply, It is now working .

Thanks,

Suvedha

[Suvedha Vignesh]

Hi Appanna,

Thank you for the reply.

It is not working,

Thanks,

Suvedha

[Prasanna_Patil]

Hi Suvedha

Could you please elaborate the problem statement with screen shot

Regards