sql-servicenow v1.0.1

SQL for ServiceNow

Major features

• Full-featured relational database
• Supports SQL ANSI '92
• 100% built with JavaScript
• Supports CRUD: insert, update, select, delete
• Virtual database: all tables are empty an populated in real time to answer users' queries
• Honors ServiceNow permissions and business rules
• High performance at scale, honoring users' quotes
• Built upon the ServiceNow's APIs:
  • Read (select): GraphQL API now/graphql
  • Write (insert, update. delete): REST API now/table
• No support for ACID transactions
• Output in human readable table format or JSON format for integrations

Installation

npm i -g sql-servicenow

Configuration

1) SQL for ServiceNow requires permissions to read the following system tables:

• sys_db_object
• sys_dictionary

2) Create a ServiceNow API Token by following these ServiceNow's instructions:

OAuth Client

Or watch this step by step tutorial video:

How to Create an ServiceNow API Token

3) Create a configuration file with these two entries:

• SERVICENOW_URL: Your ServiceNow instance URL
• SERVICENOW_TOKEN: Your ServiceNow API Token (created in step #2)

4) Set the SN_SQL_CONFIG_FILE environment variable poiting to the configuration file created in step #3

Run the SQL console

Open a console and run:

C:\> sql-servicenow

Tables cache

When SQL for ServiceNow starts up the first time, it reads all the tables and saves them (names and columns) into a local file named tables.cache.

This file is created in the sql-servicenow directory under the npm configuration root folder:

$ npm root -g

The tables cache file must be manually removed when a table has been created in ServiceNow in order to refresh it. Re-run the console to create it again from the scratch.

Query examples

• Show all tables:

sql> show tables

• Show columns from a table:

sql> show columns from incident

• Your first ServiceNow SQL statement:

sql> select count(*) from incident

• All the incidents opened a year ago

sql> select i.number as [Incident], u.name as [Assignee], i.short_description from incident i left join sys_user u on u.sys_id = i.assigned_to where opened_at > DATEADD(YEAR, -1, GETDATE())

• Past year assignees with more than 5 incidents order by # of incidents desc

select u.name as [Assignee], count(*) from incident i left join sys_user u on u.sys_id = i.assigned_to where opened_at > DATEADD(YEAR, -1, GETDATE()) group by u.name having count(*) > 5 order by 2 desc

• Convert a string to a date:

sql> SELECT CAST('2023-01-31' AS DATE) FROM DUAL
sql> SELECT CAST('2023-01-31 12:45:06' AS DATETIME) FROM DUAL

• Closing the console

sql> exit;
sql> quit;

Support

Your feedback is highly appreciated. For support, please contact us at: support@marketplace.expert

Enjoy SQL for ServiceNow!

The Marketplace Expert Support Team