Troubleshooting the connector

The Snowflake connector for ServiceNow® is subject to the Connector Terms.

This topic provides guidelines for troubleshooting issues with the Snowflake Connector for ServiceNow®.

Note

The following sections describe stored procedures that are defined in the PUBLIC schema of the connector database. Before calling these stored procedures, select that database as the database to use for the session.

For example, if that database is named my_connector_servicenow, run the following command:

USE DATABASE my_connector_servicenow;
Copy

Verifying the Connection to the ServiceNow Instance

To verify that the Snowflake Connector for ServiceNow® can access the ServiceNow instance, call the TEST_SN_CONNECTION stored procedure, which is defined in the PUBLIC schema of the connector database:

CALL TEST_SN_CONNECTION('<table_name>');
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow instance.

If the connector is set up correctly, the stored procedure returns a row from the specified table.

For example, if the database for your connector is named my_connector_servicenow, and you want to verify the connection by retrieving a row from a table named my_table in your ServiceNow instance, run the following commands:

USE DATABASE my_connector_servicenow;
CALL TEST_SN_CONNECTION('my_table');
Copy

Comparing Table Row Counts in ServiceNow and Snowflake

To compare the current row count for a table in both ServiceNow and Snowflake, call the CHECK_SN_ROW_COUNT procedure:

CALL CHECK_SN_ROW_COUNT('<table_name>');
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow instance.

If the procedure times out, the procedure was unable to use the stats API to determine the row count of the table in ServiceNow. This may mean that the number of rows in this table is too large to be counted by this API.

Note

The number of rows returned may vary. A ServiceNow table may contain more rows that the equivalent Snowflake table. This may be caused by the access control list rules (ACLs) set for a given table in ServiceNow.

When a data range start time is configured for a table, the returned numbers of rows will likely be different. The ServiceNow row count still represents the count of all rows, while according to the configuration only a limited number of rows were ingested to Snowflake.

The connector uses different endpoints for retrieving information about the number of rows in a ServiceNow table. The connector uses stats for information about a table, including the number of rows. It uses table to ingest data into Snowflake.

Checking the Status of the Ingestion of a Row

To check the status of the ingestion of a row in all possible places in ServiceNow and Snowflake, call the CHECK_RECORD_HISTORY procedure:

CALL CHECK_RECORD_HISTORY('<table_name>', '<sys_id>');
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow instance.

sys_id

Specifies the sys_id of the row to check.

The procedure returns a JSON object containing the following properties:

Property

Description

table_name

Name of the table.

sys_id

Unique identifier for the row in ServiceNow.

status

Status of the ingestion of the row.

is_present_in_servicenow

true if the row is present in the table in ServiceNow; false otherwise.

is_present_in_servicenow_audit_table

true if the row is tracked in the audit table in ServiceNow; false otherwise.

is_present_in_snowflake_destination_table

true if the row has already been ingested and is available in the dest_db database in Snowflake; false otherwise.

event_log_records

Array of JSON objects that represent entries in the event log for the row with this sys_id.

Each object contains the following properties, which correspond to the columns in the event log table that specify the timestamps and event types of the data change:

  • sys_updated_on

  • event_date

  • event_type

Determining if a Table Is Audited for Deletion

The Snowflake Connector for ServiceNow® relies on auditing to propagate the deletion of records to Snowflake.

To verify that a given table in ServiceNow is configured to audit the deletion of records, call the CHECK_IF_AUDIT_ENABLED stored procedure:

CALL CHECK_IF_AUDIT_ENABLED('<table_name>');
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow instance.

This stored procedure checks the audit and no_audit_delete configuration for the specified table and prints out the information about whether or not auditing is enabled.

Obtaining Troubleshooting Data

To obtain troubleshooting data, call the GET_TROUBLESHOOTING_DATA stored procedure:

CALL GET_TROUBLESHOOTING_DATA(<number_of_days>);
Copy

Where:

number_of_days

Specifies the number of days in the past data should be fetched.

This stored procedure returns the following data in tabular format:

  • Configuration information

  • Errors experienced by connector

  • Ingestion history

The following example shows how to call this stored procedure:

USE DATABASE my_connector_servicenow;
  CALL GET_TROUBLESHOOTING_DATA(1);
Copy

You can save the returned data in CSV format to send to Snowflake Support.

Testing the Connection to ServiceNow

To validate if the connector can access a ServiceNow instance, call the GET_CONNECTION_STATUS stored procedure.

This stored procedure checks the connection and credentials stored in secret provided during the setup. It returns a variant with two keys:

  • status

  • details

The following example shows how to call the shows run the GET_CONNECTION_STATUS stored procedure:

USE DATABASE my_connector_servicenow;
CALL GET_CONNECTION_STATUS();
Copy

Recovering an Inaccessible Object

The connector requires multiple database objects that are external to the connector database. If these objects are unavailable, the connector will fail or stop working correctly. Situations where objects can become unavailable include:

  • Dropping an object.

  • Recreating an object without keeping or restoring the required grants.

  • Revoking the grants necessary for the connector to use these objects.

In most cases you can restore these objects manually by recreating them and granting the necessary privileges. The following sections describe how to restore different objects.

Restoring the Connector Warehouse

If the connector loses access to its warehouse, configure a new one by calling the CONFIGURE_WAREHOUSE stored procedure.

Restoring the Connector Secret Object

If the secret object used by the connector is dropped, you must recreate the secret using the same name in the same database and schema.

To check the fully qualified name of the secret run the following command:

SELECT value:secret FROM GLOBAL_CONFIG WHERE key = 'connection_config';
Copy

Additionally, you must grant the USAGE privilege on the recreated secret to the custom role used by the connector.

If the database or schema of the secret object is dropped, you can recover it by running the UNDROP command. This command also recovers the secret object.

If you cannot use the UNDROP command, then you must recreate the database and schema using the same names. You must also grant the USAGE privilege on the database and schema to the custom role used by the connector.

Restoring the API Integration

If the API integration used by the connector is dropped, you must recreate it using the same name. To check the name of the API integration the connector is expecting, run the following command:

SELECT value:apiIntegrationName FROM GLOBAL_CONFIG WHERE key = 'connection_config';
Copy

When recreating the API integration remember to use the same fully qualified secret name and ServiceNow instance URL as those used before.

To determine the fully qualified name of the secret run the following command:

SELECT value:secret FROM GLOBAL_CONFIG WHERE key = 'connection_config';
Copy

To determine the ServiceNow instance URL run the following command:

SELECT value:serviceNowUrl FROM GLOBAL_CONFIG WHERE key = 'connection_config';
Copy

You must also grant the USAGE privilege on the recreated API integration to the custom role used by the connector.

Restoring the Database and Schema for the ServiceNow Data

If the database or schema for the ServiceNow data is dropped, the only way to recover is by running the UNDROP command. If this command is unavailable, you must reinstall the connector and ingest the ServiceNow data again.

If a view containing the ServiceNow data is dropped, it should be recreated automatically the next time the background task responsible for creating them runs.

If one of the tables containing the ServiceNow data (either the event logs or raw data tables) is dropped and you cannot use the UNDROP TABLE command to recover it, do the following to start the ingestion of the ServiceNow table again:

Restoring the Custom Role of the Connector

If the custom role used by the connector is dropped, you must recreate it using the same name. To determine the name of the role the connector is expecting, run the following query:

SELECT value FROM GLOBAL_CONFIG WHERE key = 'data_owner_role';
Copy

You must also restore the listed privileges to the role. You can determine the names of the corresponding objects using the GLOBAL_CONFIG view.

Additionally, the role must have ownership on all the tables and views containing ServiceNow data from the corresponding schema.

If no additional tables or views were manually created in this schema, you can restore the roles by running the GRANT OWNERSHIP ON ALL TABLES/VIEWS IN SCHEMA ... TO ROLE ... commands.

For example, if the ServiceNow data is stored in the database dest_db and the schema dest_schema, to restore a role named connector_resources_provider run the following commands:

GRANT OWNERSHIP ON ALL TABLES IN SCHEMA dest_db.dest_schema TO ROLE connector_resources_provider;
GRANT OWNERSHIP ON ALL VIEWS IN SCHEMA dest_db.dest_schema TO ROLE connector_resources_provider;
Copy

The recreated role must also be granted to the connector database. For example, to grant the role named connector_resources_provider to the database my_connector_servicenow, run the following command:

GRANT ROLE connector_resources_provider TO DATABASE my_connector_servicenow;
Copy

Restoring the Notification Integration for the Connector

If the connector loses access to the notification integration object, do the procedures for configuring alerts again, recreating the notification integration object if necessary.

If email notifications are configured via Snowsight then you can just disable and re-enable them to restore the necessary external objects.