Approaches to easy reporting on assignment group history?

[brian-rowland]

My team gets regular reporting requests from service desk leads for historical reporting on incident assignment.   A typical request is "I want to see all incidents opened last month that were assigned to my groups at some time." I haven't seen an elegant way to create such a report out of the box, and we're to the point where using incident-to-metric database views is no longer viable in terms of ease of use, scalability, and performance.

What I've come up with is a new task field and business rule that appends to a glide list for every group assignment.   Then answering the above question in an incident report is as easy as adding an   "Assignment Group History contains ..." to the report filter.

How have other folks handled offering historic reporting on incident assignment?  

Thanks,

-Brian

[MG Casey]

I've been thinking about adding "Assigned To History" and "Assignment Group History" list fields to the Task table.

Every time the assignment changes, it adds that new "Assigned To" or "assignment group" to their respective list field on the Task table (if not already present).

This would then allow you to report (using the Incident table) to find incidents that have, at one point, been assigned to a specific group.

[taylor21]

Hi there, were you successful in implementing this? If so, how did you do it? I have the same requirement from a business unit and I'm trying to figure out the best way to fulfill it. 

[MG Casey]

Yep I was! I added two fields to the Task table:

• Summary - Assigned to (u_summary_assigned_to)
• Summary - Assignment group (u_summary_assignment_group)

Then I added this business rule:

Condition:

current.assigned_to.changes() || current.assignment_group.changes()

Script:

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

if (current.assigned_to.changes() && (current.assigned_to != "")) {

var currentassignedto = current.assigned_to.toString();
var currentsummaryassignedto = current.u_summary_assigned_to.toString();

if (currentsummaryassignedto == "") {
current.u_summary_assigned_to = currentassignedto;

} else if (currentsummaryassignedto.indexOf(currentassignedto) == -1) {
current.u_summary_assigned_to += "," + currentassignedto;
}
}

if (current.assignment_group.changes() && (current.assignment_group != "")) {

var currentassignmentgroup = current.assignment_group.toString();
var currentsummaryassignmentgroup = current.u_summary_assignment_group.toString();

if (currentsummaryassignmentgroup == "") {
current.u_summary_assignment_group = currentassignmentgroup;

} else if (currentsummaryassignmentgroup.indexOf(currentassignmentgroup) == -1) {
current.u_summary_assignment_group += "," + currentassignmentgroup;
}
}

})(current, previous);

I also ran a job to populate those fields historically for all current tasks. Warning - this script might take days to run depending on how many tasks you have in your instance.

var addToSummaryString = function(existingstring, newpiece) {

if (newpiece != "") {
if (existingstring == "") {
existingstring = newpiece;

} else if (existingstring.indexOf(newpiece) == -1) {
existingstring += "," + newpiece;

}
}

return existingstring;
};

var previousvaluescount = 0;

var findoldvalues = new GlideRecord("sys_audit");
findoldvalues.addEncodedQuery("fieldname=assigned_to^ORfieldname=assignment_group");
findoldvalues.query();

while (findoldvalues.next()) {

var findtask = new GlideRecord("task");
if (findtask.get(findoldvalues.documentkey)) {

var summaryfield = "u_summary_assigned_to";
if (findoldvalues.fieldname == "assignment_group") {
summaryfield = "u_summary_assignment_group";
}

findtask[summaryfield] = addToSummaryString(findtask[summaryfield].toString(), findoldvalues.oldvalue.toString());

findtask.setWorkflow(false);
findtask.autoSysFields(false);
findtask.update();

}

previousvaluescount++;
if (previousvaluescount % 5000 === 0) {
gs.log("Populate Historical Summary of Assignments V3 - loop through audit records, current count: " + previousvaluescount.toString());
}
}

gs.log("Populate Historical Summary of Assignments V3 - PART 1 COMPLETE!!!");

var findtasks = new GlideRecord("task");
findtasks.addEncodedQuery("assigned_toISNOTEMPTY^ORassignment_groupISNOTEMPTY^ORsys_mod_count>0");
findtasks.query();

var recordsupdated = 0;

while (findtasks.next()) {

findtasks.u_summary_assigned_to = addToSummaryString(findtasks.u_summary_assigned_to.toString(), findtasks.assigned_to.toString());
findtasks.u_summary_assignment_group = addToSummaryString(findtasks.u_summary_assignment_group.toString(), findtasks.assignment_group.toString());

findtasks.setWorkflow(false);
findtasks.autoSysFields(false);
findtasks.update();
recordsupdated++;

if (recordsupdated % 5000 === 0) {
gs.log("Populate Historical Summary of Assignments V3 - adding current field values: " + recordsupdated.toString() + " records updated.");
}
}

gs.log("Populate Historical Summary of Assignments V3 - PART 2 COMPLETE!!!");

[taylor21]

Beautiful, thank you so much!