Views

Usecase

Squid enables the configurable export of all and any data including references, relations, 1-to-n and the various forms of m-to-n tables in the ServiceNow database as structured JSON. The content and structure of these exports is fully configurable.

---

Views

squid by arc46 utilizes ServiceNow "Database views" to define what is exported. Views can restrict what columns are displayed / exported as well as incorporate data from other joined tables, making complex structures possible. The included data can then be leveraged to create complex queries.

If no view fields are defined for a table included in a view, all columns of that table are included in the view. If view fields are defined, only those are returned. This allows for a controlled, restricted export of data.

Single table view

The simplest use case is a single table view. The view defines what columns are exported. This is a valid, often applied use case.

Include ServiceNow subclasses

Another common use case is to combine a base table with derived subclasses to enable a consolidated export of data even if the exported attributes are defined on the type specific derived tables.

Example:

Servers are defined on the base table cmdb_ci_server.

Type specific details however are defined and only available on the derived tables of the subclasses, e.g.
cmdb_ci_linux_server which contains the added property kernel_release,
cmdb_ci_aix_server which contains the added property mainframe and
cmdb_ci_solaris_server which contains the added properties ldom_role, ldom_state and ldom_version.

The solution is to define a view based on cmdb_ci_server that is outer joined with cmdb_ci_linux_server, cmdb_ci_aix_server and cmdb_ci_solaris_server including the type specific values in the resulting view.

It is then possible to export all servers, including their type specific attributes in a single view / export.

TODO matching screenshot with correct data.

Include extra data for query purposes

A more specific use case is filtering entities based on values that are not part of the entity, but may be joined from other tables.

TODO examples and differentiate from dot walking references

ServiceNow "Variable Prefixes"

In order to prevent column name (variable) conflicts when creating a view with multiple tables, ServiceNow requires you to define a so called ' Variable Prefix' for every table that is used as a namespace for the columns of that table.

The variable prefix of the leading base table currently MUST be 'base'. If view fields are defined, sys_id MUST be included in the view. (The prefix requirement may be relaxed in the future.)

The prefix is removed when rendering the returned JSON, i.e. base_sys_id becomes sys_id.

Property name conflicts

squid by arc46 handles possible column name (variable) conflicts gracefully.

Properties defined with the variable prefix base always have precedence.

In the case of properties with other prefixes, the column with the alphabetically highest value has precedence ( zzz_column wins out over aaa_column).