I've seen a few articles around and the question pops up fairly regularly in some form or another.
"Who was the first assignee of this ticket"
"What group got sent this ticket first"
The answers are usually some form of "Create a custom field, set it with a BR the first time the value changes"
And while this works technically, I'm not a big fan of adding custom fields that are only used for very niche circumstances or by very few people. Usually it's just a reporting requirement for 1 person, and that doesn't sit well with me.
You can answer any question about "What happened to this ticket in the past" by viewing the Audit history of a record. However this is only available to admins.
There is an API called the HistoryWalker and Chuck has done a blog about it here: https://developer.servicenow.com/blog.do?p=/post/historywalker/ which allows you to programmatically step through the audit history of the record, and interact with it as if it was at that point in history, retrieving values and so on.
Here is a practical application
New metric
Go to Metrics > Definitions
And create a new metric on your table of choice. You can repeat this for as many fields as you want.
Name: First Assignee
Type: Script Calculation
Field: Assigned to
Script:
// variables available
// current: GlideRecord - target incident
// definition: GlideRecord - (this row)
if (!gs.nil(current.assigned_to)) {
var hw = new sn_hw.HistoryWalker(current.getTableName(), current.getUniqueValue());
hw.walkTo(0); // view the record as it was at update 0
var walked = hw.getWalkedRecord();
while (gs.nil(walked.getValue("assigned_to"))) { //ignore while it was empty
hw.walkForward(); // increments the update count by one
walked = hw.getWalkedRecord();
}
var id = walked.getValue('assigned_to');
var value = walked.getDisplayValue("assigned_to");
createMetric(id, value);
}
function createMetric(id, value) {
var mi = new MetricInstance(definition, current);
if (mi.metricExists())
return; // we only need 1 metric per record
var gr = mi.getNewRecord();
gr.field_value = id;
gr.value = value;
gr.calculation_complete = true;
gr.insert();
}
Then your report is about as simple as it gets.
Fix Script for adding metrics to historical records
The script is almost identical, you just have to define current and definition first
var definition = new GlideRecord("metric_definition");
definition.get("119d98fb1b659dd0ac8331ddcc4bcb31"); // replace with the sys_id of your metric_definition
var current = new GlideRecord("incident"); //or whichever table you are looking at
current.addQuery("active", false);
current.addNotNullQuery("assigned_to");
// add additional queries as needed
current.query();
while (current.next()) {
if (!gs.nil(current.assigned_to)) {
var hw = new sn_hw.HistoryWalker(current.getTableName(), current.getUniqueValue());
hw.walkTo(0);
var walked = hw.getWalkedRecord();
while (gs.nil(walked.getValue("assigned_to"))) { //ignore while it was empty
hw.walkForward();
walked = hw.getWalkedRecord();
}
var id = walked.getValue('assigned_to');
var value = walked.getDisplayValue("assigned_to");
createMetric(id, value);
}
}
function createMetric(id, value) {
var mi = new MetricInstance(definition, current);
if (mi.metricExists())
return;
var gr = mi.getNewRecord();
gr.field_value = id;
gr.value = value;
gr.calculation_complete = true;
gr.insert()
}
