Metric Definition: How to calculate a numeric value from a duration value.

[Scott29]

I have a basic metric that calculates the time it takes from when a group is assigned to the first time assigned to it is populated. I cannot use the duration value as hoped in a report. How could I update the script to calculate a numeric value representing hours from the metric script's duration value and populate it in the "value field"?

The script I am currently using:

// variables available

// current: GlideRecord -  target incident

// definition: GlideRecord -  (this row)

var g = current.assigned_to;

if (g !="")

  createMetric();

function createMetric() {

  var mi = new MetricInstance(definition, current);

  if (mi.metricExists())

    return;

  var gr = mi.getNewRecord();

  gr.start = current.u_time_to_assignment_group;;

  gr.end = current.sys_updated_on;

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

  gr.calculation_complete = true;

  gr.insert();

}

I am trying to do this because when I build a report with the duration information, I cannot edit the X-axis; the column values are too high for my needs.

[Bert_c1 · Accepted solution]

Hi @Scott29,

the following script logic may help you. Tested in Scripts - Background:

var mi = new GlideRecord('metric_instance');
mi.addEncodedQuery('duration!=NULL');
mi.query();
while (mi.next()) {
	gs.info("Table: " + mi.table + ", field: " + mi.field + ", duration = " + mi.duration);
	var durDateTime = new GlideDateTime(mi.duration);
	gs.info('durDateTime = ' + durDateTime);
	var noSeconds = durDateTime.getNumericValue() / 1000;
	gs.info('noSeconds = ' + noSeconds);
	var rem = noSeconds % 3600;				// get remainder (minutes, seconds) we don't care about
	var noHours = (noSeconds - rem) / 3600;		// divide by no of seconds in a hour
	gs.info('noHours = ' + noHours);
}

You can remove the 'gs.info()' lines used for debugging. You could add a custom field to the metric_instance table to hold the hours value, or create a custom table with desired fields to report on.