Since a task can have multiple SLA records, these are stored in a separate table called task_sla, which contains a reference field to the task table.

This means that if you want to work with SLAs, you have two options. Either use the task_sla table, which does not have all the information about the task, or work with a database view. The database view will have multiple rows for each task when that task has multiple SLA records related to it. Neither of these options are perfect as they can cause confusion for the user, or provide insufficient information.

One solution to this is to add SLA fields to the task table, and update them to reflect the progress of the related SLA records. This can be useful to make reporting easier, simplify spotlight criteria based on SLAs as well as making information more available to users working with lists of tasks.

Out-of-the box, two fields related to SLA:s existing in the ServiceNow task table, “Made SLA” and “SLA Due”. Both of these field are part of the old SLA engine, in which a task could only have one SLA record related to it. Even though the old SLA engine is no longer in use, these fields still exist but in an unused state. We wont be working with these in our solution, as we aim to not modify any out-of-the box functionality.

We can however make our own version of these fields an populate them using business rules. The first step is to determine what information we want to include in the task, and what fields we need for that. Then we can determine how these fields are to be updated.

Let’s say we want our fields to contain updated information about the active SLA to help prioritize tasks, as well as information about the maximum elapsed SLA percentage to support reporting. If one of the SLAs has breached, we want to show that. We probably also want the ability to exclude certain SLA definitions from affecting the field.

Adding the Required Fields

First we need to add fields to our tables. The following fields will be added to our [task] table:

Name: u_time_left_active_sla

Label: Time Left Active SLA

Field Type: Duration

Name: u_sla_elapsed_percentage

Label: Elapsed Percentage Active SLA

Field Type: Percent Complete

Name: u_most_elapsed_percentage_sla

Label: Most Elapsed Percentage SLA

Field Type: Percent Complete

Name: u_has_breached_sla

Label: Has Breached SLA

Field Type: True/False

For the percent complete fields, we also need to open the dictionary form in advance view and add the following attributes to color the field green/orange/red depending on the percentage elapsed:

target_threshold_colors=0:#71e279;50:#fcc742;75:#fc8a3d;100:#f95050

Finally we’ll add the following field to the [sla_contract] table, to allow us to exclude specific SLA Definitions from updating our fields:

Name: u_exclude

Label: Exclude From Task Fields

Field Type: True/False

Determining the Logic of Our Fields

Now that we have out fields setup, it's time to plan how and when they are to be populated with values and updated.

The logical trigger for the values to be updated is when an SLA is inserted or updated. This will mean we have a new value to write to the fields.

We want to query the task_sla table for all the SLA records that will be the basis of our values. This includes all SLA records that are not cancelled or has a value of "true" for our added field "Exclude From Task Fields".

For the fields "Time Left Active SLA" and "Elapsed Percentage Active SLA" we want to get the SLA record with the closest breach time. Meanwhile for the "Most Elapsed Percentage SLA" and "Has Breached SLA", we want to look at any SLA record that's not cancelled, even the inactive (completed) ones.

We also have to take into account the case that a SLA record could set the values for any field, only to later be cancelled. In this case, we want to discard the values from this SLA record and get them from another SLA record.

When no active SLAs exist for the task, we want the "Time Left Active SLA" and "Elapsed Percentage Active SLA" fields to be blank, but the "Most Elapsed Percentage SLA" and "Has Breached SLA" to still have values.

Finally, if at any point a task does not have any non-cancelled SLA records related to associated to it, we want all values to be blank except "has breached", which should be false.

Taking the Mechanisms of SLA Updates Into Account

I've written a post about how SLA records are recalculated and updated. In short, there are two things that update the SLAs: scheduled jobs and and a business rule running on display of the task. There is an important difference. The on-display business rule that updates the SLA records when the parent task is displayed is running with setWorkflow(false). This means that when this business rule makes an update to the SLA record, no other business rules will trigger. So if we were to have our own business rule to update our newly added fields each time an SLA record is updated, it would not run for these SLA updates. On the other hand, the scheduled jobs do not run with setWorkflow(false), so their changes would trigger our business rule.

The solution is to create two business rules, one triggered by updates and inserts to the task_sla table. This business rule will run every time a new SLA record is created, like when a new task is opened, and each time the schedules jobs update the SLA records.

The other business rule will run on display of the task record, after the SLAs have been updated by the other on-display business rule.

Since we have two business rules doing the same job, just triggered by different things, we want to avoid repeating code. So we will put our script in a script include and call it from the business rules.

The Script Include

We will create a script include to set the values of our fields. The script include will require an input in the form of a task sys_id, get the values from the SLA records in task_sla related to this task, and then update the task.

This is the script we will be using. It uses GlideAggregate when possible in order to be more efficient, as this script will run often:

function updateTaskSLAFields(taskID) {

current_task = taskID;
var hasBreached = false;
var activeTimeLeft;
var activeElapsedPercent = "";
var maxElapsedPercent = "";

var activeSLA = new GlideRecord('task_sla');
activeSLA.addQuery('task', current_task);
activeSLA.addQuery('sla.u_exclude', 'false');   
activeSLA.addQuery('stage','!=','cancelled');
activeSLA.addQuery('active','true');
activeSLA.orderBy('planned_end_time');
activeSLA.setLimit(1);
activeSLA.query();
if (activeSLA.next()) {
    activeTimeLeft = activeSLA.business_time_left.dateNumericValue();
    activeElapsedPercent = activeSLA.business_percentage;
}


var maxSLA = new GlideAggregate('task_sla');
maxSLA.addQuery('task', current_task);
maxSLA.addQuery('sla.u_exclude', 'false');  
maxSLA.addQuery('stage','!=','cancelled');
maxSLA.setGroup(false);
maxSLA.addAggregate('MAX', 'business_percentage');
maxSLA.query();
if (maxSLA.next()) {
    maxElapsedPercent = maxSLA.getAggregate('MAX', 'business_percentage');
}       

var breachSLA = new GlideAggregate('task_sla');
breachSLA.addQuery('task', current_task);
breachSLA.addQuery('sla.u_exclude', 'false');
breachSLA.addQuery('stage','!=','cancelled');
breachSLA.addQuery('has_breached','true');
breachSLA.setGroup(false);
breachSLA.addAggregate('COUNT', 'sys_id');
breachSLA.query();
if (breachSLA.next()) {
    var breachNumber = breachSLA.getAggregate('COUNT', 'sys_id');
    if (breachNumber > 0)
        hasBreached = true;
}   

var getTask = new GlideRecord('task');
getTask.get(current_task);
getTask.u_has_breached_sla.setValue(hasBreached);
if(activeTimeLeft == null) {
    getTask.u_time_left_active_sla.setValue('');
}
else {
    getTask.u_time_left_active_sla.setDateNumericValue(activeTimeLeft);
}
getTask.setValue('u_elapsed_percentage_active_sla',activeElapsedPercent);
getTask.setValue('u_most_elapsed_percentage_sla', maxElapsedPercent);
getTask.autoSysFields(false);
getTask.setWorkflow(false);
getTask.update();
}

Business Rules

Once we have our script include we can create business rules to call the script. As mentioned before, we need two of them.

The first business rule runs after an update or insert to the task_sla table has happened:

The script on the "Advanced" tab calls the script include with the sys_id of the SLA records parent task:

(function executeRule(current, previous /*null when async*/) {
    updateTaskSLAFields(current.task.sys_id);
})(current, previous);

The second business rule runs on-display of a task. It needs to run after the "Calc SLAs on Display" BR, which has an order value of "100". So we'll set the order to 200:

On the "Advanced" tab, we call the script include with the sys_id of the current task:

(function executeRule(current, previous /*null when async*/) {
    updateTaskSLAFields(current.sys_id);
})(current, previous);

We'll also add the following conditions to the second business rule:

gs.getProperty("glide.sla.calculate_on_display") === "true" && gs.getProperty("com.snc.sla.run_old_sla_engine") !== "true" && !current.isNewRecord() && GlideStringUtil.notNil(current.getUniqueValue())