Troubleshooting the connector

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

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®V2 can access the ServiceNow® instance, call the TEST_CONNECTION stored procedure, which is defined in the PUBLIC schema of the connector database:

CALL TEST_CONNECTION();
Copy

If the connector is set up correctly, the stored procedure returns the following response:

{
  "responseCode": "OK",
  "message": "Test request to ServiceNow® succeeded."
}
Copy

For example, if the database for your connector is named my_connector_servicenow, and you want to verify the connection to ServiceNow® instance, run the following commands:

USE DATABASE my_connector_servicenow;
CALL TEST_CONNECTION();
Copy

Verifying Access to the specific table in the ServiceNow® Instance

To verify that the Snowflake Connector for ServiceNow®V2 can acces data from the specific table in the ServiceNow® instance, call the TEST_TABLE_ACCESS stored procedure, which is defined in the PUBLIC schema of the connector database:

CALL TEST_TABLE_ACCESS('<table_name>');
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow® instance.

If the connector is set up correctly and data is available to the user used by the connector, the stored procedure returns the following response:

{
  "responseCode": "OK",
  "message": "Test request to ServiceNow® succeeded."
}
Copy

Note

If table is empty or all the rows are hidden from the connector because of ACLs, the message will say: Test request to ServiceNow® succeeded but it didn't return any record. In this situation, make sure that the table is really empty. If any rows are visible from the UI, it means that the connector is not able to ingest them.

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_ROW_COUNT procedure:

CALL CHECK_ROW_COUNT('<table_name>');
Copy

or

CALL CHECK_ROW_COUNT('<table_name>', <max_sys_created_on>);
Copy

Where:

table_name

Specifies the name of a table in the ServiceNow® instance.

max_sys_created_on

Specifies additional optional filter on maximal value of sys_created_on column. Only rows matching this filter will be counted. Default value of this parameter is NULL which means the filter won’t be applied. This parameter helps to compare only counts of records already ingested to Snowflake, without taking into account records recently created in ServiceNow® but not yet ingested into Snowflake.

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®.

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®V2 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(<from_timestamp>, <to_timestamp>);
Copy

Where:

from_timestamp

Specifies the start of dates range (in UTC timezone) for which data should be fetched.

to_timestamp

Specifies the end of dates range (in UTC timezone) for which 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('2024-02-05 10:00:00', '2024-02-10 22:30:00');
Copy

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

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 UPDATE_WAREHOUSE stored procedure.

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 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.