Reporting On Hold average time for a given Assignment Group

[Paula Alejandra]

Hi,

I need to create a report to exhibit some statistics related to TWO SIMULTANEOUS conditions:

State = On Hold

Assignment Group = XYZ

Incident Metric table as is won't help me, since the Definition Values I need are separated from each other. I am starting to thinking that a NEW Definition [Scripted] my be the solution but wouldn't want to go that waiy if there's something already in place that I am not seeing / not aware of.

Any ideas or suggestions on how to approach this?

Thanks in advance,

[Bert_c1]

hi @Paula Alejandra ,

you can use Performance Analytics for this.

update: I was able to create a Metric Definition for 'On Hold' duration for a specific group. If you want to use this as doing so in PA will require more effort.  See screen shot:

Complete script:

// variables available
// current: GlideRecord -  target incident
// definition: GlideRecord -  (this row)
var s = current.incident_state;
var g = current.assignment_group.toString();
var grp = '8a5055c9c61122780043563ef53438e3';  // Hardware, the group this applies to
//gs.info("groupOnHoldDuration: s = " + s + ", g = " + g);

if ((g != '') && (g == grp)) {
//	gs.info("groupOnHoldDuration: processing: " + current.number);
	// Check for existing metric instance
	var mi = new MetricInstance(definition, current);
//	gs.info("groupOnHoldDuration: checking for: " + definition.name + ", current: " + current.number);
	if (mi.metricExists()) {
//		gs.info("groupOnHoldDuration: Found existing metric to close, state: " + s + ".");
		// close metric, if state is no longer 'On Hold'
		if (s != 3)
			closeMetric(definition.sys_id, current.sys_id);
	}
	else {
		// start metric, if state is now 'On Hold'
		if (s == 3)
			createMetric(mi);
	}
}

function createMetric(m_i) {
	var mir = m_i.getNewRecord();
//	mir.start = current.sys_update_on;
	mir.start = new GlideDateTime();
	mir.calculation_complete = false;
	mir.value = 'On Hold';
//	gs.info("groupOnHoldDuration: creating metric to start: " + mir.start);
	mir.insert();
}

function closeMetric(def_sys_id, inc_sys_id) {
//	var mir = m_i.metricExists(); just returns T/F, so query for existing record
	var gmr = new GlideRecord('metric_instance');
    gmr.addQuery("id", inc_sys_id);
    gmr.addQuery("definition", def_sys_id);
	gmr.addQuerry('value', 'On Hold');
    gmr.query();
//	gs.info("groupOnHoldDuration: closeMetric: found: " + gmr.getRowCount() + " records for id: " + inc_sys_id);
	if (gmr.next()) {
		gmr.end = new GlideDateTime();
		gmr.duration = gs.dateDiff(gmr.start.getDisplayValue(), gmr.end.getDisplayValue());
		gmr.calculation_complete = true;
//		gs.info("groupOnHoldDuration: closeMetric: setting end: " + gmr.end);
		gmr.update();
	}
}

This works if the group doesn't change while the incident is 'On Hold'. if group is changed, similar logic in a business rule can 'close' the metric instance.

[Paula Alejandra]

Thank you so much for sharing Bert_C1! I will definitelly explore this to build upon. The metric I need to build is dynamic and given the customer's process Assignment Group nad State can change more then one time and with no restrictions of any kind. Hence I may have situations where an INC has been parked [ON HOLD] for 2 days and while stil beeing ON HOLD is re-assigned into another group. So I need to fine tune a script to capture the START and END of both conditions and make a metric out of this. Thanks again for sharing!

[Bert_c1 · Accepted solution]

You are welcome, I'm glad I was able to help.  You can use an "After" business rule defined on the incident table, with condition: "Assignment group", "changes from", "Hardware" (ABC in your example). with Advanced checked and script:

closeMetric('c06cbfdf477b6110753923dbd36d43a8', current.sys_id);

function closeMetric(def_sys_id, inc_sys_id) {
//	var mir = m_i.metricExists(); just returns T/F, so query for existing record
	var gmr = new GlideRecord('metric_instance');
    gmr.addQuery("id", inc_sys_id);
    gmr.addQuery("definition", def_sys_id);
	gmr.addQuerry('value', 'On Hold');
    gmr.query();
//	gs.info("groupOnHoldDuration: closeMetric: found: " + gmr.getRowCount() + " records for id: " + inc_sys_id);
	if (gmr.next()) {
		gmr.end = new GlideDateTime();
		gmr.duration = gs.dateDiff(gmr.start.getDisplayValue(), gmr.end.getDisplayValue());
		gmr.calculation_complete = true;
//		gs.info("groupOnHoldDuration: closeMetric: setting end: " + gmr.end);
		gmr.update();
	}
}

Good luck.

[LuisMarcel]

Hello Bert_c1, I have some questions in your suggestions of solution I would like to see if you can clarify me please.
What I understand is that you propose to initially create a metric and then generate a business rule that closes what initially the metric is starting to measure?

Thanks in advance,

Luis Castañeda