How to get the first value of a field in a report via a metric (no custom fields required)
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.
https://www.servicenow.com/community/developer-articles/how-to-get-the-first-value-of-a-field-in-a-report-via-a-metric/ta-p/2317881