For the BR script, if the Assignment Group ever gets blanked out, your "if" statement will skip that event. If that's not what you meant, you may need to remove the "if".
For retroactively filling in the new column value, you'd use GlideAggregate like below (not tested):
var ga = new GlideAggregate('sys_audit');
ga.addAggregate('MAX', 'sys_created_on');
ga.groupBy('documentkey');
ga.addQuery('tablename', 'incident');
ga.query('fieldname', 'assignment_group');
while (ga.next()) {
var sys_id = ga.documentkey; // sys_id of incident
var assigned_on = ga.getAggregate('MAX', 'sys_created_on'); // last time assignment_group changed
var gr = new GlideRecord('incident');
if (gr.get(sys_id)) { // find incident record
gr.u_assigned_on = assigned_on; // set value
gr.update();
}
}
Now that you're capturing this value in a field, you won't need that as a metric any more but use if for other metrics such as
- Duration between Opened and Last Assigned
- Duration between Last Assigned and Resolved
These in conjunction with reassignment_count and other metrics may provide valuable insights.
Again, if you ever need to see all reassignment history, you can use the very first solution I provided using sys_audit,
