Average time to resolve a request ticket

[Debbi L]

So, I have a bit of a conundrum. We've created a separate ticketing module called Construction Engineering. It functions like the Incident module, but is set up like the Request module. (Not sure why, this was before my time...) We've recently had to extend the time between resolution and close from 3 days to 30 days. However, doing that just killed their metrics, as request measures average time to close, not resolve, and there is no "Resolve" time stamp in the table. Is there a way to time stamp when it was set to the "Resolved" state, and then measure from that? That is probably the easier part of the question. The harder part is this: Once we've set this up, can we query ticket history so that the historical tickets also have this data?

[gyedwab · Accepted solution]

Hi Debbi,

If you don't have a metrics definition measuring state, then the only place this information would live is in the audit tables.

The audit tables are not indexed, so reporting against them is not recommended.

What might be possible (but might also not be recommended) is to create a new "Resolved" field, and then run a one-time script to populate it based on the audit table.... maybe clone prod down to dev, run the script there, and import the resulting data to prod?

[Debbi L]

Thanks, Guy. I'll start looking at those.

[Dennis R]

Hey Debbi, we faced the same problem at my company.   We added a Resolved Time field and a business rule when the state is changed to check if the new state is resolved or closed (since some people directly close tickets without going through the resolved state), and if so, set the timestamp field with the data.

As for retroactively getting the data, if the table is audited, you'll need to query the sys_audit log to see when that happens. But please be careful, rule #1 of querying the sys_audit table is to make sure you filter by the documentkey field. Otherwise, your script will run full table scans against the table, and since it typically contains millions of rows, it will take forever and consume system resources.

I don't know your exact database layout, but I'll copy a script I used to do something similar below.   You won't be able to use it straight as posted; you'll need to customize it to hit against your table and your particular fields, but it should be a really good starting point.   The field name we use for resolution time is u_resolved_on, and we also track the person who resolved the incident (u_resolved_by).u_resolved_on

/**
 * Iterates through all tickets that do not have [u_resolved_on] field set, and
 * for each one, checks history to see if they should be and sets them if so.
 *
 * Revised 2106-12-09 by Dennis R
 * - Added query condition to filter out records that aren't closed.
 * - Added check to correct closed_by field as well as closed_at
 *
 * Initially created 2016-12-05 by Dennis R
 */

(function RepairResolvedTimestamps() {
       /**
         * Configuration
         */
       var ADMIN_NAME = 'admin';
       var grNoResolved = new GlideRecord('incident');
       var grAudit = null;
       var grUser = null; 

       var total_count = 0;
       var modified_count = 0;
       var log_msg = '';
       var sys_admin = null;
       var record_edited;

       var resolved_timestamp;
       var resolved_user_id;
       var closed_timestamp;
       var closed_user_id;

       var modified;
       var modified_count = 0;

       // The following is for debugging purposes.   Comment it out or delete it
       // when you're confident that this script is ready to go live.
       var limit = 100;

       grNoResolved.addEncodedQuery(
                       // Incidents where u_resolved_on or u_resolved_by isn't populated
                       'u_resolved_byISEMPTY^OR' +
                       'u_resolved_onISEMPTY^' +

                       // But where the incident is closed (state '7') or resolved ('6')
                       'incident_state=7^OR' +
                       'incident_state=6^OR' +
                       'state=7^OR' +
                       'state=6' );
       grNoResolved.query();
       total_count = grNoResolved.getRowCount();

       while (grNoResolved.next()) {
               record_edited = false;
               log_msg = grNoResolved.getValue('number') + ':';


               // Find the latest closure/resolution information in the audit log for
               // the incident.
               grAudit = new GlideRecord('sys_audit');
               grAudit.addQuery('documentkey', grNoResolved.getValue('sys_id'))
                             .addCondition('newvalue', '6')     // State 6 = Resolved
                             .addOrCondition('newvalue', '7') // State 7 = Closed
                             .addCondition('fieldname', 'state')
                             .addOrCondition('fieldname', 'incident_state');
               grAudit.orderByDesc('sys_created_on');
               grAudit.query();

               resolved_timestamp = null;
               closed_timestamp = null;   // Used to track closed timestamp in case
                                                                   // resolved timestamp cannot be found.
               resolved_user_id = null;
               closed_user_id = null;             // Used to track closed user in case
                                                                   // resolved user cannot be found.
               while (grAudit.next()) {

                       if (grAudit.newvalue == '6') {
                               resolved_timestamp = grAudit.sys_created_on;
                               resolved_user_id = grAudit.user;
                               break;
                       }
                       if (grAudit.newvalue === '7' && closed_timestamp === null) {
                               closed_timestamp = grAudit.sys_created_on;
                               closed_user_id = grAudit.user;
                       }
               }

               // Normalize the resolved by user and timestamp if needed
               if (resolved_timestamp === null) {
                       resolved_timestamp = closed_timestamp;
                       resolved_user_id = closed_user_id;
               }

               // Do we need to update the u_resolved_by field?
               if (grNoResolved.u_resolved_by.nil()) {
                       // Can we update the u_resolved_by field?
                       if (resolved_user_id !== null) {
                               grUser = new GlideRecord('sys_user');
                               if (grUser.get('user_name', resolved_user_id)) {

                               }
                       }
               }


               if (resolved_timestamp !== null) {


                       if (grNoResolved.u_resolved_by.nil() && resolved_user_id !== null) {
                               // If the user is not 'system', try to set the resolved_by
                               // field to be the user who resolved the incident.
                               if (resolved_user_id !== 'system') {
                                       grUser = new GlideRecord('sys_user');
                                       if (grUser.get('user_name', resolved_user_id)) {

                                       }
                               }
                       }

                       // The following is for debugging purposes.   Comment it out or
                       // delete it when you're confident that this script is ready to
                       // go live.
                       gs.print('Set ' + grNoResolved.number + ' resolved to ' +
                                       resolved_timestamp);


                       //// THE FOLLOWING LINES WILL ACTUALLY MAKE CHANGES.   DO NOT
                       //// UNCOMMENT THEM UNTIL YOU'RE READY TO MAKE CHANGES TO THE DATA
                       //// IN THE INSTANCE!
                       // grNoResolved.u_resolved_on = resolved_timestamp;
                       // grNoResolved.setWorkflow(false);
                       // grNoResolved.autoSysFields(false);
                       // grNoResolved.update();
                       //// END OF SYSTEM CHANGES SECTION
               }



               // The following is for debugging purposes.   Comment it out or
               // delete it when you're confident that this script is ready to go
               // live.
               if (modified_count >= limit) break;
       }
       gs.print(total_count + ' records, ' + modified_count + ' modified.');
})();

Hope this helps,

--Dennis R

[Debbi L]

Dennis, thank you! I'll do some modification and see where this takes me. I'll let you know how it works.