Note: This article is concerned with ServiceNow database views, which are, in baseline implementations, only available for configuration by ServiceNow administrators.
ServiceNow is built to be dependent on database tables to capture and store data. ServiceNow reporting helps make sense of this data. Reporting on existing tables, without having to delve into some advanced options, provides a great deal of flexibility. For example, when reporting on the ServiceNow Incident table, you can easily create a filter that will show all open incidents related to change requests that are also open. This is possible because the fields that exist on tables referenced by the source table can be used in filter conditions. The ServiceNow database schema map displays the details of tables and their relationships visually, allowing administrators to view and effortlessly access different parts of the database schema.
What is a database view?
There are some scenarios where it may be inefficient or impossible to find the insight you’re looking for by reporting from a single table. For example, there may be scenarios where several tables contribute to the scenario you’re analyzing or the relationships between associated data can’t be quantified using the referencing capabilities of baseline reporting on basic tables. For these scenarios, it may be helpful to utilize a Database View. A ServiceNow Database View is a pseudo-table representing a join between several contributing tables. The rows consist of combinations of contributing source records that share values according to pre-indicated mappings.
As an example, we will look at the baseline Incident Metric Database View. This View in Database joins the Metric Definition, Metric Instance, and Incident tables.
To access this database view, which should be available in your instance, visit the Database Views module found within the System Definition application menu. From the list view, search for the “incident_metric” record. The form for this record appears above.
The Database View form itself is relatively sparse. You will only need to define the name of the database view, its description, and the label/pluralization used to identify this view in other places within the platform. The essential data here is defined in the records found in the View Tables related list.
- The ServiceNow Table field defines the contributing table that should be part of the join.
- The Variable prefix field defines the prefix that will be prepended to field names in the resulting pseudo-table. This will identify each field’s source table. For example, the incident table’s “assignment_group” field becomes “inc_assignment_group”. This prefixing is most often useful to differentiate between fields that appear in multiple contributing tables, but which have identical labels. It’s also key to identify field mappings for the “where clause,” as explained below.
- The Where clause defines how the tables should be joined, or implicitly filtered. For this Database View, Metric Definition is filtered to include records where the defined table is “incident,” Metric Instance is joined with Metric Definition where the instance’s “Definition” field is the corresponding record, and Incident is joined where the instance matches the incident record. Note the use of the variable prefix values.
As a result of this definition, you will have access to report on things like “High Priority Incidents ‘In Progress’ for 3+ Days,” “Incidents Resolved on First Call (by Category),” or other such scenarios that would be difficult or impossible to report on without utilizing the functionality of Database Views.