Which Assignees have no incidents assigned to them..??!!

General Add comments
by:

“I cannot see which users in my assignment group are not assigned to any incident”.
A valid remark of a customer, which had multiple assignees in multiple assignment groups, and each assignee needed to have three active incidents per assignment group. How can we make this happen?

Database view

First I needed to get the info of the assignment group, the user and the incidents in one view. The most practical way to get this in one overview is by using a database view. In this view we need the assignment group and the users (retrieve this from the sys_user_grmember table) and the incidents (we can use the task table). See below the database view I have created.

As you can see I used a ‘Where clause’ to relate the task ‘Assigned to’ and ‘Assignment group’ from the TASK table to the ‘User’ and ‘Group’ field on the SYS_USER_GRMEMBER table. Be sure to set ‘Left join’ on the TASK table to true.

We will receive a list of all users in all assignment groups that are assigned to a task. We can now see that Fred Luddy is not assigned to any TASK in assignment group ‘RMA Approvers’ (as the number is (empty), but Fred Luddy is assigned to an incident in assignmet group ‘Network’ (we can see an incident number).

Although we got a handy view now, we do not have hard figures that we can use in reporting. We need to know if Fred Luddy is assigned to 0 incidents in assignment group RMA Approvers and assigned to 1 incident in Network. We need to have a count mechanism.

Get them counted, but where?

As we need a counting mechanism, we need a place where we can store the count and use it in our report. For this I thought it was best to add a counter on the ‘sys_user_grmember’ table. Make sure you create a new field on the sys_user_grmember table of the type ‘integer’. I labelled this field ‘Active Incidents’. See the dictionary record below.

Start counting, the business rule

To fill the u_active_incidents field we need to create a counter that will update the counter every time an incident is assigned to a user, but also when an assigned to is replaced by another assigned to. We need to update the count of the previous and current assigned to. We can do this best in a business rule. The business rule will store the previous assignment group and user information and the current assignment group and user information. With this information we can do a count on the u_assigned_users table (which we created by a database view) and fill in the information on the sys_user_grmember table in the u_active_incidents field.

[cc lang=”javascript”]
// Get the previous assigned to user and assignment group
var oldUSR = previous.assigned_to;
var oldGRP = previous.assignment_group;

// Get the current assigned to user and assignment group
var newUSR = current.assigned_to;
var newGRP = current.assignment_group;

// Set the task type which is needed to be counted, this can also be ‘change’ if you want to count changes
var type = ‘incident’;

// Check if the previous assignment group is empty or the same as the current assignment group. In both cases, the previous assignment group will become the current assignment group
if (oldGRP == ” || oldGRP == newGRP) {
var oldGRP = newGRP
}
// Check if the old user is not empty and perform a count on all incidents the user is assigned to in the previous assignment group by using a count query statement

if (oldUSR != ”) {

var ou = new GlideAggregate(‘u_assigned_users’);
ou.addQuery(‘grp_user’, oldUSR);
ou.addQuery(‘grp_group’, oldGRP);
ou.addQuery(‘tsk_sys_class_name’, type);
ou.addQuery(‘tsk_active’, ‘true’);
ou.addAggregate(‘COUNT’);
ou.query();

// Set the counter to zero

var countou = 0;

if (ou.next()){

// Check if counted record is not empty by using a regular query

var checkou = new GlideRecord(‘u_assigned_users’);
checkou.addQuery(‘grp_user’, oldUSR);
checkou.addQuery(‘grp_group’, oldGRP);
checkou.query();

if (checkou.next()){

// If the task number is not empty, the count is needed to be filled in with the actual counted records
// If the task number is empty, the counter is required to be set to 0

if (checkou.tsk_number != ”) {
countou = ou.getAggregate(‘COUNT’);
}
if (checkou.tsk_number == ”) {
countou = ‘0’ ;
}

}
}

// Now we are going to perform the same cyclus as above, but then with credentials of the current assigned to user and assignment group

var insou = new GlideRecord(‘sys_user_grmember’);
insou.addQuery(‘user’, oldUSR);
insou.addQuery(‘group’, oldGRP);
insou.query();

while (insou.next()) {
insou.u_active_incidents = countou;
insou.update();
}
}

if (newUSR != ”) {

var nu = new GlideAggregate(‘u_assigned_users’);
nu.addQuery(‘grp_user’, newUSR);
nu.addQuery(‘grp_group’, newGRP);
nu.addQuery(‘tsk_sys_class_name’, type);
nu.addQuery(‘tsk_active’, ‘true’);
nu.addAggregate(‘COUNT’);
nu.query();

var countnu = 0;

if (nu.next()){

var checknu = new GlideRecord(‘u_assigned_users’);
checknu.addQuery(‘grp_user’, newUSR);
checknu.addQuery(‘grp_group’, newGRP);
checknu.query();

if (checknu.next()){

if (checknu.tsk_number != ”) {
countnu = nu.getAggregate(‘COUNT’);
}

if (checknu.tsk_number == ”) {
countnu = ‘0’ ;
}
}
}

var insnu = new GlideRecord(‘sys_user_grmember’);
insnu.addQuery(‘user’, newUSR);
insnu.addQuery(‘group’, newGRP);
insnu.query();

while (insnu.next()) {
insnu.u_active_incidents = countnu;
insnu.update();
}
}
[/cc]

Report on it!

After a while, most of the users will have a count. We can now start reporting on the sys_user_grmember table and see which users are assigned to how many incidents per assignment group.
Please see the report I have created below

Request fulfilled!

If you have any question you can send me an email on tim.willer@2e2.nl

2 Responses to “Which Assignees have no incidents assigned to them..??!!”

  1. Jeremy Mandle Says:

    Hi Tim,

    Thanks for the great customization. I have however come across one thing that you may not have taken into account.

    The additional “u_active_incidents” attribute to the “sys_user_grmember” table that is updated whenever an Incident is assigned to the member of a group, which causes the user’s respective record in the “sys_user_grmember” table to be updated and fires off a few Business Rules.

    The “Group Member Add” Business rule is set to run on “Insert” and “Update” on the “sys_user_grmember”.

    So this means that every time an incident is assigned to a Group Member the “Group Member Add” Business Rule will grant their user account the roles assigned to the Assignment Group.

    We are looking at a few ways to handle this and may even create a custom table that references the “sys_user_grmember” table to store the “Active Incident” counts for Group Members.

    Definitely interested in any other ideas you may have.

  2. Tim Willer Says:

    Hi Jeremy,

    Indeed, the business rule “Group member add” is applicable to every update on the “sys_user_grmember”.

    But is the “Update” trigger really required for the business rule? Normally the “sys_user_grmember” only handles inserts and deletions. Updating “sys_user_grmember” records is quite unusual.

    You can look for the possibilities to deactivate the “Update” trigger or perhaps you can adjust the condition that the business rule should not fire if the “u_active_incidents” fields is updated? Both solutions really depend on your implementation of ServiceNow.

    In your situation an additional table might be a proper solution, please do not forget to check if there are new user – group relations and if the user – group relations are still present.

    Hope this helps you with implementing this feature!

Leave a Reply