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();
}

[Tylerjknapp]

I have not ran the script yet, I figured I'd post it here before running it since it's been a bit since I used GlideScript. I looked in Performance Analytics and I couldn't find anything that would also show what assignment groups have had 0 incidents assigned to them in the past 6 months. I can see what AGs have had incidents assigned but it doesn't show me what has had none. I could just remove those ones from the list but then I would be assuming that the rest haven't had any, if I do it with a script I can confirm that the count is 0 before we remove those assignment groups. Essentially we are doing an assignment group cleanup. 

[DrewW]

You create a breakdown for the assignment group field.  Then add that breakdown to the incident indicator that is counting incidents.  You can set it up to count by day or by month.

Then when you look at the indicator you can then pick that breakdown and it will show you every group that is part of your assignment group breakdown and how many incidents were assigned, even if its zero.  From there you can look at it by Month, 3 months, quarterly sum and a bunch of others.

The PA can then be used for other things like whats the work load for each group based on the number of incidents that it handles and so on.

Another way you can do this is with a remote table (i.e. scripted table).  Since it runs a script to generate the data in the table you can just write the script so it queries the data the way you want to calculate the counts and then anytime someone runs a report on the table the system will build the data in it on demand from the data in the system and you will not have to run a script each time and send someone the results. Remote tables can also have flows run against them along with BR's so there is a bunch of automation you could add if so inclined.

Just tossing out other ways to go about it since I'm sure you will have to do this again and again as time goes on.

[Palash Sarkar1]

Hi 

I don't understand the script well. But I can give you a different solution to your expected result.

I prefer you can create a report as below capture. Hope you will find a new idea.

[Tylerjknapp]

So the issue I ran into with running a report is that it won’t show me ones with 0. However maybe it will doing it the way you have laid out. I will give it a try in the morning. Thank you!

[DrewW]

For this to work the way you want you would have to create a database view with the sys_user_group table first and then select Left join when you add the incident table.  Then the view will include all of the groups from the group table no matter what.