Incident count per assignment group

[Tylerjknapp]

Hi everyone, 

I'm trying to query the incident table and then count how many incidents have been assigned to each assignment group. I would add a column to the sys_user_group table named u_group_incidents and populate that column via a scheduled job. We are trying to detemine how many groups have had 0 incidents assigned to them in the past 6 months. So if I can populate that column and then filter for opened on at or after 6 months, then I should be able to export that to an excel document. However, it's been a while since I used GlideScript and so I wanted to post what I have here first. This is what I have so far, any feedback is appreciated - 

var grp = new GlideRecord('sys_user_group');
grp.query();
while(grp.next()){
//Query for the number of incidents per AG
var grpInc = new GlideAggregate('incident');
grpInc.addQuery('assignment_group', grp.sys_id);
grpInc.addAggregate('COUNT');
grpInc.query();
var groupIncidents = 0;
if(grpInc.next()){
groupIncident = grpInc.getAggregate('COUNT');
grp.u_group_incident = groupIncident;
}
//Update the group record with the new counts
grp.update();
}

[Allen Andreas]

Hi,

Does this mean you are seeing an error? It's not working? Have you used log statements, etc.?

Not sure the correlation between you not using GlideRecord/GlideAggregate query in awhile and this post, unless you are trying to optimize it for performance? Since you're not saying that it's not working.

Can you give us a bit more specific information to assist unless you're just wanting format review?

GlideRecord: https://developer.servicenow.com/dev.do#!/reference/api/quebec/server/no-namespace/c_GlideRecordScop...

GlideAggregate: https://developer.servicenow.com/dev.do#!/reference/api/quebec/server/no-namespace/c_GlideAggregateS...

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Allen Andreas]

Hi,

Thanks for marking my reply as Helpful.

A few things with this approach, if you're querying incidents and their assignment groups at some specific intervals (like one a day, etc.), in theory you're missing the time in-between, right?

There's a few ways to go about this, but one way, would be to create a field on the group record called something like: Last assigned incident and it's a date/time field? Then on the incident table, use condition if the assignment group changes, and simply write the current date/time over this field on the group record.

There's flaws to any of this and that's the date/time could get set by someone accidentally assigning the incident to it, then reassigning right after...so nothing you do (unless a literal count that is constantly adjusted) would be 100%.

Then, you could use a simply report that looks at the group table (and perhaps "type field" is ITIL or Incident -- to indicate it's an incident eligible group) and checks if this date/time is greater than 6 months, if so, show it on the list. Then you could even move that report to a dashboard and have it as part of your admin checks, etc.

Anyways, like I said, there's a ton of ways to go about this, but before you deactivate a group, you'd remove all the members, and as part of that, you could look at the task table before doing so, etc. and see if their group is currently assigned anything (so it's not limited to just incidents). I'd imagine it's pretty rare to deactivate groups if their only created when absolutely needed. So taking a few moments to check across the platform is worth it and shouldn't take that long, even if manually.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[DrewW]

If you are going to start trying to track the value of the assignment group field then just setup a metric for the assignment group field.  I think there is one there by default for incidents. 

[DrewW]

If you have Performance Analytics that would be a better way to do this I think.