Latest Assignment Date/Time stamp on Incident Table

rbaig · ‎12-21-2016

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

drjohnchun · ‎12-23-2016

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,

View solution in original post

mamtachaudhary · ‎02-07-2018

This worked.... just we need to make sure to change the filed name according to what we created.

Shoheb Shaikh · ‎01-28-2021

Hi @drjohnchun,

Could you please help me with my post. It is somewhat related to the above scenario.

My post:

https://community.servicenow.com/community?id=community_question&sys_id=74a3d1acdb0e2050a08a1ea66896199c

Regards,

Shoheb