Latest Assignment Date/Time stamp on Incident Table

[rbaig]

We have to run some reports based on incoming tickets for an assignment group by month.

I don't see an OOTB field on Incident table which captures Assignment date/time stamp.

There is only opened field, but the requirement is on assigned to group date/time stamp.

Please help on same.

Thank you

[rbaig]

Fantastic!

I have created a report using database view and Metrics. This will help the client to know the assignment group involvement count on a monthly basis as trend.

As said, I needed a field on the list, preferred to go with a 4th option.

For the 4th option -

The below simple BR is created, let me know if it's correct.

1. Created a new field on Incident table.

2. Added Before Business Rule (Insert and Update)

Condition -

(current.assignment_group != previous.assignment_group)

(function executeRule(current, previous /*null when async*/) {

if (current.assignment_group !== null){

current.u_assigned_on = current.sys_updated_on;

}

})(current, previous);

CAVEAT: This will only capture the field value going forward, not retroactively (unless you write a script to populate this retroactively)

It would be helpful if can get a reference script to populate this field for the past.

[drjohnchun · Accepted solution]

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,

[rbaig]

Thank you very much, John

[drjohnchun]

You're welcome. Although not a likely scenario, I'd like to add that sys_audit doesn't track record insertions. So if an incident record has an assignment_group set at the time of creation and it never changes, then it won't be captured in sys_audit. After you run the script to retroactively populate u_assigned_on and still find blank values, then this may be the case. If so, you can fill in u_assigned_on with sys_created_on (rather than opened_at) of the same record.

[rbaig]

Good point, it would be the case with all First Call Resolution tickets.