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;
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>');
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');
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>');
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>');
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 |
---|---|
|
Name of the table. |
|
Unique identifier for the row in ServiceNow. |
|
Status of the ingestion of the row. |
|
|
|
|
|
|
|
Array of JSON objects that represent entries in the event log for the row with this 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:
|
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>');
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>);
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);
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();
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';
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';
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';
To determine the ServiceNow instance URL run the following command:
SELECT value:serviceNowUrl FROM GLOBAL_CONFIG WHERE key = 'connection_config';
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:
Ensure both event logs and raw data tables for this ServiceNow table are dropped.
Use the DELETE_TABLE procedure.
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';
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;
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;
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.