What are remote tables
Since the New York release, ServiceNow offers a new functionality called remote tables.
Remote tables allows to query and retrieves data from other systems, via REST/SOAP and use them almost seamlessly in service-now.
The remote table can be used like a standard, local, table:
- They can be queried by script;
- We can see the records in lists or in forms;
- Reports can be run on the data;
- They can be used as reference from other tables (both local or remote tables);
- They support, like standard tables, sorting, aggregating and filtering like internal tables;
The obvious case is to retrieve data from a remote system, but there is nothing that prevent us to also use local tables or a mix of remote and local tables and present them as one remote table. We can also see the remote tables like a kind of enhanced database view, that allows for example to :
- aggregating data from multiple sources, both local or remote;
- Making calculations;
- Applying logic and complex conditions;
There is also an option to cache the data that has been retrieved for a certain time, to reduce the load and improve performances.
As you can see, Remote tables are a nice feature to use external data in ServiceNow and have them always up to date. It can be use in numerous scenario where external data need to be always up to date and when it is OK to have it read-only in ServiceNow.
In this article, we will play around and try different use of the remote tables. It will of course not cover all the use cases but it will give a good overview of what can be achieved.
The different use cases
For this test case, we will link an external table to ServiceNow using the remote table functionality.
As a first step, we will link the user table from an ServiceNow instance, using REST. We will include a few fields, including one reference field to Company and see how it react.
Then we will see how to retrieve the Company data in the remote table (like the company name) and see how to include also local data and set custom data in the remote table like calculation result.
Next step will be to use our remote table in a reference field on a local table (like on the incident table) and see what happen
Then we will create a new remote table for companies and see how we can make a reference from a remote table to another table
Finally we will see during all the article what should be done to optimize the query of the remote table, as the default behavior of our setup is to query always all the records.
For the sack of simplicity, both the source table and the remote table are on the same instance. But for make it more realistic (like if we are not querying another ServiceNow instance), we will not rely on the sys_id of the remote user table and we will user the UserId as the key. For the company table, we will use the sys_id, in order to not overcomplicate the example.
Creation the Remote Table
Let’s start by creating our remote rable.
There are three main steps:
- Define the table in the dictionary, where we set the table name, the columns and all the standard options for a table;
- Create a REST message to connect to the source table we want to query;
- Create the related Script Definition that query the source with the REST message and present the data in the Remote Table.
⚠Remote table plugin activation
Before using the Remote tables, check that the corresponding plug-in (com.glide.script.vtable) is activated. If this is not the case, activate it.
Define the table in the dictionary
To define the table in the dictionary, go in the menu System Definition - Remote Tables - Tables and click New.
⚠ there is currently no possibility to create Remote Table from the Studio. You need to create them from the menu and only after this they will appear in the Studio.
Fill in the name and choose whether or not you want to create a module, and check all the usual options for table creation (roles, etc.).
Then you need to add in the Columns list all the fields that you want on your Remote Table.
For now, we will keep it simple with only the First Name, Last Name, Company, Active, Phone and Email.
ℹ The Sys_id field is automatically created. Note that there is not the other usual systems fields like sys_created_on, sys_update_by, etc.
Create the REST message
Let’s create a REST message to the user table in our own instance, using the following settings:
- Endpoint : https://xxx.service-now.com/api/now/table/sys_user
- Authentication: basic - Create a profile and set your own user/password for example
- add an HTTP method: Get
- add the HTTP Query parameter (in the HTTP method);
Create the related script definition
Now the next step is to create a script definition for our new Remote Table. Go to System Definition - Remote Tables - Definitions and click New.
In table select the table you created before and let’s start writing the script that will retrieve the remote records and make them available in the Remote Table.
ℹ You will notice that there is a huge comment at the beginning of the script: it contains the explanation about how to prepare the script and how to use the APIs. I suggest you keep it in your script for reference, as there is not this information elsewhere in the documentation.
Now the goal of the script is to add rows to the Remote Table, using v_table.addRow. This function expect an object containing all the values. The name of each property in the object should match the name of the field defined in the Remote Table.
So let’s write the following script, where we query using the REST message and then build and add the rows to our Remote Table.
(function executeQuery(v_table, v_query) {
try {
// get the REST message
var message = new sn_ws.RESTMessageV2('x_12270_remote_tab.my users', 'Get');
// execute it
var response = message.execute();
// get the response
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
// if there is an error, log and return
if (response.haveError()) {
v_query.setLastErrorMessage(response.getErrorMessage());
gs.addErrorMessage(response.getErrorMessage());
return ;
}
// Parse response
var json = new global.JSON();
var responseObj = json.decode(responseBody);
var resultArr = responseObj.result; // all the records are contained in an array 'result'
for (var i=0; i< resultArr.length; i++){
//build the row
var row = {};
// map fields from the result
row.first_name = resultArr[i].first_name;
row.last_name = resultArr[i].last_name;
row.active = resultArr[i].active;
row.email = resultArr[i].email;
row.phone = resultArr[i].phone;
row.company = resultArr[i].company.value;
// add the row to the remote table
v_table.addRow(row);
}
} catch(ex) {
v_query.setLastErrorMessage(ex.message);
gs.addErrorMessage(JSON.stringify(ex));
}
})(v_table, v_query);
Now we can test it with the following script (to run in Background script):
var grX_12270RTSRU = new GlideRecord('x_12270_remote_tab_st_remote_user');
grX_12270RTSRU.query();
while (grX_12270RTSRU.next()) {
gs.info('active: ' + grX_12270RTSRU.getValue('active'));
gs.info('company: ' + grX_12270RTSRU.getValue('company'));
gs.info('email: ' + grX_12270RTSRU.getValue('email'));
gs.info('first_name: ' + grX_12270RTSRU.getValue('first_name'));
gs.info('last_name: ' + grX_12270RTSRU.getValue('last_name'));
gs.info('phone: ' + grX_12270RTSRU.getValue('phone'));
}
Also if we open the list of the table, you should see records in it:
So far so good: we can see the data from the ‘remote’ table and use it.
However, if we look carefully, we can see that for the company we only have the sys_id. This is because we do not have the display value in the payload. To get a more user friendly information, like the name of the company, we need to query it and use it in the script definition.
Getting the company name instead of the sys_id
One easy solution that comes to mind to solve this is to query the remote system with another HTTP method in the same REST message as before and get the company name. Let’s see how to do this.
First, let’s create a new HTTP method (in the existing REST message) for the company table, in a similar way that the one for querying the users, but with this endpoint: /api/now/table/core_company/${id} (and set a name like GetCompany and use it the the script below).
Then let’s create a function getRemoteCompanyName to retrieve the remote company and get the name.
function getRemoteCompanyName (id) {
try {
// get the REST message
var message = new sn_ws.RESTMessageV2('x_12270_remote_tab.my users', 'GetCompany');
// set the id of the company we want to retrive
message.setStringParameter("id", id);
// execute it
var response = message.execute();
// get the response
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
// if there is not error, continue
if (!response.haveError()) {
// Parse response
var responseObj = new global.JSON().decode(responseBody);
var resultObj = responseObj.result;
// check if the reuslt if ok and return the name
if (resultObj){
return resultObj.name;
}
}
// default empty string
return "";
} catch (ex){
gs.addErrorMessage(JSON.stringify(ex));
return "";
}
}
And replace the mapping line with this one:
row.company = getRemoteCompanyName(resultArr[i].company.value);
If we try it, we now we get the company name, but it pretty slow. The reason is that our script need to query the remote system for each user.
We will see later in this post of the solve this issue. If performances are too bad, for now just comment the mapping on the company.
Adding calculation and local data to the remote table
Now let’s see how we can add calculated data to the rows in the remote table and how we can use local data as well.
The calculated data would be simply to add a prefix to the phone number coming from the remote instance.
And local data would be looking incidents where the caller email matches the remote user email and give a count of how many incidents are found.
Both addition to the remote table are quite straightforward: we just need to add the required data in the row.
Add prefix to the phone number
Simply amend the script to add the prefix to the phone number, like this:
// add prefix to phone
var phone = resultArr[i].phone;
if (phone != ""){
phone = "+123 - " + phone;
}
row.phone = phone;
Now the users that have a phone number will have the prefix added in front
Add an incident counter
This requirement is a bit more complicated. First of all let’s create a new field *incident count" on the Remote User table.
To do this, go in the menu System Definition - Remote Tables - Tables, select the Remote User table and add a new field.
Then in the script definition, let’s create a function getIncidentCount.
function getIncidentCount(email){
try{
// get the incident count where caller email match the given email
var incidentGr = new GlideAggregate("incident");
incidentGr.addQuery("caller_id.email", email);
incidentGr.addAggregate('COUNT');
incidentGr.query();
if (incidentGr.next()) {
return incidentGr.getAggregate("COUNT");
}
return 0;
} catch (ex){
gs.addErrorMessage(JSON.stringify(ex));
return 0;
}
}
And add the result of the function into the new field on the row:
// add incident count
row.incident_count = getIncidentCount(resultArr[i].email);
Now the remote user have the incident counter filled:
Referencing a remote table from a local table
Now that we have the Remote User table defined and working, let’s use it in other tables. For example, let’s add a new field on the incident table, referencing the Remote User table.
First, let’s create a new reference field on the incident table, with reference to the Remote User table.
Then let’s try to use it: open the pop-up related list and choose a random Remote User. Before doing so, you will need to edit the reference list layout to add some meaningful fields, like First Name, Last Name and Email.
What happen after selecting the record? The field Remote User is still empty…
Why is this? The reason is simple: there is no sys_id defined on our remote table. This means that the reference field don’t know how to related to the correct row in the remote table, as sys_id is empty for all the rows of the remote table.
To solve this, we need to populate the sys_id of our rows. This need to be done, as you probably guess, in the definition script.
We could take the remote system sys_id, but let’s pretend we are not retrieving the records from another service-now instance and that we do not have a sys_id coming in. Let’s use instead the user ID (user_name), with a prefix ‘remote_’ added.
Let add this line to our script:
row.sys_id = 'remote_' + resultArr[i].user_name;
If we now go back in the incident and try again to select a Remote User, the value is set in the field and if we save the incident, it remains. We can even preview the record like with a local table (but read-only).
Technically, it stores what we have set as a sys_id into the reference field:
Change the display value of the remote table
It would be nicer if we can have something else that the sys_id set as a display value. Is this possible? Yes, of course: just go in the table definition and set the field you want to have as a display value.
Back in the incident, we now have the First Name as the display value:
Query optimization
Each time we go in the Remote Table, or we open a specific record, or use it like in the incident, the Remote Table queries the remote system and retrieve all the existing records. This is not optimal and could lead to poor response time.
We could of course enable the caching, but first let’s try to optimize the queries.
First let’s make a check to know how many records are coming in each time we query the Remote Table:. Add the following the the Remote Table definition script:
gs.addInfoMessage("querying remote users return "+ resultArr.length);`
If we now refresh the incident, we see that we query a lot of users, even if we want to display only one record.
Getting a single record
To optimye the query, we need somehow to get only the record that is interesting us.
The query helper method v_query.isGet() indicate if the query is a get and that only one record is expected.
We can then query the remote system with a different API call, using this endpoint /api/now/table/sys_user/${id}. The id will be provided by v_query.getSysId().
That would work like a charm if we didn’t decided to use a custom sys_id (“remote_” + user_name).
To overcome this, we need instead to use the same endpoint and build a query using the user_name. Luckily, v_query.getSysId() returns the sys_id stored in the reference field (or coming from a get from GlideRecord), so we can use it in our query.
Let’s add the following code:
// handle the get case
if (v_query.isGet()){
// get the sys_id
var sys_id = v_query.getSysId();
// remove the remote_ from sys_id
sys_id = sys_id.replace("remote_", "");
// build the query
message.setQueryParameter("sysparm_query", "user_name=" + sys_id);
}
and try to refresh the incident:
Our query is now retrieving only the record that is needed.
Getting onl y the records matching a filter
There are multiple cases where we filter the records, for example in a list, in a reference qualifier, etc.
Without anything in place, the remote table don’t care about the filer and always triggers a full query to the remote table.
But we can restrict the query to the remote system according the filter set in the query to the remote table.
Restrict the query to the remote system
If we go to the Remote User table and filter, we can see that anyway the Remote Table query all the records from the remote system
To avoid this, we need to add a filter on the outgoing REST message.
We can know about the query with the helper function v_query.getEncodedQuery().
We can also get rid of the part with the isGetthat we put in place before, because the encoded query contains in this case 'sys_id=xxx'.
⚠ Of course, depending the backend system, it will probably not be possible to simply use the encoded query. An ad-hoc query would need to be built. There are two functions that can help achieving this, if needed:
v_query.getCondition()andv_query.getParameter().
For the sys_id, here we need something to put a special handling in place to basically to parse the encoded query, replace sys_idwith the name of the field we want (user_name) and remove the prefix remote_. For more clarity this sys_id handling it is placed in a separate function adaptSysIdQuery().
So we can modify the script as follow:
// get the encoded query
var query = v_query.getEncodedQuery();
// set the query, if not empty
if (query != "" ){
message.setQueryParameter("sysparm_query", adaptSysIdQuery (query, "user_name", "remote_"));
}
// Function to adapt the sysid for the REST query - replace sys_id with the field name and remove the prefix
function adaptSysIdQuery (query, idField, prefix){
// if there is no sys_id in the query, return
if (query.indexOf('sys_id') == -1){
return query;
}
// split each condition
var queryArr = query.split('^');
for (var i = 0; i < queryArr.length; i++){
var elementStr = queryArr[i];
// split each term of the condition
var elementArr = elementStr.split("=");
if (elementArr[0] == "sys_id"){
// replace sys_id by the field name
elementArr[0] = idField;
// remove the prefix
elementArr[1] = elementArr[1].replace(prefix, "");
// reassemble elementArr and set back in queryArr
queryArr[i] = elementArr.join("=");
}
}
// reassemble query and return
query = queryArr.join('^');
return query;
}
Now back on the list, if we refresh we can see that we are querying only the record we need from the list.
References between remote tables
Remember above? With the company field on the Remote User table, we added a function that retrieve the company information from the remote instance and set it in the row.
What about, instead, creating a new remote table for the company and make the reference work between the existing Remote User table?
Create the Remote Company table
To do this, create a Remote Company remote table, in a similar way that for the incident
Add the fields you need on that table. At the moment we really only need the field name. As you are in the dictionnary, set this field name as the display value.
Create a HTTP method
Create a new HTTP method (in the existing REST message) for the company table, in a similar way that the previous for querying the company, but with this endpoint: /api/now/table/core_company (and set a name like GetCompanies and use it the the script below). The difference with the previous endpoint is that now we want to be able to query all the companies, not just only a specific company, hence the use of a different endpoint.
Create the script definition
Then create the related script definition for the Remote Company table and use the script below. Note that, not like with incident, we are now using the remote record sys_id as a sys_id.
(function executeQuery(v_table, v_query) {
try {
// get the REST message
var message = new sn_ws.RESTMessageV2('x_12270_remote_tab.my users', 'GetCompanies');
// get the encoded query
var query = v_query.getEncodedQuery();
// set the query if not empty
if (query != "" ){
message.setQueryParameter("sysparm_query", query);
}
// execute it
var response = message.execute();
// get the response
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
// if there is an error, log and return
if (response.haveError()) {
v_query.setLastErrorMessage(response.getErrorMessage());
gs.addErrorMessage(response.getErrorMessage());
return ;
}
// Parse response
var json = new global.JSON();
var responseObj = json.decode(responseBody);
var resultArr = responseObj.result; // all the records are contained in an array 'result'
for (var i=0; i< resultArr.length; i++){
//build the row
var row = {};
// map fields from the result
row.name = resultArr[i].name;
// build sys_id
row.sys_id = resultArr[i].sys_id;
// add the row to the remote table
v_table.addRow(row);
}
} catch(ex) {
v_query.setLastErrorMessage(ex.message);
gs.addErrorMessage(JSON.stringify(ex));
}
})(v_table, v_query);
Now we can try the Remote Company table and check that we get all the records.
Adapt the Remote User table
Let’s do now the required adaptation on the Remote User table.
First, go in the dictionary and change the type of the field company to Reference and make it reference to the new Remote Company table.
Then adapt the script definition of the Remote User table to set the value of the field company to the sys_id of the company coming in the payload:
row.company =resultArr[i].company.value;
Now let’s try to open a Remote User and see if it works:
It looks fine. Also from the list:
Conclusion
We cover quite some use cases in the examples above. The main idea was to discover this remote tables functionality and to play a bit with them.
The key take away is that remote table are a powerful feature to add in out toolbox. It can be used to help in numerous use case, either using remote data or calculating data on local tables.
Please leave you comments or questions below.
References
- ServiceNow documentation, Retrieving external data using remote tables and scripts, accessed 2020 06 09
- Chuck Tomasi, NOWCommunity Live Stream - Topical Deep Dive - Remote tables, broadcasted 2020 05 04