vision-sql-servicenow v1.2.22

Vision SQL for ServiceNow

Vision SQL is a sophisticated relational database emulator in JavaScript that introduces ANSI '92 SQL support to ServiceNow. This powerful solution is specifically engineered to operate seamlessly with cloud systems, accommodating thousands of tables and users, and millions of data.

Utilizing an exceptionally efficient algorithm, Vision SQL retrieves the minimal data necessary from the remote system to answer user queries in real-time. By integrating with both GraphQL and REST Table APIs, it adheres to established business rules, permissions, and system users' quotas. This ensures optimal data privacy and scalability, making it an ideal choice for large-scale enterprise applications integrated with ServiceNow.

It supports CRUD.

It does not support ACID transactions.

It ouptus the results in JSON format.

OpenAI ChatGPT integration

It is ideal for Natural Language integration as it provides two essential parts:

1) A console to write and run SQL queries which allows to create a fine-tuning file easly.

{"prompt": "how many incidents in total?", "completion": "select count(*) from incident"}

2) An HTTP server to run SQL queries

http://localhost:3001/run?sql=select coount(*) from incident

You can fine-tuning your own ChatGPT model to translate users' Natural Language to precise SQL queries and then run those queries to manage ServiceNow's data: read data to asnwer and reporting, and write data to performa actions like create an incident, add a comment, change the priority or whatever else.

It is even posible to integrate it with ServiceNow Virtual Agent for a users' complete and real experience speaking with a chatbot able to ineract with ServiceNow at data level without sharing the data with third party AI systems: only SQL queries!.

Examples of queries you can fine-tune:

• How many incidends were opened in last week assigned to me last week?
• Who did create those indidents?
• How many incidents are major or critical and still opened?
• What are the incidets close to their due dates?
• Change priority of INC00035 to Low.
• Add this comment to INC00035: "Your incident will be resolved within 24 hours"
• Close INC00035
• etc.

Please read Openai ChatGPT Fine-tunning for more information.

Installation

npm i -g vision-sql-servicenow

Configuration

Open a system console and run:

C:\> vision-sql-servicenow

• Edit the servicenow.config file and provide the ServiceNow credentials:

  • SERVICENOW_URL: Your ServiceNow instance URL
  • SERVICENOW_TOKEN: Your ServiceNow API Key

To create an API Key, refer to ServiceNow's documentation: Create an API Token

Or watch this video: How to Create an API Token

Vision SQL requires permissions to read the following system tables:

• sys_db_object
• sys_dictionary

Run modes

Console

You can run SQL queries against ServiceNow direclty from the console:

C:\> vision-sql-servicenow

or pass a SQL query as argument to execute it:

C:\> vision-sql-servicenow "select number as [inciddent], short_description from incident" > output.json

HTTP Server

First, you have to configure the HTTP Server basic credentials in the http-server.config file.

Oprionaly, you may want to add a cert.pem and a key.pem files to the installation root directory to enable secure HTTPS prototocol

Start up the http server:

C:\> vision-sql-servicenow --port 3001

Run queries against ServiceNow:

http://localhost:3001/run/?sql=select count(*) from incident

HTTP Client

An HTTP client version is hosted on our servers:

https://servicenow.marketplace.expert/

It's a convenient way to run Vision SQL for ServiceNow from the browser.

It supports code autocompletion for ServiceNow tables and columns with Shit + Space (in Windows).

Query examples

• Your first ServiceNow query:

sql> select count(*) from incident

• Show all tables:

sql> show tables

• Show columns from a table:

sql> show columns 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 using Vision SQL! ... and happy fine-tunning!

The Marketplace Expert Support Team