Fix Script to populate custom field with duration on metric

[Rocky5]

Hello Experts,

I want to update the historical records. i.e. I have a custom field called 'state duration' on projects and we have a metric created 'Duration' is populated when state is changed. So, I need help with fix script to populate my custom field on the project form with 'Duration' field on metric for all existing/historical records.

Any help with fix script is appreciated.

Thanks,

Rocky.

[Arun_S1]

@Rocky5 Metrics are stored in a table called "metric_instance", to query the correct record you need the following

1. Definition - Sys_ID of the Metric definition which calculates the state change duration.

2. Value (Exact State)- There may be multiple state changes recorded in the instance table.

for example an incident may be moved from New to In Progress, from In Progress to Resolved, and then from Resolved to Closed. In this case there will 3 records in the instance table for each state.

Please finalize which state change duration needs to be updated in the project record.

3.  Please check if the custom field is a string field or a duration field.

We can write the fix script when we have the answer for above 3 points. Below is the script I have written to find the new state duration for an incident and update in the custom duration field in the incident, please modify accordingly for projects.

var incident=new GlideRecord('incident');
incident.addQuery('number','INC0000100');
incident.query();
if(incident.next()){
var instance=new GlideRecord('metric_instance');
short_table_name='incident';
instance.addEncodedQuery('table=incident^field=incident_state^definition=35f2b283c0a808ae000b7132cd0a4f55^value=New^id='+incident.sys_id);
instance.query();
if(instance.next()){
incident.u_new_duration=instance.duration;
incident.update();
}

}

Please mark the appropriate response as correct answer and helpful.

Thanks!!

[Rocky5]

Hi Arun,

Thank you for the response, So.. We have separate metrics for each state change. For now I am looking for Open to Inprogress (our metric definition is called 'Open to Inprogress duration') . So I called my custom field as 'Open State Duration', And I need to update 'Open State Duration' field with 'Duration' on my metric definition. And I want to update this for all existing records not just one or two records.

And yes my custom field is of 'Duration' type only.

Thanks,

Rocky.

[Riya Verma]

Hi @Rocky5 ,

Hope you are doing great.

To update the historical records and populate the custom field 'state duration' on projects with the 'Duration' field from the metric, we can use a Fix Script in ServiceNow.

function updateHistoricalRecords() {
  var projectGR = new GlideRecord('project'); // Replace 'project' with the actual table name for projects
  
  // Add any necessary filters to target specific records if needed
  // e.g., projectGR.addQuery('state', 'some_value');
  
  projectGR.query();
  
  while (projectGR.next()) {
    var durationMetricGR = new GlideRecord('metric'); // Replace 'metric' with the actual table name for the metric
    
    // Add any necessary filters to match the appropriate metric record for the project
    // e.g., durationMetricGR.addQuery('project', projectGR.sys_id);
    
    durationMetricGR.query();
    
    if (durationMetricGR.next()) {
      // Update the 'state duration' field on the project with the 'Duration' field from the metric
      projectGR.state_duration = durationMetricGR.duration;
      projectGR.update();
    }
  }
}

updateHistoricalRecords();