As you can tell from the other answers, there are some simpler ways to kinda sorta get at what you're asking. If you want to nail down the exact answer to your question, though, you'll need to do a bit of customization. I actually put together something very similar to this for one of our teams. Here's how I did it; hopefully this will help.
First, create a new metric definition called "Help Desk Escalations". (If you call it something else, make a note of the exact name you call it; you'll need it in a script later.) Set the type to Script calculation, the table to Incident [incident], the Field to Assignment group, and give it an appropriate Description. For the script, just enter some comments. Here's a pasted screenshot of the metric definition I used; feel free to copy it or tweak it as you want:
Note the comment; because we don't have access to the previous value of the assignment group field here, we'll actually need to set up a business rule to record the metric instances. With that in mind, create a new business rule called "Record Help Desk escalation metric" (or really, whatever you want to, that's just what I called mine...). Make it an advanced rule on the Incident [incident] table. Have it run as an after rule on Insert or Update, and for the filter conditions, use:
- State is not one of Resolved, Closed, Canceled
- Assignment group changes from Help Desk
- Assignment group is not empty
I've pasted a screenshot of my business rule below for your reference:
On the Advanced tab, you'll need a script that records a metric instance whenever the business rule gets triggered. I've pasted a script you can use below. If your help desk queue isn't named "Help Desk" or you've named your metric definition something besides "Help Desk Escalations", you'll need to change the names in the variables at the top of the script.
If you really want to, you can hard-code the sys_id values in the script instead of looking them up as I have; that will save you a bit of a performance hit in going back to the database a couple of times in the script, but personally I avoid "magic numbers" (such as hard-coded 32-character sys_ids) when I can so that the script is more readable, even if it means doing a couple of extra lookups. Whatever floats your boat, though; if you're familiar enough with scripting to grok what's going on in this one, feel free to tailor it to your needs.
Also note that you don't have to pull the audit record to get the start time. If all you're interested in is the count of transfers out of the help desk queue and you don't care about how long it was in the help desk queue, feel free to strip out the audit log lookup and just leave the start time blank in the metric instance.
Anyway, with no further ado:
(function(current) {
var hdQueueName = 'Help Desk';
var metricDefName = 'Help Desk Escalations';
// Find the sys_id of the help desk queue
var grGroup = new GlideRecord('sys_user_group');
grGroup.addActiveQuery();
grGroup.addQuery('name', hdQueueName);
grGroup.query();
if (!grGroup.next()) return;
var hdQueueId = grGroup.getUniqueValue();
// Find the sys_id of the metric definition
var grMetricDef = new GlideRecord('metric_definition');
if (!grMetricDef.get('name', metricDefName)) return;
var metricDefId = grMetricDef.getUniqueValue();
// Search audit trail to find out when ticket was transferred to help desk
var ghs = new GlideHistorySet(current.sys_class_name, current.sys_id);
var setId = ghs.generate();
var grHist = new GlideRecord('sys_history_line');
grHist.addQuery('set', setId);
grHist.addQuery('field', 'assignment_group');
grHist.addQuery('new_value', hdQueueId);
grHist.orderByDesc('update_time');
grHist.setLimit(1);
grHist.query();
// If an audit record doesn't exist for when the ticket was transferred to
// the help desk, set the start time to null. This should never happen,
// but might if, for example, audit records for the incident have been
// cleared.
var gdtStart = grHist.next() ?
new GlideDateTime(grHist.update_time) : null;
var gdtNow = new GlideDateTime();
var gdDuration = gdtStart ?
GlideDateTime.subtract(gdtStart, gdtNow) : null;
// Create the metric instance record
var grMetric = new GlideRecord('metric_instance');
grMetric.newRecord();
grMetric.definition = metricDefId;
grMetric.table = 'incident';
grMetric.field = 'assignment_group';
grMetric.id = current.getUniqueValue();
grMetric.start = gdtStart;
grMetric.end = gdtNow;
grMetric.duration = gdDuration;
grMetric.calculation_complete = true;
grMetric.field_value = current.assignment_group;
grMetric.value = current.getDisplayValue('assignment_group');
grMetric.insert();
})(current);
Once all of this is set up, you should get one metric instance record each time a ticket is transferred out of the Help Desk queue to any other for an active incident. You can report directly against the metric_instance table, or better yet, the incident_metric database view so that you have access to the incident record fields also. I've pasted a screenshot of a sample report below:
Note how the same ticket (INC0009009) shows up three times in the report above. That's because in writing this post, I transferred the ticket into and out of the Help Desk queue in my personal instance three times, and a unique metric instance record is created each time it's transferred out.
If you only want a count of the unique incident transfers, you have two options. Option one is to make it a single score report and set aggregation to "Count Distinct" and the Sumfield [sumfield] field of the report to Number [inc_number]. Note that because ServiceNow is wonky, you'll have to set that sumfield value through a background script. (I don't know why it won't let you choose it, but it is what it is, as they say.)
Option two is to modify the business rule script slightly so that if a metric already exists for an incident, it overwrites it instead of creating a new one. Here's a version of the script that ensures that you have only one unique metric for the last time a ticket was transferred out of the Help Desk queue instead of a metric for every time it was transferred out:
(function(current) {
var hdQueueName = 'Help Desk';
var metricDefName = 'Help Desk Escalations';
// Find the sys_id of the help desk queue
var grGroup = new GlideRecord('sys_user_group');
grGroup.addActiveQuery();
grGroup.addQuery('name', hdQueueName);
grGroup.query();
if (!grGroup.next()) return;
var hdQueueId = grGroup.getUniqueValue();
// Find the sys_id of the metric definition
var grMetricDef = new GlideRecord('metric_definition');
if (!grMetricDef.get('name', metricDefName)) return;
var metricDefId = grMetricDef.getUniqueValue();
// Search audit trail to find out when ticket was transferred to help desk
var ghs = new GlideHistorySet(current.sys_class_name, current.sys_id);
var setId = ghs.generate();
var grHist = new GlideRecord('sys_history_line');
grHist.addQuery('set', setId);
grHist.addQuery('field', 'assignment_group');
grHist.addQuery('new_value', hdQueueId);
grHist.orderByDesc('update_time');
grHist.setLimit(1);
grHist.query();
// If an audit record doesn't exist for when the ticket was transferred to
// the help desk, set the start time to null. This should never happen,
// but might if, for example, audit records for the incident have been
// cleared.
var gdtStart = grHist.next() ?
new GlideDateTime(grHist.update_time) : null;
var gdtNow = new GlideDateTime();
var gdDuration = gdtStart ?
GlideDateTime.subtract(gdtStart, gdtNow) : null;
// Create the metric instance record
var grMetric = new GlideRecord('metric_instance');
grMetric.addQuery('definition', metricDefId);
grMetric.addQuery('id', current.getUniqueValue());
grMetric.query();
if (!grMetric.next()) {
// Metric doesn't already exist, so create it
grMetric = new GlideRecord('metric_instance');
grMetric.newRecord();
}
grMetric.definition = metricDefId;
grMetric.table = 'incident';
grMetric.field = 'assignment_group';
grMetric.id = current.getUniqueValue();
grMetric.start = gdtStart;
grMetric.end = gdtNow;
grMetric.duration = gdDuration;
grMetric.calculation_complete = true;
grMetric.field_value = current.assignment_group;
grMetric.value = current.getDisplayValue('assignment_group');
grMetric.isNewRecord() ? grMetric.insert() : grMetric.update();
})(current);
Hope this helps,
--Dennis R
