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())
Result
The result is that we now have useful fields on the task table, which we can use to easily sorts and prioritize task lists, as well as report on.
In the image below, we can see that we have one incident which has breached at least one of its SLAs (INC0010042). One incident is closed and has no active SLAs, but of its SLAs one elapsed to 68% (INC0010032). One incident has an SLA of which 77% elapsed, but that is inactive and the currently active SLA has only elapsed to 1,83% (INC0010038):
Use Cases
We wanted the fields added to be useful in prioritizing tasks, but also for reporting purposes. All together, we have the following use cases and solutions using our new fields:
- A user is looking to take on a task from the queue and needs to see which one should be prioritized based on the goal of staying compliant with SLAs. For this purpose, the user can sort the list by "Time Left Active SLA".
- A service owner is looking to report on how many tasks have breached one or more SLAs. For this purpose, the service owner can group cases (open or closed) by the column "Has Breached".
- A process manager is looking to report on the average elapsed percentage of SLA:s of open tasks by state. For this purpose, the manager can average the value of "Elapsed Percentage Active SLA" of open tasks, and group them by state.
- A group manager want to see how much on average his group missed the SLA delivery in closed tasks with breached SLA:s. For this purpose, the manager can average the value of the "Most Elapsed Percentage SLA" of closed tasks.
Update Set
An update set is included as an attached file in this article. Importing it will add the fields, script include and business rules to your instance.
/Niklas Engren
If you like my post you can read more of my content, including a more in-depth explanation of this post, on my blog: http://performinganalytics.com/
