Add a custom field mapping in the staging table for Service Graph

  • Release version: Washingtondc
  • Updated February 7, 2024
  • 3 minutes to read

    • Add a custom field to the staging table and map the custom field from the staging table to the CI field through Excel SGC.

    Before you begin

    To configure the form layout, see Configure the form layout.

    To create a custom field on the staging table, see Add and customize a field in a table.

    Roles required:
    • admin - Can make changes in the script include, and add class or field mappings and make changes in the ETL.
    • cmdb_inst_admin - Can only add new class or field mappings and make changes in the ETL.

    Procedure

    1. After a custom column is created in the staging table, navigate to All > Industrial Workspace Admin > OT Manager > Import OT Devices - Staging table.
    2. Download the Excel template.
      For more information, see Download Excel template.

      If records are present in the staging table, you can edit the value for the column in the existing records.

    3. Prepare the Excel template.
      For more information, see Preparing your Pre-import OT worksheet entry review tool for Service Graph Connector import.
    4. Import the Excel template.
      For more information, see Import Excel template.
    5. Navigate to All > Industrial Workspace Admin > OT Manager > Import OT Devices - Script Includes and select the SGOTAssetImportExcelConstants script include.
    6. In the script, update the new column name from the staging table, and the new ETL column name inside the SGOTAssetImportExcelConstants.importSetColumnsVsStagingColumnsMap object in the format “<ETL Column Name>”: “<Column Name from staging table>”.

      In this example, the “u_my_custom_field” before the colon ( : ) indicates the ETL column name (shown as a column in the ETL preview step), and the “u_my_custom_field” after the colon indicates the column name in the staging table.

      Make sure that there is a comma (,) added at the end of the line above the new line. In this example, a comma is added after the "custom_fields": "custom_fields" line.

      Updated ETL column name with SGOTAssetImportExcelConstants.
    7. Select Update to save your changes.
    8. Navigate to All > Configuration > IntegrationHub ETL.
    9. Select the CMDB Application: SG-OT Excel Import ETL.
    10. If the Invalid Mapping Data Detected page is displayed, select Close.
    11. From the ETL Transform Map Assistant, in the Specify Basic Details section of the guided setup, select Import Source Data and Provide Basic Details.
      Select step 1: Provide basic information for the ETL Transform Map.
    12. In the Sample Import Set field, select Auto-pull a new import set.
      Sample Import Set field, select Auto-pull a new import set.
    13. Select Save.
      The basic information saved successfully banner is displayed.
    14. Select Mark as Complete.
    15. From the ETL Transform Map Assistant page, in the Prepare Source Data for Mapping section, select Preview and prepare data.
      Preview and prepare data.
      If the column is not visible, repeat the steps 11 through 14.
    16. Select Mark as Complete.
    17. From the ETL Transform Map Assistant page, in the Map Data to CMDB and Add Relationships section, select CMDB Classes to Map Source Data.
    18. Map the column to the target class and attribute.

      For example, the “comments” field is present on the Hardware cmdb_ci_hardware class. After this field is mapped, the “comment” field on Hardware child classes is updated as well, as long as the value for the “comments” column in the staging table for that row is not empty. If you're adding a mapping for a new field that is not present, or for a field that is not specific to the Hardware cmdb_ci_hardware class, but instead is a field in the Operational Technology (cmdb_ci_ot) class, you would add the field mapping in the Operational Technology (OT) 1 stub.

      1. Add a field mapping to the Hardware 1 class.
      2. For the Hardware 1 class, select Edit Mapping.
        Select CMDB Classes to Map Source Data
    19. In the Add Attribute dialog box that appears, from the Attribute list, select Comments.
      Map Hardware 1 class - Add Attribute dialog box.
    20. Select Save.
      A new field named “Comments” is displayed. You may need to scroll to see the field.
      New comments field added.
    21. From the Data pane, drag the data pill to the Comments Source Column field.
      Map to hardware.
      You can also select the source column icon ( Source column icon. ) to select the data mapping field for it.
      The Source Column includes the data pill.
      Source Column field filled with a data pill.
    22. Navigate back to the CMDB Classes to Map Source Data of the Map Data to CMDB and Add Relationships section in the ETL Transform Map Assistant page.
      Navigate back to the CMDB Classes to Map Source Data page.
    23. Select Mark as Complete.
    24. Follow these steps to verify the new field mappings.
      1. Navigate to the home page of the ETL Transform Map Assistant.
      2. In the Preview Sample Integration Results and Schedule Import section of the guided setup, select Test and Rollback Integration Results.
        ETL Transform Map Assistant - Test and Rollback Integration Results.
      3. Select Run Integration.
      4. After the run finishes successfully, confirm that the Comments field of the CI that you provided a comments value for is updated in the staging table.
      5. Select Mark as Complete.
        After the Test and Rollback Integration Results successfully runs, confirm that the Excel sheet is updated.
      6. Select Perform Rollback.
        Select Perform Rollback.
      7. If the ETL is not activated, select Activate.
      The new column field mapping is successfully added and verified.