Skip to main content

Views

Usecase​

squid by arc46 enables the configurable export of all and any data including references, relations, "backreferences" ("related lists") 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.

ViewDefinition

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).

Configs​

A config defines a specific export, e.g. cmdb_ci_server. This is the path param that the caller must set in order to request a specific configuration.

Configuration Parameter for Configs​

TableConfigs

Config​

This is the name of a configuration and has two use cases:

  • as URL parameter when requesting data, e.g. https://../v1/data/cmdb_ci_computer. Here cmdb_ci_computer is the config that defines what data is returned for this request and
  • as a so-called 'Target Config' that is used when defining references, backreferences, etc. In this case config defines how the referenced entity should be rendered.

Description​

A text description of the config. The intended use and specifics of a config will sometimes be difficult to discern from just the config and view names and may be described here.

Database view​

Database view is a reference to the ServiceNow database view that is used to retrieve data. See Views.

Is Root​

root defines if a config may be used as the initial entry point of a request, i.e. if you can use the config as a URL parameter when requesting data.

This has security implications. You might want to allow access to companies as manufacturer or vendor of a computer, but prevent querying all and any companies in your system.

In this case you would define a configuration cmdb_ci_computer as root = true and but core_company as root = false. This way all referenced companies would be accessible, without granting direct access to other companies.

Limit​

limit defines the maximum amount of records returned for a request of this config.

squid by arc46 is designed to have a minimal impact on your instances, regardless of the amount of data retrieved. It can retrieve millions of records including referenced data with 10-20 database queries and a minimal amount of memory usage.

This is achieved by collecting references and retrieving these in combined database queries. The downside is that these references are appended to the end of the JSON.

{
  "data": [
    {
      ...,
      "manufacturer": {
        "sys_id": "b7e7a507c0a8016900920ee7d71d6df9",
        "sys_class_name": "core_company"
      },
      ...
    }
  ],
  "relations": {},
  "referenced": {
    ...,
    "b7e7a507c0a8016900920ee7d71d6df9": {
      "city": "Palo Alto",
      "name": "Hewlett-Packard",
      "sys_class_name": "core_company",
      "sys_id": "b7e7a507c0a8016900920ee7d71d6df9"
    },
    ...
  }
}

Requests for a limited amount of records may want to have referenced data inlined, resulting in a completely self-contained JSON element. This is a valid use case and can be achieved by setting the render property to e.g. Inline Content.

{
  "data": [
    {
      ...,
      "manufacturer": {
        "city": "Palo Alto",
        "name": "Hewlett-Packard",
        "sys_class_name": "core_company",
        "sys_id": "b7e7a507c0a8016900920ee7d71d6df9"
      },
      ...
    }
  ]
}

This however requires the referenced data to be retrieved ad hoc, potentially triggering a large amount of database requests. This is not a problem for one or ten CIs. It is however a problem when requesting 100.000 CIs, as this could cause millions of database requests, depending on the amount of resolved references

limit allows you to limit the amount of returned elements for resource intensive configurations, protecting your ServiceNow instances.

View Filter​

ServiceNow database views do not allow conditions in the view definitions. view_filter compensates for this and allows you to refine what data is returned by a config by defining an additionally executed encoded query. This may be used in two major use cases:

  • restricting access to data to a specific client. Add a view_filter base_client=your_client_name (not a real field name) and secure access to the config by defining required roles. This way you can reuse the same view while offering different subsets of data to different users or
  • use the same view for differing purposes such as cmdb_ci_server with a base_sys_class_name=cmdb_ci_linux_server view_filter would allow you to reuse a cmdb_ci_server based view while only returning Windows server as opposed to defining a specific view based on cmdb_ci_win_server. This is a valid usecase if you do not require Windows server specific data.
TODO Not completly correct. Rework.

Reference Group Names​

squid by arc46 by default will render any reference fields contained in a view as so-called 'Inline SysId' without any further resolution. This might be all you need and has no performance impact.

{
  "manufacturer": {
    "sys_id": "b7e7a507c0a8016900920ee7d71d6df9",
    "sys_class_name": "core_company"
  }
}

In order to retrieve and render data above and beyond the above, we have to tell squid by arc46 how the reference should be resolved and rendered.

'Reference Group Names' contain a comma separated list of Reference Configs Group Names that are accessed in the order defined here. 'default' is implicitly appended to the list unless explicitly included. The current config name is implicitly added at the beginning of the list unless explicitly included. Reference configs are processed in the order listed. First match has precedence. See Reference Configs Group Names for details on how 'Reference Group Names' are defined.

Relation Configs​

Relations (Relations, Backreferences, M-to-N) are not part of the database view queried to initially retrieve data. They must be explicitly resolved with additional, specific queries. Accordingly, they must be explicitly configured for a configuration.

'Relation Configs' contain a comma separated list of Relation Configs (TODO link to Relations) that are to be resolved. Entries are matched against both Names as well as Group Names. All matching configs are resolved and included in the returned JSON.

Roles​

roles allow you to restrict access to a config to members of the defined role. These are ServiceNow roles, that must be administrated according to standard procedures.

rolescontains a comma seperated list of ServiceNow roles. Only users that are members of at least one of these roles are granted access to the config. This allows for highly regulated data access when combined with view filters.

Use ServiceNow ACLs​

secure decides if GlideRecord or GlideRecordSecure are used when retrieving data.

This flag is evaluated on the root config only and is then used for every database access for that request. Subsequent settings on referenced configs are ignored.

Please test access extensively if set to true as ACLs might result in hard to debug effects.

Referenced Entities​

Resolving referenced entities is the core added value of squid by arc46. TableAPI allows you to retrieve a JSON representation of the content of a specific table and may even include links to directly referenced records. But TableApi will not provide the content of the referenced records nor will it resolve 'backreferences', relations or m-to-n tables. This is left for the caller of TableAPI to do on their own.

squid by arc46 will render all direct references of an entity if included in the defined view.

squid by arc46 will retrieve and render all configured 'backreferences', relations and m-to-n connections if these are configured.

References​

References are ServiceNow reference fields that are included in the view of a config. The default behavior is to render reference fields as 'Inline SysId'.

ReferncesConfig

Group Name​

Reference (Fieldname)​

Render / Render Types​

render defines how a referenced entity should be rendered and may be any of the values described below.

Inline SysId​

This is the default render type used for references and renders the sys_id of the reference and the sys_class_name as derived from the table / view metadata.

Example: The reference field manufacturer in cmdb_ci_computer - if included in the view without any further configuration or explicitly with Inline SysId would be rendered by squid by arc46 as

{
  ...,
  "manufacturer": {
    "sys_id": "42acbc44874035106dc863540cbb350a",
    "sys_class_name": "core_company"
  },
  ...
}

This is almost the same as what ServiceNow Table API would return

{
  ...,
  "manufacturer": {
    "link": "https://myit-dev.post.ch/api/now/table/core_company/b7e7d7d8c0a8016900a5d7f291acce5c",
    "value": "b7e7d7d8c0a8016900a5d7f291acce5c"
  },
  ...
}

or

{
  ...,
  "manufacturer": {
    "display_value": "Dell Inc.",
    "link": "https://myit-dev.post.ch/api/now/table/core_company/b7e7d7d8c0a8016900a5d7f291acce5c",
    "value": "b7e7d7d8c0a8016900a5d7f291acce5c"
  },
  ...

Does NOT require a target config.

Performance Impact of Render Type Inline SysId: None. No extra database queries are executed.

Literal​

If a ServiceNow reference is defined with a specific 'Reference key', then that key is persisted in the database instead of theusual sysId. Attempting to resolve such a reference be counterproductive as the reference key will usually be the relevant business value and any attempt to resolve the reference would fail as squid by arc46 expects a sysId as a reference value.

ReferenceKey

In these cases use render type literal to render the value in the reference field as-is:

{
  ...,
  "life_cycle_stage": "Operational",
  "life_cycle_stage_status": "In Use",
  ...
}

A Future release might expand functionality to resolve entities with specific reference keys.

Does NOT require a target config.

Performance Impact of Render Type Literal: None. No extra database queries are executed.

Inline Reference​

An 'Inline Reference' is rendered exactly the same as an 'Inline SysId' inside the referencing entity.

Additionally, the referenced entity is rendered in the referenced section of the returned json. How the referenced entity is rendered is determined by the target config defined on the Reference.

As the same entry may be referenced by different paths (e.g. Apple Inc as vendor and manufacturer) it may occur that differing configs are defined as target configs. In this case all configurations are retrieved, the data is merged and the merged data rendered. If e.g. vendor is defined to render only name and telephone number, but manufacturer is defined to render name, location and website, but not the telephone number, the rendered reference will merge both objects including all data.

{
  "data": [
    {
      ...,
      "manufacturer": {
        "sys_id": "42acbc44874035106dc863540cbb350a",
        "sys_class_name": "core_company"
      },
      ...
    }
  ],
  "relations": {},
  "referenced": {
    ...,
    "42acbc44874035106dc863540cbb350a": {
      "country": "USA",
      "name": "pyramid",
      "sys_class_name": "core_company",
      "sys_id": "42acbc44874035106dc863540cbb350a"
    }
  }
}

Requires a target config that defines how the entity should be rendered.

Performance Impact of Render Type Inline Reference: Minimal. Referenced entities are queried by target configuration. Regardless of the amount of referenced entities (1 or 100.000) we only execute one query per target config, retrieving all referenced entities for that config in one call. In practical terms depending on your configuration you will seldom execute more than 10-20 extra queries for referenced entities.

Inline Content​

Some use cases may require fully self-contained JSON representation of entities, meaning all data belonging to an entity is included in the JSON element itself without referencing any element outside of that element.

Reference with Render Type 'Inline Content'

{
  "data": [
    {
      ...,
      "manufacturer": {
        "country": "USA",
        "name": "pyramid",
        "sys_class_name": "core_company",
        "sys_id": "42acbc44874035106dc863540cbb350a"
      },
      ...
    },
    ...
  ]
}

as opposed to reference with Render Type 'Inline Reference'.

{
  "data": [
    {
      ...,
      "manufacturer": {
        "sys_id": "42acbc44874035106dc863540cbb350a",
        "sys_class_name": "core_company"
      },
      ...
    }
  ],
  "relations": {
  },
  "referenced": {
    ...,
    "42acbc44874035106dc863540cbb350a": {
      "country": "USA",
      "name": "pyramid",
      "sys_class_name": "core_company",
      "sys_id": "42acbc44874035106dc863540cbb350a"
    }
  }
}

With 'Inline Content' callers do not have to resolve the references themselves, but may consume the returned JSON directly as-is.

Using 'Inline Content' is a valid approach if only single entities are being queried as the net amount of database queries remains more or less the same.

If however large datasets are being exported, 'Inline Content' becomes extremely performance expensive as the inlined entities must be queried ad hoc at the time of writing the referencing entity resulting in potentially millions of database queries.

See Cache and Limit.

Requires a target config that defines how the entity should be rendered.

Performance Impact of Render Type Inline Content: Potentially extreme. Unless Caching is a viable option each and every reference triggers a database query.

Display Value​

Render Type 'Display Value' allows you to render the ServiceNow 'display value' of an entity instead of a reference to that entity.

{
  ...,
  "manufacturer": "pyramid",
  ...
}

Render Type 'Display Value' looks deceptively innocent and simple. In fact, it is even more resource intensive than ' Inline Content' as every referenced entity must be retrieved ad hoc, just as with 'Inline Content', but display value is not cached.

A future release might implement caching of display values.

Does NOT require a target config.

Performance Impact of Render Type Display Value: Potentially extreme, as each and every reference triggers a database query.

Target Config​

Some of the above-mentioned render types render the content of a referenced entity. For this purpose a so called 'target config' must be defined. The 'target config' determines which config should be utilized to retrieve the data of the referenced entity.

Cache​

cache is an optimization only relevant for the render type 'Inline Content'.

TODO WRONG Relation Content should be able to cache too.

Example: A reference to a manufacturer is highly likely to occur multiple times, e.g. if you manage 2000 iPhones and are exporting the manufacturer and vendor you will be referencing Apple Inc. 4000 times. Any inline rendering of Apple Inc. without caching would result in 4000 redundant database queries. If cache=true Apple Inc. will only be retrieved once, twice if manufacturer and vendor have different target configs.

Why not cache all the time? Some, if not most, references are unique. A referenced Asset will be unique to the referencing CI and never be used again. Keeping large amounts of entities referenced can easily cause memory problems. Therefore, use cachewisely and with care.

'Backreferences' / Relations / M-to-N Tables​

While references are define on the referencing entity itself, 'Backreferences', Relations and relations defined in M-to-N tables require more effort to resolve.

'Backreferences': ServiceNow often defines 1-to-n relations on the 'n'-entity, e.g. a storage device references the CI it is built into in the property computer. When exporting computer hardware this 'backreference' is sometimes relevant and required. It cannot however be directly retrieved from the defined view. 'Backreferences' define which 1-to-n relations should be queried and how they should b represented in the JSON.

{
  "manufacturer": {
    "sys_id": "42acbc44874035106dc863540cbb350a",
    "sys_class_name": "core_company"
  }
}

Group Name​

Reference (Fieldname)​

Cache​

Target Config​

Render​

"Backreferences"​

Relations​

M-to-N Tables​

Last updated on