Is it possible to have Metric Definitions create instances on historical activity?

[jxsavage]

Requirement is to provide detail of duration tickets are assigned to any priority before assigned to a new priority or being closed. This will require having start and end times for when those field values were assigned/reassigned.

My research so far shows that SLA's appear to require that duration start times be assigned to a date/time field existing within the task form itself. Metrics definitions can calculate on the activity time stamps, however, it appears they do not allow for consideration of audited data.

Maybe scripting is required for either option? Or maybe careful querying of the audit tables is the only option?

Looking for any suggestions. Thanks.

[Jace Benson]

Hey Josh,

I just did something like this at work.   I looks specifically how metrics work.   (see script action "Metric Update")

They use a method called, "GlideRecordRollback"

I thought the same thing, I'll parse the audit data and I did that and it worked great for field duration metrics, but not for scripted metrics.

Things to note.   Scripted metrics, well all metrics, revert the "current" record to it's version it was when the metric fired, and that is a reason you should NOT USE gs.nowDateTime(), instead use current.sys_updated_on or something with the current record.

Scripted metrics running in order is important.

With that.   Here's the script action I am using to create old metric instances for new metric definitions

/*jslint eqeq: true, undef: true, sloppy: true, vars: true */

/*global gs, GlideRecord*/





var definition = new GlideRecord('metric_definition');

definition.get(event.parm1);

definition.query();

if (definition.next()) {

       var targetRecord = new GlideRecord(definition.table);

       targetRecord.orderBy('sys_created_on');

       gs.log(definition.name + ': Query against the target table(' + definition.table + '): ' + targetRecord.getEncodedQuery(), 'historic.metric.instances');

       targetRecord.addQuery('sys_created_on', '>', definition.u_create_metrics_after);

       targetRecord.query();

       while (targetRecord.next()) {

               var audit = new GlideRecord('sys_audit');

               audit.addQuery('documentkey', targetRecord.sys_id.toString());

               audit.addQuery('fieldname', definition.field);

               audit.orderBy('record_checkpoint');

               audit.query();

               while (audit.next()) {

                       try {

                               var arrOfFields = [];

                               var count = new GlideAggregate('sys_audit');

                               count.addQuery('documentkey', targetRecord.sys_id.toString());

                               count.addQuery('record_checkpoint', audit.record_checkpoint);

                               count.addAggregate('COUNT', 'fieldname');

                               count.query();

                               while (count.next()) {

                                       arrOfFields.push(count.fieldname.toString());

                               }

                               arrOfFields = '[' + arrOfFields + ']';

                               var gdt = new GlideDateTime();

                               gdt.addSeconds(audit.record_checkpoint * 120);

                               gs.eventQueueScheduled('metric.update', targetRecord, arrOfFields, audit.record_checkpoint, gdt);

                       } catch (e) {

                               gs.log('generateScriptedMetrics against ' + current.number.toString() + ' for ' + definition.name.toString() + 'Error: ' + e, 'historic.metric.instances');

                       }

               }

       }

}

[jxsavage]

Thanks, Jace. Sorry for the late acknowledgement. I ended up manually tracing the records and logging them in a local DB, as the requirements needed delivery too soon to allow for research and testing. Our next step was to manually insert those records into the definition table so as to fill the gap. Just out of curiosity, from your perspective would that approach cause any concerns? In the mean time I'm going to forward this script to our admins. Thanks again.