MicroStrategy ONE

Connect to ServiceNow Using JDBC

Connecting to Concur using JDBC is only supported on MicroStrategy ONE Update 11 or earlier. If you are using MicroStrategy ONE Update 12 or later, see Connect to ServiceNow Using ODBC.

ServiceNow is a web-based tool in which users can log service requests or incidents to ask assistant. All the requests should be routed to the appropriate service desk staff and will be managed on time. The primary purpose is to provide an easy-access self-service tool and users can request and receive necessary assistant timely and correctly. MicroStrategy ships the ServiceNow JDBC connector for customers to connect ServiceNow as a data source when importing data on Web and Workstation.

The ServiceNow JDBC driver supports standard SQL-92 syntax and functions but limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

  • Data Retrieve Mode: JDBC Call Only
  • Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

JDBC Connection URL

MicroStrategy ships the ServiceNow JDBC driver to connect ServiceNow as a data source with the following connection methods. You can find them on the data source dialog when importing data.

  • Using OAuth 2.0 authentication with standard ServiceNow credentials

    JDBC;DRIVER={com.simba.servicenow.jdbc42.CLJDBCDriver};URL={jdbc:servicenow://;Host=@host;Auth_Type=OAuth 2.0;Auth_Client_ID=@client; Auth_Client_Secret=Client_secret ;UID=@username;PWD=@password}

  • Using Access Token

    JDBC;DRIVER={com.simba.servicenow.jdbc42.CLJDBCDriver};URL={jdbc:servicenow://;Host=@hostname;Auth_Type=OAuth 2.0;Auth_AccessToken=@token

  • Using Refresh Token

    JDBC;DRIVER={com.simba.servicenow.jdbc42.CLJDBCDriver};URL={jdbc:servicenow://;Host=@host;Auth_Type=OAuth 2.0;Auth_RefreshToken=@refresh_token;Auth_Client_ID=@client_ID;Auth_Client_Secret=Client_secret}

See How to Get ServiceNow Connection String Parameters for steps to acquire the correct parameters.

JDBC Schema Tables

ServiceNow stores data in structures which do not follow the rules of traditional tables and columns. The JDBC maps ServiceNow data into its designed relational schema with multiple fixed schema tables. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Configure ServiceNow using JDBC on Web and Workstation

  • Workstation
  • Web

See Create and Edit Data Sources for steps to create a new data source and connection.

  1. Choose Add Data > New Data.
  2. Search ServiceNow in the data source list.

  3. Click the connector.
  4. Choose Select Tables, Type a Query, or Build a Query to import data.

    It is recommended to use Select Tables to reduce joins on the driver side for best performance.

  5. Click Next.
  6. In the Data Source interface, select DSN-less Data Source and choose ServiceNow for both Database and Version. Use one of the following methods to configure the DSN-less connection:
    • Standard Access: Enter the required Host Name, Client ID, and Password.
    • Access Token: Enter the required field Host Name and Access Token.
    • Refresh Token: Enter the required Host Name, Refresh Token, Client ID, and Client Secret.

    • Edit the Connection String: Click Show connection string and select Edit connection string. Enter the connection information of the JDBC connection string.

      Ignore Driver. Type any character in the User and Password fields, as they will not be used in the connection.

  7. Click OK.
  8. Click on the new data source to connect.

In-Memory Data Import

After creating and connecting to the ServiceNow data source, you can retrieve data through JDBC and then publish a cube for dashboards, reports and, documents.

The examples below are using the Select Tables import option.

  1. Click your ServiceNow data source and select the tables you want to import.

  2. Click Prepare Data.
  3. Click Finish.
  4. Select Import as an In-memory Dataset Mode to publish a cube.

How to Get ServiceNow Connection String Parameters

The Simba ServiceNow JDBC Driver supports OAuth 2.0 Access Token Authentication. This OAuth flow redirects the end user from a user defined app to the ServiceNow Interface to authenticate and authorize the request. Then it redirects the user back to the app with an authorization code that can be used to obtain an access token.

Create and Configure OAuth Provider for Client Credentials

  1. Log into your ServiceNow account with your credentials.

    ServiceNow URL: https://developer.servicenow.com/dev.do

  2. Create an instance or use an existing one if available.

  3. Expand you profile to check if your existing instance is online.

  4. Click Manage instance password to get the instance name, instance URL, username, and password.

  5. Click the instance URL: https://<my instance>.service-now.com/

  6. Navigate to System OAuth > Application Registry and click New.

  7. Click Create an OAuth API endpoint for external clients.

  8. Enter the Name. Leave the Client Secret field blank as it will auto-generate.

  9. Submit the request to get the Client ID and Client Secret connection parameters.

Get Access Token through Postman

  1. Download the Postman app.
  2. Create a POST request with the following information:
    • POST to URL: https://<my instance>.service-now.com/oauth_token.do
    • Authorization: No Auth

  3. In the Body tab:

    • Select the application/x-www-form-urlencoded option

    • grant_type: password

    • client_id: Client ID from ServiceNow

    • client_secret: Client Secret from ServiceNow

    • username

    • password

  4. Click Send.