Autumn is here, let’s start it with a short read about ServiceNow database views

Database views in ServiceNow are somewhat limited, but with a bit of creativity, you can still make good use of them. The two main uses of database views are: Reporting and (direct) Web Services.

This blog will try to list the options and limitations of ServiceNow database views by trying to translate SQL statements to ServiceNow Database Views. Below you will find a number of Information Requirements that you could have and the technical way to deliver this information. The goal is to show a technical concept where Database Views together with Before Query Business Rules provide a much more powerful solution that Database Views alone.

1 – Show some information from incidents

SQL: SELECT number, incident_state FROM incident; This is an obvious one. See more here: https://docs.servicenow.com/bundle/newyork-platform-administration/page/use/reporting/concept/c_DatabaseViews.html

2 – Show some information from incident and its related caller

SQL: SELECT inc.number, inc.incident_state, clr.email FROM incident AS inc INNER JOIN sys_user AS clr ON inc.caller_id = clr.sys_id WHERE inc.active=true;

Fairly straight forward: Create a Database View with the two tables incident and sys_user and select the attributes “number”, “incident_state” and “caller_id” from incident and “sys_id” and “email” from sys_user (remember: attributes that you use in the where clause of the Database View need to be in the View Fields).

3 – Show incidents that are linked to an active problem

SQL: SELECT inc.number, inc.incident_state FROM incident AS inc WHERE inc.sys_id IN (SELECT prb.parent FROM problem AS prb WHERE prb.active);

Now it gets interesting. A simple join between the incident and problem tables would create a lot of duplicates in the resulting view. For this we will introduce a “hidden” feature: Before Query Business Rule on Database Views. Through a right-mouse-click on the database view header you don’t have access to Personalize=>Business Rules, but fortunately you can still create them through the Business Rules module.

Database View:

Create a Database View on the incident (inc) table alone that shows only the attributes “sys_id” “number” and “incident_state”. You do not need to set any filter (where-clause) here.

Before Query Business Rule:

var prbQuery = current.addJoinQuery(‘problem’, ‘inc_sys_id’, ‘parent’); prbQuery.addCondition(‘active’,’true’);

The result is a list of all Incidents that is limited via the addJoinQuery in the Query Business Rule to Incidents that are related to one or more active problems.

4 – Show Incidents that are not connected to a problem

SQL:

SELECT inc.number, inc.incident_state FROM incident AS inc WHERE inc.sys_id NOT IN (SELECT prb.parent FROM problem AS prb);

Even this is possible:

Database View:

Create a Database View with tables incident (inc) and problem (prb), left joining the problem table on inc_sys_id = prb_parent. The attributes “sys_id”, “number” and “incident_state” from incident and “parent” and “number” from problem should be available. You will see that you get a list of all incidents and their related (if any) problem information, resulting in duplicate incidents when an incident is parent for multiple problems.

Before Query Business Rule:

current.addNullQuery(‘prb_number’);

This Business Rule will filter out all the records that actually contain problem information. The result is a list of Incidents that do not have a problem connected to them.

Some notes on Access Controls versus Before Query Business Rules

When providing data for a report or a Web Service I try to avoid Access Controls, because they result in unwanted behavior: A Web Service will return only the records that the interface user is allowed to see when you use Access Controls. This sounds OK, but when you say “I want records 100 to 200” (assuming the number of valid results > 200), you may receive only less than 100 records because the others are hidden by an ACL. If you use the Before Query Business Rule, however, you will receive 100 records. In Reports this difference also shows: When using ACLs, you will get a message that a certain amount of records is not shown due to security constraints and less than complete list of records may be shown (you get 100 pages of 20 records, and on the first page only 10 records are shown because the others are hidden by ACLs). Before Query Business Rules do not have this problem; You will simply not see the records that you are not supposed to see.

Be aware: Before Query Business rules that are set on one or more of the tables that are a source of the Database View do NOT impact the results of the Database View. The same goes for Access Controls. The other way around: Business Rules and ACLs that are set on Database Views do not impact the source tables. Look at this as an opportunity rather than an issue.

Léandre Mundere is a multi-platform IT professional, committed to continuous learning and detail-obsessed tech-enthusiast. Léandre is currently focused on ITSM, CSM, HR, and ITBM where the main application is ServiceNow and its various integrations.

devoteam