Snowflake Integration with ServiceNow Using REST API, OAuth 2.0, and Scheduled Job!!

[Selva Arun]

Snowflake Integration with ServiceNow Using REST API, OAuth 2.0, and Scheduled Job

This document provides a comprehensive guide for implementing OAuth integration between Snowflake and ServiceNow. It covers the complete process from user creation to testing, including detailed steps for OAuth token generation, REST API setup, and the implementation of a scheduled job to update Windows Server CI records in the CMDB.

Use Case

The purpose of this integration is to enable Snowflake to ServiceNow integration using REST API instead of JDBC. The integration leverages OAuth 2.0 for secure authentication and ensures that the Windows Server CI records in the ServiceNow CMDB are regularly updated with accurate data from Snowflake.

Snowflake contains the true source of data for the purchase_date and warranty_expiration fields, which are critical for managing the lifecycle of Windows Server CIs. This integration was chosen over ServiceNow Discovery because Snowflake already holds the most reliable and up-to-date information for these fields. The scheduled job ensures that the CMDB is synchronized daily with Snowflake, providing accurate and consistent data for lifecycle management.

Step 1: User Creation and Configuration

1. Create the ServiceNow integration user in Snowflake:

CREATE USER SERVICENOW_USER PASSWORD = 'Snow2Service@2025'; 

1. Create and assign the necessary roles:

CREATE ROLE SERVICENOW_METADATA_ROLE; 

CREATE ROLE SERVICENOW_INTEGRATION_ROLE; 

GRANT ROLE SERVICENOW_METADATA_ROLE TO USER SERVICENOW_USER; 

GRANT ROLE SERVICENOW_INTEGRATION_ROLE TO USER SERVICENOW_USER; 

1. Grant warehouse access to the SERVICENOW_USER:

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SERVICENOW_INTEGRATION_ROLE; 

GRANT OPERATE ON WAREHOUSE COMPUTE_WH TO ROLE SERVICENOW_INTEGRATION_ROLE; 

1. Grant access to the database and schema:

GRANT USAGE ON DATABASE SERVICENOW TO ROLE SERVICENOW_INTEGRATION_ROLE; 

GRANT USAGE ON SCHEMA SERVICENOW.PUBLIC TO ROLE SERVICENOW_INTEGRATION_ROLE; 

GRANT SELECT ON TABLE SERVICENOW.PUBLIC.CMDB_ENRICHMENT_DATA TO ROLE SERVICENOW_INTEGRATION_ROLE; 

Step 2: Security Integration Setup

1. Create the OAuth security integration using the ACCOUNTADMIN role:

CREATE SECURITY INTEGRATION MY_SERVICENOW_CLIENT 

TYPE = OAUTH 

ENABLED = TRUE 

OAUTH_CLIENT = CUSTOM 

OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' 

OAUTH_REDIRECT_URI = 'https://dev197363.service-now.com/oauth_redirect.do' 

OAUTH_ISSUE_REFRESH_TOKENS = TRUE 

OAUTH_REFRESH_TOKEN_VALIDITY = 86400 

PRE_AUTHORIZED_ROLES_LIST = ('SERVICENOW_METADATA_ROLE', 'SERVICENOW_INTEGRATION_ROLE'); 

BLOCKED_ROLES_LIST = ('ACCOUNTADMIN', 'ORGADMIN', 'SECURITYADMIN'); 

1. Verify the integration configuration:

DESCRIBE SECURITY INTEGRATION MY_SERVICENOW_CLIENT; 

1. Retrieve and securely store the client secret:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MY_SERVICENOW_CLIENT'); 

Step 3: REST Message & OAuth 2.0 Setup

To fetch data from Snowflake, a REST Message was configured in ServiceNow. This REST Message uses the Snowflake SQL API to execute SQL queries and retrieve the required data.

Why Use POST Instead of GET?

• SQL Query Execution: The Snowflake SQL API requires the SQL query to be sent in the request body, which is only possible with the POST method.
• Dynamic Querying: Using POST allows dynamic execution of SQL queries, such as selecting specific columns or filtering data.
• Security: The POST method ensures sensitive information is not exposed in the URL.
• Compliance with Snowflake API: The Snowflake SQL API is designed to execute queries via POST requests.

OAuth Configuration in ServiceNow

• Client ID: BgLEuJIFvpcrYBfpc4TWewUXXZ0=
• Authorization Endpoint: https://pva65602.east-us-2.azure.snowflakecomputing.com/oauth/authorize
• Token Endpoint: https://pva65602.east-us-2.azure.snowflakecomputing.com/oauth/token-request
• Redirect URI: https://dev197363.service-now.com/oauth_redirect.do

REST Message Configuration

• Name: Snowflake data ingestion
• HTTP Method: POST
• Endpoint: https://zjfxgev-tva76920.snowflakecomputing.com/api/v2/statements/
• Headers:
• Authorization: Bearer token (retrieved via OAuth)
• Content-Type: application/json
• Request Body:

{ 

  "statement": "SELECT SYS_ID, SERVER_NAME, SERIAL_NUMBER, WARRANTY_EXPIRATION, PURCHASE_DATE, VENDOR_CONTACT, MAINTENANCE_SCHEDULE, LIFECYCLE_STAGE, LIFECYCLE_STATUS, ADDITIONAL_NOTES FROM CMDB_ENRICHMENT_DATA", 

  "role": "SERVICENOW_INTEGRATION_ROLE", 

  "database": "SERVICENOW", 

  "schema": "PUBLIC", 

  "warehouse": "COMPUTE_WH", 

  "timeout": 60, 

  "parameters": { 

    "query_tag": "ServiceNowIntegration", 

    "use_cached_result": "true" 

  } 

} 

Step 4: Scheduled Job Implementation

To automate the update of Windows Server CI records in the CMDB, a scheduled job was created in ServiceNow. This job fetches data from Snowflake, converts the date fields from Epoch format to standard YYYY-MM-DD format, and updates the warranty_expiration and purchase_date fields in the cmdb_ci_win_server table.

Script for Scheduled Job

try { 

  var r = new sn_ws.RESTMessageV2('Snowflake data ingestion', 'Get CMDB Info'); 

  var response = r.execute(); 

  var responseBody = response.getBody(); 

  var httpStatus = response.getStatusCode(); 

  if (httpStatus == 200) { 

    var responseData = JSON.parse(responseBody); 

    var data = responseData.data; 

    for (var i = 0; i < data.length; i++) { 

      var row = data[i]; 

      var serialNumber = row[2]; 

      var warrantyExpiration = row[3]; 

      var purchaseDate = row[4]; 

      if (!serialNumber) continue; 

      var warrantyDateStr = convertDaysToDate(warrantyExpiration); 

      var purchaseDateStr = convertDaysToDate(purchaseDate); 

      var gr = new GlideRecord('cmdb_ci_win_server'); 

      gr.addQuery('serial_number', serialNumber); 

      gr.query(); 

      while (gr.next()) { 

        if (warrantyDateStr) { 

          var warrantyDate = new GlideDateTime(); 

          warrantyDate.setValue(warrantyDateStr); 

          gr.warranty_expiration = warrantyDate; 

        } 

        if (purchaseDateStr) { 

          var purchaseDateTime = new GlideDateTime(); 

          purchaseDateTime.setValue(purchaseDateStr); 

          gr.purchase_date = purchaseDateTime; 

        } 

        gr.update(); 

      } 

    } 

  } 

} catch (ex) { 

  gs.print('Error occurred: ' + ex.message); 

} 

function convertDaysToDate(daysSinceEpoch) { 

  if (!daysSinceEpoch || isNaN(daysSinceEpoch)) return null; 

  var epoch = new Date(1970, 0, 1); 

  epoch.setDate(epoch.getDate() + parseInt(daysSinceEpoch)); 

  return epoch.toISOString().split('T')[0]; 

} 

Snowflake SQL API reference | Snowflake Documentation

Conclusion

This integration ensures that the CMDB is updated daily with accurate data from Snowflake. The conversion of dates from Epoch format to YYYY-MM-DD ensures compatibility with ServiceNow's data requirements, providing a seamless and reliable solution for managing Windows Server lifecycle information.

For a detailed video, please check out our YouTube channel: https://www.youtube.com/watch?v=si-yGGUKrAo. Please like, share, and subscribe to our channel.

If you believe the solution provided has adequately addressed your query, could you please mark it as 'Helpful'? This will help other community members who might have the same question find the answer more easily.

Thank you for your consideration.

Selva Arun

[SupriyaWaghmode]

@Selva Arun -Insightful Information.