Connecting to the ServiceNow REST API using OAuth2

Options

The Autonomous REST Connector JDBC driver allows you to load (and refresh) the results returned from REST API calls as Data Studio Datasets, by translating SQL statements to REST API requests.

In this article I'll show how to call ServiceNow's REST API (using OAuth2 authentication) to bring response data into Data Studio. There are several steps:

  1. Register your OAuth app in ServiceNow
  2. Retrieve the OAuth token
  3. Configure the External System in Data Studio
  4. Load data from parameterized paths

Register your OAuth app in ServiceNow

In ServiceNow, navigate to System oAuth > Application Registry > New and then Create an OAuth API endpoint for external clients. The Client ID and Client Secret will populate when the app is saved

Retrieve the OAuth token

There are lots of different ways to get the OAuth refresh token, which we will be using to authenticate requests. I use Postman. Follow these steps to set up Postman to get the OAuth token(s) for the initial authorization flow

  • The callback URL should match what you have configured in the ServiceNow app
  • Use the "Send client credentials in body" option in Postman
  • Auth URL: https://{instancename}.service-now.com/oauth_auth.do
  • Access token URL: https://{instancename}.service-now.com/oauth_token.do
  • Use Scope useraccount and state code

Clicking "Get New Access token" should prompt you to allow the connection

Make a note of the refresh_token.

Configure the External System in Data Studio

The attached ServiceNow.rest model file maps the ServiceNow REST API endpoints to tables.

To use it, remove the ".txt" extension, replace "{instancename}" in line 2 to your own SNOW instance, and then place on the Data Studio server.

Now create a new External System in Data Studio:

  • authURI=https://instancename.service-now.com/oauth_auth.do
  • clientId=From SNOW app
  • tokenURI=https://instancename.service-now.com/oauth_token.do
  • clientSecret=From SNOW app}
  • refreshtoken=From Postman token generation

You will need to create a Credential, but the username and password for this can be left blank.

Load data from parameterized paths

The final step will be to use the External System to load data from an endpoint. In this example we'll use the GET /now/table/{tableName} method from the Table API. Note that this (like most of the ServiceNow API endpoints) is a parameterized request, and we will need to provide the tableName as part of the query.

In Data Studio we do this by creating a Dataset from a SQL query. Create a new Dataset, select the ServiceNow External System, and select Database query:

The query SELECT * FROM AUTOREST.TABLERECORDS WHERE TABLENAME = 'incident' will bring back data into my new dataset from the GET Table API endpoint for the table named "incident".

Comments

  • Mahulima
    Mahulima Contributor
    edited June 25
    Options

    @Henry Simms

    Thank you for giving detailed steps, I just follwed the Steps and tried to establish the connection but I am getting this message when I try

    1. My Aperture settings:

    Refresh Token I received from Postman, and Rest of the Information is the same which I used in Postman and got the response.

    2. rest file I used yours and change the .txt so that its a .rest file and changed the instance in the rest

  • Henry Simms
    Henry Simms Administrator
    Options

    @Mahulima The error suggests a problem with the .rest file (or the driver's ability to read it), since the schema for TABLERECORDS is defined there rather than in ServiceNow.

    Your .rest file formatting may be the cause, can you double-check it's been downloaded correctly? It should look like this (section which defined the GET table → TABLERECORDS mapping:

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms

    I see this

  • Henry Simms
    Henry Simms Administrator
    Options

    Hi @Mahulima

    Can you try removing the schema name (AUTOREST) from the query, so just run:

    SELECT * FROM TABLERECORDS WHERE TABLENAME = 'incident

    Also, could you try to load a table that models a REST API call that is not parameterized: CINOTALLOWEDACTION. For this, you should be able to just select the table name rather than specifying a SQL query.

  • Mahulima
    Mahulima Contributor
    edited June 26
    Options

    @Henry Simms I tried without schema result is same

    also I dont see that table in my table list

    I see this kind of table only, So is it because of parsing the .rest file? as instead of the table names I am getting the Path, Path_1 like that.

    I think we are very close to set up the connection :) Please help us a bit more for a successful completion

  • Henry Simms
    Henry Simms Administrator
    Options

    @Mahulima It does look like the .rest file is not correctly being read. Here's the list of tables you should see as defined by that file I shared:

    Can you try using the same External System connection but change the .rest file to this small "test" on that just contains a single table definition (which is not parameterized):

    This should show one table (INCIDENTTABLE) which can be loaded.

  • Mahulima
    Mahulima Contributor
    edited June 26
    Options

    @henry_ @Henry Simms

    I tested with the above one and there also I see this

    But the big rest file if I point that to Progress Data Direct as a model and Auth Type I selected Oauth2, Basic both then I am able to get the API response in Progress Data Direct which was not able to do earlier.

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms

    Another Observation in the attached .rest file for me in my ADS I saw onlly HHTP is coming as Table , I tested add [ before and after Account like below

    and then I see that I can get that as a table

  • Henry Simms
    Henry Simms Administrator
    Options

    @Mahulima and I have been able to resolve this, but for future reference: This unusual behaviour (table names showing as "_HTTP", "_PATH_1", "_PATH_2" etc, is what happens when the .rest file path is entered into the REST endpoint field in the External System config, rather than into the REST config file field:

    Easy mistake to make, and unexpected result!

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms Thank you so much for all your help! :) The connection is Successful now for us. :)

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms

    I tried now to fetch other table information and able to get the records from other table as well. Now only problem is I am able to get only 21 columns from each table.

    Is it because of some permission issue? I am able to get the Postman response of all columns though. In Postman response I am getting all columns of those tables, but through external system I am getting only 21 columns for each table.

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms

    Few more observations, for all table its only getting specific system columns for example –

    SYS_ID

    SYS_CREATED_BY

    SYS_CREATED_ON

    SYS_DOMAIN_LINK

    SYS_DOMAIN_VALUE

    Also I tried with querying with the columns which are part of that table as I am getting those column values when I tried with Postman response. But those columns also throwing error.

  • Mahulima
    Mahulima Contributor
    Options

    @Josh Boxer @Henry Simms @henry_

    The issue is still there, I have checked for all tables its only fetcing records from some system columns and not the actual column records, In fact my Postman Response which has actual column records that does not have these systems columns. I have cross verified the columns from Postman Response Vs Columns from the External System.

    Please help here!!

  • Mahulima
    Mahulima Contributor
    edited July 4
    Options

    hi @Henry Simms @henry_ @Josh Boxer @Seamus Kyle

    Any update regarding the Limited number of columns fetching issue in SNOW connection? We are very close with our successful connection so eagerly waiting for your further response/guidance for making it up and running.

  • Mahulima
    Mahulima Contributor
    Options

    @Henry Simms @henry_ @Josh Boxer @Seamus Kyle Is there any update on this? This is very critical for us and we are blocked here. Please help

  • Josh Boxer
    Josh Boxer Administrator
    edited July 16
    Options

    Sorry Mahulima but I am not an expert with this driver and do often have to use some trial and error to get it working as required. If you have a Consultant to work with they should be able to work with you to get it configured to your exact specifications.

  • Mahulima
    Mahulima Contributor
    Options

    Hi @Josh Boxer It is not about the exact Configuration of My side of the system. The Example shared in the experian community also does not have all the details it has only 21 columns details and in my case also I am getting exact same number of columns. So my configuration is giving me exact same result what is shared here in the community. Do you know someone who can help us here from Experian side?