Using Spotlight to Prioritize Tasks, Including SLA-based Criteria

Learn how to set up and use Spotlight in ServiceNow, and build criteria based on how much SLA has elapsed in the record.

Spotlight is a plugin used to evaluate records based on weighted criteria. Each criteria then adds a score to a records, and the sum of these scores can be displayed as a “spotlight”. This allows us to get a better understanding of which records to prioritize than if we were just looking at the SLA or priority field.

When prioritization is based on a single dimension, we run the risk of certain tasks constantly falling behind in the queue. In the end, this might lead to our backlog growing, users losing confidence in us and us not hitting our key performance indicators. Spotlight lets us prioritize our tasks in a smarter and more efficient way.

Colored circles representing Spotlight criteria

In order to use Spotlight, we first need to make sure the plugin, called “com.snc.pa.spotlight” is activated.

Spotlight Groups

The basis for spotlights is the spotlight group that can be found by navigating to Spotlight > Spotlight Groups.

You will find that there is a group provided for incident out-of-the box. We’ll use this as a starting point. When we open the incident spotlight group we see this form:

The form has the following fields:

Name: The name of the spotlight group

Threshold: This is the sum of points that a record has to have in order for a “spotlight” to be created for that record. Each criteria gives a certain amount of points if a record matches its conditions. These points are then summed up and compared to the spotlight groups threshold. If the records total score is equal to, or exceeds, the threshold a spotlight records is created in the table “spotlight”.

Evaluate scores from: This allows us to choose platform data or performance analytics snapshot. If we choose platform data, that means that each time the spotlight collection job runs, it will get records directly from the servicenow table (the table used in the indicator source for the main indicator). Whereas if we use PA snapshot data, the records in the latest snapshot of the main indicator will be used. If a record is not in that snapshot, it will not be evaluated.
Note that Spotlights can never be truly “live data”. Even if we use platform data, the spotlight scores are still only updated when the spotlight evaluation job runs. This makes sense because of performance concerns. If the data was complete “live” that would mean that each time we looked at a list of spotlight records, each record would be evaluated against multiple criteria. That could mean thousands of database queries just to show a list for a single user.

Main indicator: This is the field where we select the indicator used for the spotlight group. it is the most important field as it will decide what data is available to the spotlight group. As the spotlight job runs to evaluate the criteria and collect scores for record, it will be limited to only look at records that are either included in this main indicators latest snapshot (if we use snapshot data) or records that matches the indicators additional conditions as well as the main indicator’s indicator source conditions.
Furthermore, we can later add conditions based on other indicators but they all have to be based on the same table as the main indicator.

There are 3 conditions for what indicators can be selected as the main indicator:

  • Only automated indicators are allowed.
  • The property “collect records” must be set to true in the indicator.
  • It must be based on a table, it cannot be based on a database view.

That last point is important, and we’ll get back to it later in this post.

Breakdown and element: Here we are able to select if we want to further limit the scope of the spotlioght group by looking at the subset of records that match a specific breakdown element of the main indicator, for example just incidents with a priority of 1.

Next we have the scheduler for the spotlight collection job for this spotlight group. It can be set to run at the standard intervals: daily, weekly, monthly, periodically, once or on demand. Depending on what we chose, we can specify a certain time, date or weekday.
If we chose to periodically run the job, we can have it run as often as we like. Even once a second. But that probably wont work very well. The more frequent we schedule the job, the closer to real-time data we will have in our spotlights.

At the bottom of the form, there is a related list containing all the spotlight criteria for the group. There are two kinds of criteria:

  • Query-based, containing a encoded query built using a condition-builder in the criteria form.
  • Indicator based, using the filters and table of an indicator and its indicator source.

For an indicator-based criteria, a breakdown element can optionally be specified. This would mean the criteria checks if the record is included in that breakdown snapshot of the indicator.
So if we wanted to set up a criteria for priority 1 incidents, we could do it in three ways:

  • Set up an indicator called “Open Priority 1 Incidents” with a filter to only get priority 1 incidents, and use this as a criteria.
  • Base a criteria on the indicator “Open Incidents” and specify its breakdown “Priority” and element “Priority 1”.
  • Base the criteria on a query, build with an condition builder where we specify “Priority=1”.

There is a difference on how these criteria would be evaluated, depending on if we set up the spotlight group to use platform data or snapshot data. Note that query-based criteria will always evaluate against platform data, even if the group is set to use snapshot data.
See the flowchart below for more information.

The Spotlight form

When opening a single spotlight from the related list in the spotlight group, we can see its aggregated score, which record it applies to as well as all the criteria that it matched at the time of the latest collection. So by viewing this form we will be able to see why, in this example INC0000031, received a score of 1200:

Flow and logic of spotlight criteria evaluation and score collection

Spotlight and scripted breakdowns and indicators

If criteria are based on indicators, and a scripted breakdown element is specified, the indicator group cannot use platform data. Since the records matching the breakdown element is only collected by the scripted breakdown mapping when the Performance Analytics data collection job runs, there is no way for Spotlight to get real-time data. If you try to set a Spotlight group that uses scripted breakdown-based criteria to use platform data, an error message will appear.

Since bucket-based indicator breakdowns are based on scripts, this means they cannot be used in spotlight groups using platform data.

An automated indicator using a scripted aggregation also cannot be used as a criteria in a platform data spotlight group. And if the main indicator is script-based, the group has to be set to use snapshot data.

Matching collection frequency

If a spotlight group is set to use snapshot data, there is no point in setting the spotlight job to run more frequent than the PA data collection job. Since no new PA data is created until the PA job runs, the spotlight job will only iterate over the same data over and over again.

In fact, job synchronization matters. Ideally, the spotlight job should run as soon as the PA job finishes, and only then. If there is a gap, there is a risk that the data has been modified in the time between job runs, leading to a potential mismatch in data.

All the indicators used in the spotlight group should also be collected at the same time, including both the main indicator and the criteria indicators.

Also note that all indicators used in the spotlight group have to have the same data collection frequency.

Limitations in Spotlight and how to work around them

Spotlight has some pretty bad limitations that makes it hard to use as one would like to. First of all, query based criteria do not support dot-walking, meaning you can only use query criteria on the fields on the main indicators table. This means you can’t query for things like the user’s job title, or the details of the configuration item. The solution lies in using indicator-based criteria with selected breakdowns. However, this requires you to set up breakdowns for each criteria you want to use, which can be cumbersome.

Another big limitation is that you cannot base the main indicator on a database view, it (meaning its indicator source) has to be based on a actual table. This can be a major problem if you want to include criteria based on the status of the record’s SLA, which you probably want, since they are stored in a different table than the main record.

It used to be that you could still create a spotlight group on a main indicator using a database view, but then the spotlights would for some reason be created without a working reference to the record, making them pretty useless. You would know that a record passed the threshold, but you wouldn’t know which one.
In later versions however, ServiceNow added a reference qualifier blocking you from selecting these indicators in the spotlight form.

How to set up SLA-based criteria

There are multiple ways to work around the lack of support for database views in spotlight in order to get information about a record’s SLA, or any other information stored in related lists for that matter.

1. Base all indicators on the task_sla table

The simplest way is to base your main indicator and all criteria indicators on the task_sla table. Since this table has a reference field to the task table, you would still be able to use indicator-breakdown-based criteria to set up conditions on the task. You would however not be able to query the task using query-based indicators, since these to not support following reference fields (from the task_sla to the task).

You also wont be able to create criteria on fields that only exist in extending tables, since there is no reference from task_sla or task to for example incident. Most fields, like assignment information and priority exist on the task table. But fields like incident category or the item of a requested-item record do not.

Still though, you can get platform data and build criteria on SLA progress, so it is a viable solution if all your criteria can be based on the task fields.

2. Use scripted breakdowns

You can use scripted breakdowns on an indicator to query for information about the related SLA:s, and match those values to bucket groups. The bucket breakdowns can then be used in criteria.

For example, you can set up at bucket group for ranges on elapsed percentages in SLA:s you want criteria based on:

A breakdown mapping script like the one below can then be used to query the task_sla table for each record the PA data collector job finds (the current-variable in the script), and return the value of percentage elapsed for the SLA record. This would then be matched to one of the buckets in the bucket group:

This script does not take into account that there can be multiple SLA records for a task

Indicator criterias can then be set up based on the buckets:

As stated previously in this post, this will unfortunately mean you can’t use platform data, only snapshots. If you are using spotlight as a tool for your fulfillers to use as a work queue, using snapshot data updated once a day would not be useful. Of course you could run the PA collector job multiple times a day, collecting with a relative end date of 0 days. Then you could run the Spotlight job right after it. But that can be bad for performance.

The third, and arguable best way, is to use the related list conditions of an indicator source. This lets us filter records not just on the fields of a table and its references, but also on records in related lists. Since SLA records are included in the related list of most task types, this works out well.

You see, while there is a requirement that all indicators in a spotlight group have to be based on the same table, they do not have to use the same indicator source. So we can set up multiple indicator sources such as:

  • Incidents.Open.Breached.SLA
  • Incidents.Open.90%.SLA.Elapsed

It could look something like this:

Then set up an automated indicator based on this source and add it to the spotlight group as a criteria:

The only downside of this is that it takes some work. I also leads to a lot of indicator sources being created for no other use than Spotlight. But it does allow us to base criteria both on incident fields and SLA progress. Most importantly – it lets us use platform data.

Tables used by Spotlight

Spotlight uses these main tables:

Table NameData stored
spotlightContains the aggregated scores for each record, with a reference to the record in the form of a Doucment ID-field as well as a reference to the spotlight group. Each record in the table has a related list with all the spotlight audits.
spotlight_auditContains an entry for each record and criteria that has resulted in a match, with a reference to the spotlight, the spotlight group and the spotlight criteria.
spotlight_groupsContains information about all the spotlight groups, including all the fields in the spotlight group form and a reference to the main indicator.
spotlight_criteriaContains all the spotlight criteria, including references to criteria indicators and the spotlight group.

spotlight_job_log
Contains the logs for the spotlight job, including their state and run time,
spotlight_job_log_rowContains the log rows for the spotlight jobs, showing with criterias werer evaluated and the results of those evaluations.

Database views

The spotlights are most useful when joined to information about the records they belong to. This lets users see the entire record and the aggregated score as a column. The user can then use that column to sorts the list.

A database view for incident spotlights is provided out-of the box. We can also create a database view joining the task table to the spotlight table. That database view would let users let users view and sort all their tasks based on the spotlight score. Each task can have its own spotlight group, and we can join them all in the database view. This means we have s single source for all spotlights, regardless of task type.

The database view provided

Dashboard and Performance Analytics

The plugin for spotlight also comes with a pre-made dashboard. It’s called “Incident” and belongs to the “Spotlight” dashboard group. While pretty basic, it can serve as inspiration for other more detailed dashboards.

Of course, the spotlight tables can also be used with Performance Analytics to report on averages and trends. Potential indicators include:

  • Number of spotlight records
  • Average score of spotlights
  • Number of spotlights wit, broken down by spotlight criteria
  • Average spotlight score per assignment group / assigned to.
  • Average duration between spotlight being created and records being closed
  • Number of spotlights with a bucket breakdown on score intervals
  • Average duration between record being created created and spotlight being created
  • Spotlight with a breakdown on business service / configuration item

So you should definitely try out spotlight now that you know how to get around some of the issues with the plugin. Be aware that setting the right criteria and the right weight of those criteria is a process of trial-and-error.

Leave a Reply

Your email address will not be published.