ServiceNow's Database View

Hypothetical Scenario: Let's say you wanted to create a report on the incident table that shows records that stayed in the In Progress state for more than three days and the report has to show fields from the incident table such as short description and priority. We can use a Field Value Duration Metric to track the duration an incident stayed on the In Progress state but if you try to use that as a data source for a report the fields you get are only from the metric instance table meaning you cannot add fields from the incident table like short description and priority to the report.

The way to go about this is to create a database view.

Database view

A database view is a way to join tables to create a virtual table that consists of fields from all the tables that are joined. The virtual table created can then be used in reports as a data source. There may be some limitations to a database view,

Limitations of database view

• They cannot be created on tables that participate in table rotation.
• It is not possible to edit data in the database view output.
• They cannot be added as a data preserver in clone requests.

How to create a database view?

To create a database view go to the Database Views module and click on the New button on the top right corner.

System Definition > Database Views

Name: Name of the database view.

Label: Give a label that best describes the database view. This will be used to search the database view in a report's data source picker.

Plural: Plural version of the label.

Description: What the database view is about and the tables being joined.

After entering the necessary information save the record.

Next, we will add view tables which are the tables we want to join together for our hypothetical scenario those tables are metric definition, metric instance, and incident. To create a view table scroll down on the database view record you created and click the New button.

Table: The table to be added to the database view.

Variable prefix: Variable that represents the table that is going to be used in the view table where clause. For instance mi, md, and inc for metric instance, metric definition, and incident respectively.

Where clause: The where clause is a condition used to fetch the right records from each table after the join. The syntax to add a condition in the where clause is TableVariablePrefix_FieldName.

N.B.

There must be '_' between the variable prefix and field name when writing the where clause.

The following conditions can be used to get the right records from all three tables with their corresponding fields.

• mi_definition = md_sys_id fetches all records that have the exact metric definition from the metric instance table.
• mi_id = inc_sys_id fetches all records with the same metric instance id and incident sys_id.

To check if the database view is getting the right records click on the Try It related link above the view table related list.

Make sure the database is working as per the requirement. To identify the fields coming from the different tables check the list layout of the database view virtual table from there each field will have its respective variable prefixes E.g. mi_duration.

The next step will be to create a report with the database view as a data source, a condition to filter active incidents which are in the In Progress state and a duration of more than three days.

You can edit the fields shown on the report by going to the table context menu from the report, then database view from the database view click on the Try It related link to open the table with joined tables and edit the list layout.

Now you are able to create a report that shows incident records that are in the In Progress state for more than three days with fields like Number, Short description, state, priority, duration, and more as per your requirement which you are able to achieve by creating a database view and using that as the reports data source.