Hey Scott, this should get you what you're looking for.
Create a metric called something like Incident Touched. Use the following settings:
- Name: Incident Touched
- Table: Incident [incident]
- Field: Assignment group
- Type: Script calculation
- Timeline: (leave blank)
- Active: True (checked)
- Description: Creates a metric to record that an assignment group touched an incident. Does not create duplicate metrics if an incident is assigned to an assignment group multiple times.
- Script: See below
Script:
/**
* Creates a metric to record that an assignment group touched an incident.
* Does not create duplicate metrics if an incident is assigned to an
* assignment group multiple times.
*
* Parameters
* These parameters are passed into all metric definition scripts.
*
* current
* The current record that triggered the metric definition
*
* definition
* The metric definition being triggered
*
* mi
* A MetricInstance object (see the MetricInstance script include for more
* information and documentation). I'm not a big fan of using the mi object
* because it doesn't really contain complete functionality or the ability
* to customize metrics to the extent that a custom script normally
* demands.
*
* Created by Dennis R
* 2017-08-16: Initial creation
*/
(function calculateMetric(current, definition, mi) {
// Check to see if a metric instance already exists for this ticket
// assigned to this assignment group
var grMetric = new GlideRecord('metric_instance');
grMetric.addQuery('id', current.getValue('sys_id'));
grMetric.addQuery('definition', definition.getValue('sys_id'));
grMetric.addQuery('value', current.getDisplayValue('assignment_group'));
grMetric.query();
if (grMetric.hasNext()) {
// If so, then this ticket has already been counted for this assignment
// group and there's no need to do anything.
}
else {
// If not, create one
var now = new GlideDateTime();
var instant = new GlideDuration(0);
grMetric = new GlideRecord('metric_instance');
grMetric.initialize();
grMetric.setValue('table', current.getRecordClassName());
grMetric.setValue('id', current.getValue('sys_id'));
grMetric.setValue('definition', definition.getValue('sys_id'));
grMetric.setValue('field', definition.getValue('field'));
grMetric.setValue('value', current.getDisplayValue('assignment_group'));
grMetric.setValue('duration', instant);
grMetric.setValue('business_duration', instant);
grMetric.setValue('calculation_complete', true);
grMetric.setValue('start', now);
grMetric.setValue('end', now);
grMetric.insert();
}
})(current, definition, mi);
(You won't hurt my feelings too much if you leave it unattributed to me; that's just a standard header I put on all my scripts.)
Save the metric definition. This is a screenshot of what your settings should look like on the definition:
Now create the report. Run it against the Incident Metric [incident_metric] database view. Make sure you add the following filter conditions:
- Definition is Incident Touched
- Value is Team Z (note that because Value is a text field, you'll have to type this in instead of selecting it)
If you don't want tickets that ultimately ended up in Team Z's queue (for example, only tickets that ended up in the queue of Team A, Team B, or etc.), then add a filter for:
- Assignment group is not Team Z
If you want tickets that ONLY ended up in a queue of Team A, Team B, or etc. (that is, that didn't end up in some outside queue), add a filter such as:
- Assignment group is Team A OR
- Assignment Group is Team B OR
- Assignment Group is Team C OR
- ...
Personally, I would save this filter as a report source so that if you decide you want to filter it further by other criteria, you don't have to keep adding those filters first. After that, you can add whatever other filters you want, such as created between, updated after, or whatever.
When that report is run, you will get a list of tickets without duplicates that were touched by Team Z at some point and, if you added the assignment group is not Team Z filter, ended up in some other team's queue.
If you just want a count and not a list of tickets, change your report time from List to Single Score. (But I'd leave it as list at least initially to validate that you're getting back the records you're looking for.) And as a side note, the time that the ticket was initially assigned to Team Z will be in the Start field of the Incident Metric view.
Hope this helps, and let me know if you have any trouble,
--Dennis R
