Adding SLA Fields to Tasks to Simplify Reporting and Daily Operations

By adding SLA fields to our tasks, we can make reporting and daily work in ServiceNow easier. This post will show you how to do this.

A task can have multiple SLA records. Because of this, these are stored in a separate table called task_sla. The task_sla table contains a reference field to the task table. This means that if you want to work with SLAs, you have two options. We can report on the task_sla table, but that won’t have all the fields of the extended task. Or we can create a database view joining the different tasks to the SLA record. But then we will probably end up with multiple rows per record, which can be confusing to a user. So neither of these options are perfect.

In this post, I will provide a potential solution to this. I will show you how to add some fields from the SLA record to the task table. I will also provide you with scripts to make sure these fields are always updated. Having the fields in the task will make reporting easier and simplify spotlight criteria. It will also help users prioritize tasks when working from a list.

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 and 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. After that 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.

Table of Contents

Adding the Required Fields

So we will add the following fields to the [task] table:

Has Breached SLA (u_has_breached_sla) – A boolean field with a value of true if any SLA record related to the task has breached.

Time Left Active SLA (u_time_left_active_sla) – A duration field showing how much time is currently left of the active SLA record with the least time left.

Elapsed Percentage Active SLA (u_sla_elapsed_percentage) – A percent complete field showing how much of the active SLA with the most elapsed percentage has currently elapsed.
When creating the field, open the form in advanced view and paste the following into the “Attributes” field. This will set the color of the duration-bar based on the value of the field:

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

Most Elapsed Percentage SLA (u_most_elapsed_percentage_sla) – A percent complete field that will contain the elapsed percentage of the SLA record that has the most elapsed percentage.
When creating the field, open the form in advanced view and paste the following into the “Attributes” field. This will set the color of the duration-bar based on the value of the field:

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

And finally we’ll add the following field to the [sla_contract] table:

Exclude From Task Fields (u_exclude) – A boolean field to enable the exclusion of specific SLA definitions from updating the task fields.

Determining the Logic of Our Fields

Now that we have out fields setup, it’s time to plan how and when their values are set and updated. The logical trigger to update the fields 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:

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();
}

Lets go over what each part of the script does:

First the variables are declared. The first variables is set to the task whose SLAs are being updated. The rest of the variables are set to contain empty values which will be changed in the script when real values are found. However, if no values are found they will remain empty, which is necessary to overwrite old field values in case an SLA record becomes inactive or cancelled after having been used previously to update the task fields:

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

Next we have a GlideRecord query to get the fields from the active SLA records, if such a record exists. The query looks for all SLA records related to the task, which are active, not an instance of a definition that is excluded from updating the task and not cancelled. The return is ordered by the breach time (planned_end_time) in a ascending order, meaning the highest and closest to breaching, first. Only the first record is returned thanks to the .setLimit(1) method. If a record is found, its values for elapsed percentage and time left is used to set the variables.

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;
}

Next we need to get the maximum elapsed percentage of any non-cancelled, non excluded SLA record related to the task. We get this value by using a GlideAggregate with a MAX-aggregation. A GLideAggregate is more efficient than a GlideRecord, so it’s preferable to use when we need the top value. A downside of the GlideAggregate though is that we cannot use it to access individual records fields, just the aggregated value itself. That’s why it wasn’t used to get the time left and elapsed percentage of the active SLA records.

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');
}	

The last variable we need to set is “hasBreached”. If any non-cancelled, non-excluded SLA records related to the task has breached we need to set this to true. Once again we use a GlideAggregate for efficiency, this time counting the number of breaches SLA records. If this count is more than 0, we set the variable to true.

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;
}	

Now that we have all our variables set, it’s time to update the parent task. Since we have the sys_id of the task, we can use the shorthand get-method to get the record object. The setValue() method is used on all our fields to set them to the variable values. For the “time left active SLA” field, we need to use the setDateNumericValue() method since this is a duration field. But if the the activeTimeLeft variable is null, we need to use the setValue() method to set the value to a blank one since the setDateNumericValue() cannot set a null value.

The three final rows are important. The autoSysFields(false) method makes sure our update does not trigger new values in the sys_updated_on, sys_updated_by and sys_mod_count of the task. This is important since our update shouldn’t count as a “real” update of the task. Nothing has really been changed in the task in terms of work or a process, it’s just the SLA counting upwards.

The setWorkflow(false) method ensures that our update does not trigger any flows, email notifications or other business rules. Again, our update should not have the same effects as a regular manual record update. We wouldn’t want the end user getting en email about their task being updated every time the SLA percentage increases or something like that.

Finally the update() method commits the update and writes the values to the task.

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);

On the “Update Task SLA Fields On Display”, we’ll also add the following conditions:

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:

  1. 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“.
  2. 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“.
  3. 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.
  4. 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

Click the download button below to download an update set containing the script include, business rules and added fields.

Leave a Reply

Your email address will not be published.