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

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:

Ensure both event logs and raw data tables for this ServiceNow® table are dropped.
Disable the ServiceNow® table.
Use the DELETE_TABLE procedure.
Enable the ServiceNow® table.

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.

Determining the reason for missing columns in flattened views¶

The connector creates flattened views in the destination schema based on the ServiceNow® metadata. There are several reasons why a column can be missing on the Snowflake side.

Checking if column metadata is present in Snowflake¶

To check if column metadata is present in the sys_dictionary table on Snowflake, execute the following query:

SELECT * FROM <dest_db>.<dest_schema>.sys_dictionary__view WHERE name = '<table_name>' AND element = '<column_name>';

Copy

If the table you’re investigating has parent tables (inherited from another table in ServiceNow®) and the column you are looking for was added to the parent table, you should use the parent table name instead.

To list all the tables from which the table you’re interested in inherits, please use the following query:

SELECT
    sys_id,
    name,
    PARSE_JSON(super_class):value::string AS super_class_sys_id
FROM <dest_db>.<dest_schema>.sys_db_object__view
START WITH name = '<table_name>'
CONNECT BY sys_id = PRIOR super_class_sys_id;

Copy

If rows are returned, metadata for the column was correctly ingested into Snowflake but the view has not yet been refreshed. Check the status and if:

the view was refreshed recently but the column is still not present, please contact support.
the view was not refreshed yet, wait for the next ingestion schedule.

If an empty result is returned, it means that the connector didn’t ingest metadata for this column yet. You need to validate on the ServiceNow® side if the record is visible to the connector and has correct timestamp.

View refresh status¶

To validate when the views for a given table were last refreshed and if the operation was successful, execute the following query:

SELECT flattened_views_status, flattened_views_last_updated FROM tables_state WHERE table_name = '<table_name>';

Copy

If the last refresh failed, you may want to query event table and look for errors reported by the connector.

View ServiceNow® column metadata availability¶

It’s possible, that the reason for missing columns in the flattened view is that column metadata cannot be ingested by the connector. This may be caused by ACLs preventing the row in the sys_dictionary table from being returned by the Table API. Another possible reason is a past timestamp value in the sys_updated_on column. It can also be the case that the column/table definition was imported from a different ServiceNow® instance. To determine if the connector can access column metadata execute GET request to the following endpoint:

https://<your_instance_name>.service-now.com/api/now/table/sys_dictionary?sysparm_query=name=<table_name>^element=<column_name>

Copy

If an empty result is returned the connector cannot access the column. The column may be protected by an ACL or not present.

If a column definition was returned, examine the value of the sys_updated_on field. Confirm the date matches the expected time when the column was added to the table. If it was imported from another instance it may show the point in time when the column was created. The CDC (incremental updates) mechanism in the connector may not notice that the record dated in the past was added. In this case, trigger a reload of sys_dictionary table.