Duration of State with respect to assignment group

[AryanSingh]

Hi All,

I have a requirement to calculate the state of the Sc_task record with respect to the assignment group.

For example- A sc_task was assigned to Service desk and they keep it on open state for 2 hours then move it to Data center group and then they keep it for open state for 1 hour and 3 hour in work in progress state.

So i want the 3 individual records in Metric table....that will give me the insight that this assignment kept this task on XYZ duration.

Please let me know, if some can help me on this?? 

Also can this be achieve in Metric definition? 

[Shruti]

Hi 

It is possible using metric definitions

1. Create a new metric definition for group field

Paste below code and updated metric definition sysid highlighted in the code

// This script creates a new metric instance when assignment_group or state changes

var grMet = new GlideRecord('metric_instance');

var tableName = 'sc_task';

var currentGroup = current.assignment_group.getDisplayValue() ? current.assignment_group.getDisplayValue() : 'Unassigned';

var currentState = current.state.getDisplayValue();

var value = currentGroup + ' - ' + currentState; // Combined value to track

// Close the previous metric instance

var grPrev = new GlideRecord('metric_instance');

grPrev.addQuery('id', current.sys_id);

grPrev.addQuery('calculation_complete', false);

grPrev.addQuery('definition', 'sys_id of group change metric definition'); //sys_id of the current metric definition

grPrev.query();

if (grPrev.next()) {

    grPrev.end = current.sys_updated_on;

    grPrev.duration = gs.dateDiff(grPrev.start.getDisplayValue(), grPrev.end.getDisplayValue());

    grPrev.calculation_complete = true;

    grPrev.update();

}

// Create a new metric instance for the new state/group

grMet.initialize();

grMet.definition = 'sys_id of group change metric definition'; //sys_id of the current metric definition

grMet.id = current.sys_id;

grMet.table = tableName;

grMet.start = current.sys_updated_on;

grMet.value = value;

grMet.calculation_complete = false;

grMet.insert();

2. Create another metric definition for state field

Paste the script and update the metric definition sysid highlighted in code below 

// This script creates a new metric instance when assignment_group or state changes

var grMet = new GlideRecord('metric_instance');

var tableName = 'sc_task';

var currentGroup = current.assignment_group.getDisplayValue() ? current.assignment_group.getDisplayValue() : 'Unassigned';

var currentState = current.state.getDisplayValue();

var value = currentGroup + ' - ' + currentState; // Combined value to track

// Close the previous metric instance

var grPrev = new GlideRecord('metric_instance');

grPrev.addQuery('id', current.sys_id);

grPrev.addQuery('calculation_complete', false);

grPrev.addQuery('definition', 'sys_id of group change metric definition'); //sys id of metric definition created in step 1

grPrev.query();

if (grPrev.next()) {

    grPrev.end = current.sys_updated_on;

    grPrev.duration = gs.dateDiff(grPrev.start.getDisplayValue(), grPrev.end.getDisplayValue());

    grPrev.calculation_complete = true;

    grPrev.update();

}

// Create a new metric instance for the new state/group

grMet.initialize();

grMet.definition = 'sys_id of group change metric definition'; //sys id of metric definition created in step 1

grMet.id = current.sys_id;

grMet.table = tableName;

grMet.start = current.sys_updated_on;

grMet.value = value;

grMet.calculation_complete = false;

grMet.insert();

[Tanushree Maiti]

Hi @AryanSingh 

There are 2 options:

Option1 : If you need to display this information directly on the task form or in a report that includes fields from sc_task , you can create a database view on sc_task ( refer  OOB database view incident_metric)

Refer: Calculating task states 

Option2: Create a custom metric definition

• Navigate to Metrics > Definitions.
• Click New.
• Fill in the form:
  • Name: SC Task Group State Duration
  • Table: Catalog Task
  • Field: state or assignment group as per your requirement

Sample script/ Not tested:

var grMetric = new GlideRecord('metric_instance');
var definitionName = 'SC Task Gr State Duration'; // Replace your Metric Name

grMetric.addQuery('definition.name', definitionName);
grMetric.addQuery('id', current.sys_id);
grMetric.addQuery('calculation_complete', false);
grMetric.query();

if (grMetric.next()) {
if (grMetric.field_value != current.assignment_group.getDisplayValue() + ' - ' + current.state.getDisplayValue()) {
grMetric.calculation_complete = true;
grMetric.end = current.sys_updated_on;
grMetric.duration = gs.dateDiff(grMetric.start, grMetric.end, true);
grMetric.update();
createInstance();
}
} else {
createInstance();
}

function createInstance() {
var newgrMetric = new GlideRecord('metric_instance');
newgrMetric.initialize();
newgrMetric.definition = definition; 
newgrMetric.id = current.sys_id;
newgrMetric.table = 'sc_task';
newgrMetric.field_value = current.assignment_group.getDisplayValue() + ' - ' + current.state.getDisplayValue();
newgrMetric.start = current.sys_updated_on;
newgrMetric.calculation_complete = false;
newgrMetric.insert();
}