Calculate time between incident creation and when service offering field is updated

[Doraemon]

 Hi, need a method to report incidents assigned to the Service Desk where the Assignee took more than 4 hours to update the service offerring field.

Calculation should be  time difference between  service_offering field is updated  and incident was created(sys_created_on )

[Ankur Bawiskar]

@Doraemon 

you can use a custom field to hold when Service Offering field got populated

Have a before update business rule

Condition: Service Offering Changes AND Service Offering IS NOT EMPTY

Script:

current.u_updatetime = new GlideDateTime();

Then use this field is reporting and determine the 4 hours check

for this you can use calculate field in reports

Calculated Fields in Report 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[J Siva]

Hi @Doraemon 

You can achieve this in different ways. One of the ways is using business rule and metric definition.

1. Create one metric definition to capture the duration.
Metric definition sample:
Note: We are going to use this metric definition only to calculate the duration by updating the existing metric instance record. Inserting the instance record will be taken by Business rule.

var record = new GlideRecord('metric_instance');
record.addQuery("id", current.sys_id);
record.addQuery("definition", definition.sys_id);
record.addQuery("calculation_complete", false);
record.query();
if (record.next()) {
    if (!gs.nil(current.service_offering)) { // Complete the calculation in Authorize/New/Approve (SOX) state
        var endDate = new GlideDateTime(current.getValue('sys_updated_on'));
        var startDate = new GlideDateTime(record.getValue('start'));
        record.end.setValue(endDate);
 
        var schedule = new GlideSchedule('38fa64edc0a8016400f4a5724b0434b8'); // sys id of 24/7 shcedule
        var duration = schedule.duration(startDate, endDate);
        record.duration = duration;
 
        record.calculation_complete = true;
        record.update();
    }
} 

2. Business rule: Crate one "After" Business rule to insert the record in the metric instance table.
Sample:

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

    var definition = GlideRecord('metric_definition');
    definition.get('b3061c89c3d8221091ea5242b40131e4'); // metric definition sys id

    // Add your code here
    var gr = new GlideRecord('metric_instance');
    gr.initialize();
    gr.table = current.getRecordClassName();
    gr.id = current.sys_id;
    gr.definition = definition.sys_id;
    gr.field = definition.field;
    gr.start.setValue(new GlideDateTime().getValue());
    gr.insert();

})(current, previous);

Output: You can build the report on Metric instance table

Hope this helps.
Regards,
Siva

[Doraemon]

Hi Siva, Thanks for the detailed response. I tried this in my instance and i do not see anything in metric instance table when i update service offerring for an incident.
Also, i did not understand this part: Note: We are going to use this metric definition only to calculate the duration by updating the existing metric instance record.

[J Siva]

Hi @Doraemon 

Here two things are there, 

1. Insert the record into the metrics instance table whenever the service offering field is empty (insert metric instance record)

2. Calculate the duration once the service desk agent updated the service offering field. (Update metric instance record)

We are inserting the instance record using business rule. While inserting we are storing the start time.

Then we are using metric definition to capture the end time once ethe agent updated the service offering.

Make sure to use the status ids from your instance.

If you still have any queries, just paste your scripts and BR snaps. Will review.

Thanks,

Siva