Multiple Task Types in Performance Analytics Widgets Using Database Views

How to setup a database view and use it for reports covering all tasks regardless of type while still being able to use extended fields.

The indicators that come preinstalled with Performance Analytics are great, but all of them are based on single task types. However, reporting on multiple task types in the same indicator can be very useful. In this post I will go over how to set this up, including a useful database view and breakdowns on task types.

This post will cover how to:

  • Create a database view
  • Build an indicator and indicator source based on this view
  • Create an automated breakdown based on the task types and mapped using a script
  • A quick example dashboard with widgets that report on multiple task types

I will be basing this example on the standard ITSM task types; incident, requested item, catalog task, change and problem. But the solution could easily be expanded to cover any tasks that extend the task table.

Table of Contents

Step 1: Creating a Database View

Lets start by creating a database view joining the task table to the tables that extend it. Database views are special kinds of tables that are made up of other tables. They gives us the ability to show data from multiple tables in a single list, and build reports and indicators that combine data from multiple tables. This is ideal to accomplish our combined task reporting goals.

Start by navigating to System Definition > Database Views and clicking “New”.

Fill in the form by giving the view a name and providing some a clear description. After saving the form a related list appears.

Click “New” in the related list to add the first table and fill in the following values:
Table: Task [task]
Variable prefix: task
Then click Submit.

Next we are going to join the incident table to the database view. Click “New” in the related list again and fill in the form with the following values:
Table: Incident [incident]
Variable prefix: inc
Order: 200
Where clause: task.sys_id = inc.sys_id
The where clause specifies the join condition, meaning the common field of the task and incident table. Make sure to include the variable prefixes specified. In this case both the incident and task have the same sys_id.
Then click submit.

After submitting the table to the view, we need to change the join type. Currently the join is an inner join, meaning only records that exists in both the task and incident table are included in the view. This means no task records matching the sc_req_item, change_request or problem will be included.

What we need is a left join, in which all records in the left table (task) are included in the view, and only the related records from the right table (incidents).

To do this, we need to personalize the “view tables” related list. Click the gear icon and move the “Left join” field to the right container.

Double click the value “false” on the incident row and change it to “true”. Then click the green checkmark to save.

Now is a good time to validate that the database view is working. Click the “Try it” link in the form. A list view of the database view should open. Personalize the list to only show task_number and inc_number.

Make sure that there are rows with matching values in both columns, as well as rows where the rightmost column is empty and the leftmost column value is not an incident number. This means that you have included all tasks, not just the ones with a matching incident records. If only incident-rows appear, make sure you set “left join” to true.

Also notice that fields that have the same name in both tables, like “number” are prefixed with the variable you typed when adding the table earlier.

Now we’ll go back to the database view form and add the change_request, problem, sc_task and sc_req_item tables. Repeat the step for adding the incident table, but with the following field values instead:

Change Request:

Problem:

Requested Item:

Catalog Task:


If you want to include additional task types in the database view, just repeat the steps for those tasks.

Now make sure that “left join” is set to true for all the rows except task in the view tables list:

Click the “try it” link in the form again and personalize the list view to include the “number” column for each table. Verify that there is a row with a value in it for each column (unless you have no records of that specific task type in your instance):

Now that the database view is set up, take the time to setup a better default list view. The default columns are not that useful:

Right click in any of the column headers and go to Configure > List Layout:

Make sure the “view name” is set to “default view”. Columns prefixed by “task_” should have a value for all rows, so those are good picks. Note that these column’s values are duplicated for all tasks, a column with a task-specific prefix (like inc_short_description) will only have a value for incident records.

Be aware that task_state might not completely match the respective extended task record state. For example, when an incident is in state “In Progress”, the corresponding task will be in the state “Work in Progress”.

Finish by clicking “save”.

Now would be a good time to impersonate a user that should be able to see the database view to verify that they have access and that the default view that you just specified looks good.

Step 2: Create an Indicator Source

Lets move on to creating a new indicator source based on the database view.

Navigate to Performance Analytics > Sources > Indicator Sources and click “New”.

Fill in the form and select the database view in the “Facts table” drop down, just like you would with any regular table.

In this example I have created an indicator source for open records, by basing all the conditions on the fields prefixed by task_, as this field will apply to all extended table records included in the view. I have also included conditions to look only for tasks of those types we included in the view.

Note that this condition is based on the closed_at field. For incidents there is a resolved_at field. If we wanted to use this field instead specifically for incidents, we would use the “New criteria” button to add an additional condition set, like this:

Remember to set the “View Table” to task. This is the table from which records will be collected, and if we want snapshots to include all task types this has to be set to task.

The indicator source in now complete, so lets create some indicators. First we will create a simple “Number of open tasks” indicator, based on a count of the indicator source.

Next let’s make an indicator to sum the age of all open tasks. For this we will need a script. Navigate to Performance Analytics > Automation > Scripts. Find and open the “Incident.Age.Days” script.

Change the “Name” to “Tasks.Age.Days”.
Change the “Facts table” to the database view.
Add “task_opened_at” to the fields list.
In the script, change “current.opened_at” to “current.task_opened_at”.

Now right click in the form header and select “Insert”. This will save our edits as a new script, rather than overwriting the incident script.

We can now move on the creating the indicator to sum the age of open tasks. Create the indicator and set the unit to “Days”, the aggregate to “Sum” and the script to the one just created.

Finally lets create a formula indicator to calculate the average age of open tasks.

Now that we have our indicators set up, it’s time to create jobs for data collection. Navigate to Performance Analytics > Data Collector > Jobs.

First we’ll set up a historic job to collect 3 months of data:

Add the indicators using the “Indicators” related list in the job form:

Then create a daily collection job, and add the indicators to the job using the related list:

Step 3: Create task type breakdowns

We want to be able to see the total number of open tasks, but also view our indicator broken down by task type. To do this we need an automatic breakdown based on the “task type” field in the database view.

While the task table contains a field called “Task type”, we cannot create a breakdown on this field. Breakdowns can only be created on field containing a sys_id that can be matched to the breakdown element, and this field only contains a string value. More specifically the field is of type “System Class Name”, which is a special kind of field.

There are two ways to create breakdowns based on task type. The first one only requires us to join an additional table to our database view while the second one requires scripting.

3.1 – The simple way

By joining a new table to our database view, we will be able to include the actual sys_id of the task type as a column, thus fulfilling the requirement for being able to map a breakdown to the table.

Go back to the database view and click “New” in the View Tables related list. Fill in the form like the example below, setting the variable prefix to “obj” and the where clause to “task.sys_class_name = obj.name”. This will add the “sys_db_objects” table to the view, which is a system table containing a record for all tables in ServiceNow. There are records in this table that have names matching those found in the Task Type field in the task table.

Submit the form, and remember to set left join to true in the list afterwards.

When adding this table we should perform an extra step compared to the previous tables. The sys_db_object table contains a lot of field, and we only want one of them. It is therefore a good idea to exclude all but the field we need.

Open the form for the join from the view table-related list again. In the bottom of the form there is another related list called “View Fields”. This lists all of the fields from the joined table that should be available in our database view. If this list is empty, as it is in all our other joined tables, all fields will be available. But in this case, we want to specify that we only want the sys_id field.

Click “New” and a new form opens. Select “Sys ID” in the fields dropdown.


After the form is submitted, the view fields list will look like this:

Now we need to create our breakdown and breakdown source in performance analytics. These will be based on the sys_db_object table as that’s where the different task types are stored.

Navigate to Performance Analytics > Sources > Breakdown Sources and click “New”. Fill in the form like this:

Make sure to filter based on the name field to only return the task types that are used, the table contains over 4000 records and we only need five of them.

Now we need to create an automated breakdown. Navigate to Performance Analytics > Breakdowns > Automated Breakdowns and click “New”. Fill in the form like this:

Finally we need to map our automated breakdown to our database view, in order to tell Performance Analytics where it should look for records matching each breakdown element. Click on “new” in the Breakdown Mappings related list on the automated breakdown form, In the view that opens, specify the database view. Click the “Field” drop down and click the field called “obj_sys_id”.

Then click Submit. The mapping is now complete and we can add the breakdown to our indicators.

Now go to the “Indicators” related list and click “Edit”:

Add all three indicators we created to right pane and click save:

3.2 – The more complicated way

The other way to create a breakdown by task type on the database view is by using a scripted breakdown. This means that there will be a script that runs to handle the logic of matching each record to a breakdown element. Since we built our indicators based on a database view and were able to include the needed sys_id of each task type, there really isn’t a reason to script the breakdown.

But there are other cases when a scripted breakdown might make sense:

  • You aren’t reporting on a database view, but directly on the task table, in which case you wont have the sys_id of the task type from sys_db_object available.
  • You don’t want to include fields from a system table in your database view.
  • The database view is already defined and in use, and you can’t change it by adding tables.

In order to map the breakdown using a script we still need to create the breakdown source and automated breakdown in the same way as specified above. Create the breakdown source:

And the automated breakdown:

Next we need to create a mapping script. Navigate to Performance Analytics >Automation > Scripts and click “New”.

Create the following script:

What this script does is to query the sys_db_object table for a record with a name that matches the value in the field “task_sys_class_name” in the database view. If a record is found, it’s sys_id is assigned to the “type” variable and that variable is returned to the data collection. If no match is found, the returned value will be empty.

(function getTaskType() {  
	var type ='';
	var gr = new GlideRecord('sys_db_object');
    if(gr.get('name', current.task_sys_class_name))
		type = gr.getValue('sys_id');
	return type;
})();

This script will execute once for every record returned by the indicator source when the data collection job runs. At run-time, the “current” variable will be replaced by the record that is currently being processed.

Make sure to include the “task_sys_class_name” field in the field list, or the script will not work.

After saving the script, go back to the automated breakdown. In the “Breakdown mappings” related list, click “New”.

Set the facts table to the database view and check the “Scripted” checkbox. Specify the mapping script we just created in the script field. Then click submit.

Now go to the “Indicators” related list and click “Edit”:

Add all three indicators we created to right pane and click save:

Step 4: Running the collection jobs

We are now ready to run the data collection job. Navigate to Performance Analytics >Data Collector > Jobs, remove any filters applied to the list and open the “Tasks Combined Historic Collection”. Then click “Execute now” in the header:

Wait for the job to complete and check the “Job Logs” related list to make sure it completed without any errors.

Open the analytics hub for the indicator “Number of open tasks” and verify that there is data for the breakdowns:

Now we have indicator based multiple task types, with the ability to see breakdown data for each type. The task type breakdown can even be applied to a dashboard as a breakdown source, letting us filter widgets to specific task types.

Using the same indicator source, we can create additional useful indicators as well as combining the task type breakdown with other breakdowns like assignment group or business service.

Of course, this could also be accomplished by using the task table itself as a source table, without joining the extended tables like incident and problem to it. However, by joining the tables we also gain access to those field which are unique to the extended table. For instance, the incident category or the requested items item-reference. So we have all the possibilities of reporting directly of each extended table, but we can report on all tasks if we need to.

In addition, the creation of a task type breakdown gives us the ability to look at a large set of data, and drill down to different task types as well as comparing KPI values by task type. You can simply fit more information in the same dashboard by having the possibility to filter by task type.

The database view is also great to use as a task list, as it provides an agent with access to the fields needed to handle specific task types, without having to switch between task specific lists.

Leave a Reply

Your email address will not be published.