Test the Excel Service Graph connector

  • Release version: Xanadu
  • Updated August 1, 2024
  • 3 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Test the Excel Service Graph connector

    This guide assists ServiceNow customers in troubleshooting and resolving common issues encountered when importing Operational Technology (OT) devices or data using the Excel Service Graph connector (SG-OT Device Excel Import ETL). It outlines key steps and checks to ensure data loads correctly and validations run as expected within the ETL guided setup and staging tables.

    Show full answer Show less

    Troubleshooting and Validation Guidance

    • Loading Data Issues: If data fails to load during the ETL guided setup, test loading data directly from the SG-OT Excel Import data source using options like "Test Load 20 Records" or "Load All Records" before retrying the ETL load.
    • Partial Payload Entries: These occur when required device fields are missing or control modules lack associated parent devices. Verify that device types and control module parent ID fields are properly populated in the staging table.
    • Incomplete Payload Entries: Usually caused by missing unique identifier fields. Note that validations apply to all records in the staging table, not just the current import batch. Users should select "ALL" in the staging table view before running validations.
    • Timestamp Format: Date/time entries in the staging table must use UTC format (YYYY-MM-DD hh:mm:ss) to avoid empty timestamps.
    • Validation State Visibility: After running validations, manually refresh the page to see updated validation states.
    • Validation State Not Updating: Changes only trigger validation states to "Pending Validation" when specific key fields (e.g., MAC address, Serial Number, Name, Correlation ID, Type, Rack Number) are modified.
    • System Properties Impact: Updates to system properties affecting transformed name computation must be made before importing data; changes post-import will not reflect in staging table records.
    • Validation Errors When Selecting Few Records: Validations run on all staging table records regardless of selection; do not select single records expecting isolated validation.
    • Duplicate Record Detection: Staging table validations do not detect duplicates against existing CMDB data; they only validate staging table data.
    • Site Name Not Displayed: Only existing CMDB site records with matching entityname values are recognized and displayed after import. Mismatches result in empty site fields.
    • No Records Imported on Load All: Possible reasons include no records in staging or records not being in a valid or partially valid state.

    Log Access and Debugging

    For detailed error investigation, access all Excel Service Graph connector logs via All > System logs > System log > All, and filter messages containing "SGOTExcel" to review execution steps and identify issues.

    The troubleshooting actions can help resolve common issues when importing your Operational Technology devices or data. Access the System Log to troubleshoot for these errors.

    These logs can be used to debug any issues or to find the SGC steps are executed properly.

    Issue Solution

    If there is an issue while loading data for SG-OT Device Excel Import ETL on ETL guided setup

    		 From the Related links list, try loading the data.
    1. Navigate to the SG-OT Excel Import data source and select Test Load 20 Records or Load All Records.
    2. Return to ETL and try loading the data again.
    If there are entries in the Partial payload tab after test running the Service Graph connector from ETL guided setup Due to the following conditions:
    • Missing values for the required fields of an device.
    • Control Modules without a parent device associated with it - Check that the type of the device and control module parent id field is filled properly in the staging table.
    If there are entries in the Incomplete payload tab after test running the Service Graph connector from ETL guided setup Due to the missing values for fields that are used uniquely to identify an device.
    After easy import is executed, users may see records that are imported from the current run. When validations are executed, they are run on all records of the staging table not only on records from the current import run. Before running the validations, the user must click ALL on the list view breadcrumb of the staging table.
    If the timestamp column appears empty on the staging table The user must use the UTC format (YYYY-MM-DD hh:mm:ss) to enter the date and time.
    If the validation state update on records is not visible after click Run Validations button. The user must manually refresh the page.
    When the user changes the existing data of the records in the staging table, the validation state is not set to Pending Validation. The validation state is set to Pending validation, when the following attributes are changed:
    • Identifier fields (Mac-address (1-9))
    • Serial Number
    • Name
    • Correlation ID
    • Type
    • Control module parent correlation ID
    • Fields used in transformed name computation
    • Rack Number
    • Slot Number

    For more information about the system properties, see System properties that impact SG-OT Device Excel Import processing.
    After records are imported into the staging table, the updates done in the system properties related to transformed name computation are not reflected in the staging table records. Change the system properties before importing the data into the staging table.
    If the multiple error banners are shown when users select few records and click Run Validation. The validations are executed for all the records available in the staging table.

    Do not select the single record for validation.
    If the duplicate records exist in CMDB, the staging table does not detect it as duplicate.

    The validations are executed only for the data available in the staging table.

    The validations are not executed for the data available CMDB.
    The site name is provided in the spreadsheet or staging table, but not shown on the OT devices after the import of the spreadsheet.

    Only the existing site records in the CMDB are considered.

    The entity_name for the site (ISA Equipment model entity) must match the value provided in the site column in the excel or staging table.

    If the entity_name for the site does not match, the value is set to empty.
    When users click Load All the records on the data source, no records are imported into the import set.

    No records are available in the staging table.

    The records are present in the staging table, but the records are not in Valid or Partially valid state.

    Access all the logs related to Excel SGC in All > System logs > System log > All. Search for the string SGOTExcel in the Message column.