Find the list of assignment groups to which no incidents are assigned in last one year

[Bhagya7]

Hi All,
Greetings!
We are planning to do assignment group clean up in ServiceNow. Hence we would like to find the list of assignment groups which are unused (no incidents assigned) in last one year. Kindly help us with your valuable suggestions.
Thank you.

[Sourabh26]

Hi,

You can take the dump of your active incidents records which are assigned (last one year).

Also take the dump of all assignment group from (sys_user_group) table. 

And can compare both the groups value in excel maybe.

Mark this as Helpful/Correct, if Applicable.

Regards,

Sourabh

[Filipe Cruz]

Hello,

I can provide a script to display the name of all groups not used in the assignment group of incidents:

unusedGroups();

function unusedGroups(){
	var used_groups = [];
	
	var ga = new GlideAggregate("incident");
	ga.addAggregate("COUNT", "assignment_group");
	ga.addEncodedQuery("assignment_groupISNOTEMPTY");
	ga.query();
	while(ga.next()){
		used_groups.push(ga.getDisplayValue("assignment_group"));
	}

	var all_groups = [];

	var ga2 = new GlideAggregate("sys_user_group");
	ga2.addAggregate("COUNT", "name");
	ga2.query();
	while(ga2.next()){
		all_groups.push(ga2.getValue("name"));
	}


	var unused_groups = new ArrayUtil().diff(all_groups, used_groups);



	gs.print("Unused Groups: " + unused_groups.join(","));
}

run it in the scripts - Background (don't forget to elevate previleges!).

You might want to add a query to the groups in ga2 to filter only groups related with incident management.

Hope this helps!!

Please mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Best Regards,

Filipe Cruz

[palanikumar · Accepted solution]

Hi,

You can use the below script to get the groups not used in last one year. 

Note: This script checks only Incident table. If you want to check Incident, Change, problem etc then you can replace incident with task table

var ga = new GlideAggregate("incident");
ga.addNotNullQuery("assignment_group");
ga.addEncodedQuery("sys_created_on>javascript:gs.beginningOfOneYearAgo()");
ga.addAggregate("COUNT", "assignment_group");
ga.query();
var assignmentGroup = [];
while (ga.next()) {
    assignmentGroup.push(ga.getValue("assignment_group"));
}
var gr = new GlideRecord("sys_user_group");
gr.addQuery("sys_idNOT IN" + assignmentGroup.toString());
gr.addActiveQuery();
gr.query();
while (gr.next()) {
    gs.print(gr.name);
}