Configuration

CORS Configuration

In ServiceNow, administrators grants use's browser to access to the ServiceNow web services like REST, GraphQL, etc. with CORS.

You have to add two CORS rules in ServiceNow instance to allow access from the domain where the app is hosted.

For Read Access (SELECT)

1. Navigate to CORS Rules (System Web Services > REST > CORS Rules) 

2. Click on "New" and provide these values:

   • Name: Vision SQL

   • REST API: GraphQL API [now/graphql]

   • Domain: https://servicenow.marketplace.expert (or *marketplace.expert)

   • HTTP Methods: POST.

3. Click on "Submit"

For Write Access (INSERT, UPDATE and DELETE)

Create and additional CORS record in ServiceNow with these values:

1. • Name: Vision SQL Write

   • REST API: Table API [now/table]

   • Domain: https://servicenow.marketplace.expert or *marketplace.expert (including asterisk)  if previous did not work

   • HTTP Methods: POST, PUT, DELETE

OAuth configuration

Once Vision SQL is able to invoke the ServiceNow web services configured above, an API token must be created to authorize to access to the data in ServiceNow.

Follow these instructions written by ServiceNow to get an API token:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0778194

We have recorded a video to help you to success:

After create the OAuth rule it will work for a few minutes only!. Please follow theses steps to extend the expiration date.

Click on System OAuth > Manage Tokens

ServiceNow has created two records for the Vision SQL rule.

Edit the  first one (expires after 3 minutes of creation): 

Select a new expiration date:

Delete the second record

If after log in ServiceNow you do not see any table, please refresh the table cache by following the instructions in the Troubleshooting guide below

Troubleshooting

In general, open de developer console to see extra relevant information

Autocompletion

Click on "Shift+Space" on Windows to display the automcepltion tex box for table and columns names.

No tables or missing a new one

If after log in ServiceNow you do not see any table (run "show tables" command from the console)  or after creating  a new table in ServiceNow it is not available from Vision SQL then please refresh the table cache:

SQL brief introduction

• It supports standard SQL close to the standard ANSI'92 

• CRUD is supported (INSERT, SELECT, UPDATE & DELETE)

• Column and table names are case sensitive

• Use brackets [] for column aliases: SEELCT number as [Incident] FROM incident. Or you can use accents: `table`.`column`

• Shift + Space for table and column autocompletion

• Working with  dates:

  • GETDATE(): to get the current time

  • DATEADD(DAY, -1, GETDATE()): to add or subtract time

  • DATEFORMAT(sys_created_on, 'yyyyMMDD HH:mm:ss): to convert and format dates to text strings. Read moment.js for the supported formatting strings

  • CAST('2023-01-31' AS DATE): to convert a string to a date or CAST('2023-01-31 12:45:06' AS DATETIME) 

Quick Demo

In this video you can learn some basics about how to use Vision SQL. The demo has been recorded with Vision SQL as Node JS for ServiceNow, but the functionality is similar to the hosted version in our servers.