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

With the Service Graph Connector for Microsoft Excel, add a custom field to the staging table and map the custom field to the configuration item (CI) field.

Avant de commencer

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 change the script include. Can add class or field mappings and change the ETL.
• cmdb_inst_admin - Can only add new class or field mappings and change the ETL.

Procédure

1. Add custom columns to the Staging [sg_ot_excel_staging] table.
2. Navigate to All and in the Filter field, add sg_ot_excel_staging.list.
3. On your keyboard, press enter.
4. If needed, manually create records by selecting New.
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’s 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.

   

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.
    
    
12. In the 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.
    
    
    If the column isn’t 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 the field is mapped, the Comments field on Hardware child classes is updated if the value for the Comments column in the staging table for that row isn’t empty.

    If you're adding a mapping for a new field that isn't present, or for a field that isn't specific to the Hardware cmdb_ci_hardware class and instead is a field in the Operational Technology (cmdb_ci_ot) class, you can 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.
       
       
19. In the Add Attribute dialog box that appears, from the Attribute list, select Comments.
    
    
20. Select Save.
    A new field named “Comments” is displayed.
    
    
21. From the Data pane, drag the data pill to the Comments Source Column field.
    
    
    You can also select the source column icon ( ) to select the data-mapping field for it.
    The Source Column includes the 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.
    
    
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.
       
       
    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.
       
       
    6. Select Perform Rollback.
       
       
    7. If the ETL isn’t activated, select Activate.
    The new column field mapping is successfully added and verified.