Prepare your Pre-import OT Worksheet Entry Review tool for Service Graph Connector import

  • Release version: Yokohama
  • Updated January 30, 2025
  • 13 minutes to read

    • Prepare your spreadsheet by positioning your existing data in the correct columns is crucial to the success of your upload.

    Before you begin

    Role required: ot_excel_import_user

    About this task

    Procedure

    1. Fill the following columns in the Microsoft Excel spreadsheet.
      Note:
      Column names cannot be changed. Extra columns can be added to the staging table. For more information about adding a new custom field mapping in the staging table, see Add a custom field mapping in the staging table for Service Graph Connector for Microsoft Excel.
      Refer to the following tables for guidance while filling in the spreadsheet. The spreadsheet contains many columns. The examples and field descriptions are split into multiple sections.
      • Filling in columns A through K
      • Filling in columns L through Y
      • Filling in columns Z through AI
      • Filling in columns AJ through AT
      • Filling in columns AU through BD
      • Filling in columns BE through BR
      • Filling in columns BS to BW
      • Filling in columns 1 to 8
      Table 1. Columns A through K
      Column Required column name Type Description and example
      A Device criticality string Measure of how critical, or important, the OT device is, based on its role. Examples:
      • High or Most critical
      • Medium or somewhat critical
      • Low or Less critical
      • None or not critical
      B Assigned to string Email address of the user that this OT device is assigned to. For example: bob@example.com
      C Backplane id string Unique ID that is used for the identification of the backplane and mapping to control modules. For example: BPSN123
      D Backplane name string Name of the backplane, if any, for the OT device. Examples: Backplane #51, PLC1 Backplane
      E Control module parent id string Unique ID that is used for the identification of the control modules to the parent control system backplane. For example: 482bb239-05e8-4bad-ba59-925eb87ff06e
      F Correlation id string Unique ID that is used for identification of the OT device. Enter the correlation_id a string. Examples: 482bb239-05e8-4bad-ba59-925eb87ff06e or 5123456. This column entry is required.
      • Each imported OT device must have a correlation_id that is unique.
      • The OT device data that you import normally originates in an external source system, which usually assigns a unique identifier to each record.
      G Custom field 1 string

      (Optional) Custom data for the OT device is stored in the Attributes field on the CI. You can use this column to associate free-form data to the OT device for categorization or other purposes. Examples: Refurbished, Used
      H Custom field 2 string (Optional) Custom data for the OT device is stored in the Attributes field on the CI. You can use this column to associate free-form data to the OT device for categorization or other purposes. Examples: Painting, Stamping
      I Custom field 3 string (Optional) Custom data for the OT device is stored in the Attributes field on the CI. You can use this column to associate free-form data to the OT device for categorization or other purposes.
      J Custom field 4 string (Optional) Custom data for the OT device is stored in the Attributes field on the CI. You can use this column to associate free-form data to the OT device for categorization or other purposes.
      K Custom field 5 string (Optional) Custom data for the OT device is stored in the Attributes field on the CI. You can use this column to associate free-form data to the OT device for categorization or other purposes.
      Table 2. Columns L through Y
      Column Required column name Type Description and example
      L Display name string Used to populate the display name of OT devices.
      M Equipment model entity path string Path of the equipment model entity that the OT device is mapped to.
      N Firmware version string Firmware version of the OT device, if any. For example: 12.0
      O First discovered datetime ISO-formatted timestamp of the first time that the OT device was first discovered on your network. For example: YYYY-MM-DD HH:MM:SS.
      P Hardware version string Hardware version of the OT device, if any. For example: 13.2
      Q Has module Boolean For control systems with modules, indicates that this system has modules. Examples: True, False
      R IO field device type string If this device is a field device, indicates if it is used for input, output, or both. Examples:
      • input
      • output
      • input_output

        The device acts as both input and output.
      S IP Address 1 string First IP address, if any, that is associated with the OT device. If there are multiple IP addresses, use the next IP address column (IP Address 2). Examples: 10.0.0.22, 10.0.0.12
      T IP Address 2 string Second IP address, if any, that is associated with the OT device. Examples: 192.168.100.1, 192.168.100.5
      U IP Address 3 string Third IP address, if any, that is associated with the OT device.
      V IP Address 4 string Fourth IP address, if any, that is associated with the OT device.
      W IP Address 5 string Fifth IP address, if any, that is associated with the OT device.
      X IP Address 6 string Sixth IP address, if any, that is associated with the OT device.
      Y IP Address 7 string Seventh IP address, if any, that is associated with the OT device.
      Table 3. Columns Z through AI
      Column Required column name Type Description and example
      Z IP Address 8 string Eighth IP address, if any, that is associated with the OT device.
      AA IP Address 9 string Ninth IP address, if any, that is associated with the OT device.
      AB MAC Address 1 string First MAC address, if any, that is associated with the OT device. If there are multiple MAC addresses, use the next Mac address column (MAC Address 2). Examples: 94:94:1d:01:6d:5f, cc:7c:4a:fb:20:71
      Note:
      For an OT device, you must create an entry in at least one of these three spreadsheet columns, all values in these columns must be unique for the spreadsheet:
      • MAC Address 1
      • Name
      • Serial number
      AC MAC Address 2 string Second MAC address, if any, that is associated with the OT device. For example: e5:4d:c8:36:b1:2d
      AD MAC Address 3 string Third MAC address, if any, that is associated with the OT device.
      AE MAC Address 4 string Fourth MAC address, if any, that is associated with the OT device.
      AF MAC Address 5 string Fifth MAC address, if any, that is associated with the OT device.
      AG MAC Address 6 string Sixth MAC address, if any, that is associated with the OT device.
      AH MAC Address 7 string Seventh MAC address, if any, that is associated with the OT device.
      AI MAC Address 8 string Eighth MAC address, if any, that is associated with the OT device.
      Table 4. Columns AJ through AT
      Column Required column name Type Description and example
      AJ MAC Address 9 string Ninth MAC address, if any, that is associated with the OT device.
      AK Manufacturer string Name of the manufacturer of the OT device. Examples: Rockwell Automation, Dell
      AL Memory card serial 1 string Assigned serial number of the first memory card, if any, that is installed in the OT device. If there are multiple memory cards, use the next memory card serial column (Memory card serial 2). Examples: MMC DA362131, MemSN123
      AM Memory card serial 2 string Assigned serial number of the second memory card, if any, that is installed in the OT device. For example: MemSN123
      AN Memory card serial 3 string Assigned serial number of the third memory card, if any, that is installed in the OT device.
      AO Memory size 1 string Size of the first memory card, if any, that is installed in the OT device. Examples: 256 GB or 1 GB
      AP Memory size 2 string Size of the second memory card, if any, that is installed in the OT device. Examples: 256 GB or 1 GB
      AQ Memory size 3 string Size of the third memory card, if any, that is installed in the OT device. Examples: 256 GB or 1 GB
      AR Memory type 1 string Type of memory card that is installed in the OT device. If there are multiple memory cards, use multiple columns. For example: RAM
      AS Memory type 2 string Type of memory card that is installed in the OT device. Examples: RAM
      AT Memory type 3 string Type of memory card that is installed in the OT device.
      Table 5. Columns AU through BD
      Column Required column name Type Description and example
      AU Model number string Manufacturer's model number for the OT device. Examples: ThinkServer TD230, XPS 15z
      AV Module type string Description of the function of the control module, if this device is one. Examples: Input, Output
      AW Name string Host name of the OT device, usually as part of the FQDN. Examples: PLC1, Door Assembly HMI, and Robot Control Module.
      Note:
      For an OT device, you must create an entry in at least one of these three spreadsheet columns. All values in these columns must be unique for the spreadsheet:
      • MAC Address 1
      • Name
      • Serial number
      AX Operating system string Operating system, if any, that is installed on the OT device. Examples: Linux Fedora, Windows 10, Windows 2000, Mac OS 8.
      Note:
      For an OT device, you should create entries in the following spreadsheet columns, even though they are not required:
      • Type
      • If available, Operating System
      • If available, Firmware version
      AY OS version string Reported version of the operating system, if any, that is installed on the OT device. Examples: 10.0, 13.5.2
      Note:
      For an OT device, you should create entries in the following spreadsheet columns, even though they are not required:
      • type
      • If available, os_version
      • If available, firmware version
      AZ OT Staging Task string Tasks created to remediate invalid records on the staging table.
      BA Purdue level string Assigned Purdue level for the OT device. Assigning a Purdue level ensures that the Discovery for the Operational Technology function properly locates each item at the correct ICS level and produces accurate Discovery results. Examples: 1, 2, 3
      BB Rack number string Rack where the control module is mounted. Examples: 1, 2, 3
      BC Serial number string Assigned serial number, if any, for the OT device. Examples: SN545, SN998
      Note:
      For an OT device, you must create an entry in at least one of these three spreadsheet columns. All values in these columns must be unique for the spreadsheet:
      • MAC Address 1
      • Name
      • Serial number
      BD Serial number type string Normally set to the value of "system," but it could be a different type of serial number. For example: uuid
      Table 6. Columns BE through BR
      Column Required column name Type Description and example
      BE Short description string Short description of the OT device. Examples: HMI for the Door Painting Cell, Controls the door assembly robot.
      BF Site string The equipment models start at the site level and contain a detailed hierarchical structure that describes each industrial site.

      For more information, see ISA-95 equipment model.
      BG Slot number string For a control module, indicates the slots that this device occupies in the chassis of the control system. Examples: 1, 2
      BH Software install date 1 datetime

      Date that the application software was installed on the OT device. If there are multiple dates, use multiple columns.

      Use only UTC format for the date.

      For example: YYYY-MM-DD HH:MM:SS
      BI Software install date 2 datetime Date that the application software was installed on the OT device. If there are multiple dates, use multiple columns.

      Use only UTC format for the date. For example: YYYY-MM-DD HH:MM:SS
      BJ Software install date 3 datetime Date that the application software was installed on the OT device. If there are multiple dates, use multiple columns.

      Use only UTC format for the date. Example: YYYY-MM-DD HH:MM:SS.
      BK Software installed 1 string Name of the application software, if any, that is installed on the OT device. If there are multiple names, use multiple columns. For example: Rockwell HMI Vision
      BL Software installed 2 string Name of the application software, if any, that is installed on the OT device.
      BM Software installed 3 string Name of the application software, if any, that is installed on the OT device.
      BN Software version 1 string Reported version of the application software, if any, that is installed on the OT device. If there are multiple versions, use multiple columns.

      For example: v1.2 or v2011 SP3 HF2 or 4.54.32145
      BO Software version 2 string Reported version of the application software, if any, that is installed on the OT device.

      For example: v1.2 or v2011 SP3 HF2 or 4.54.32145
      BP Software version 3 string Reported version of the application software, if any, that is installed on the OT device.

      For example: v1.2 or v2011 SP3 HF2 or 4.54.32145
      BQ Status string
      Status of the OT device:
      --None--
      No assigned status.
      Absent
      OT device is absent in your facilities.
      In Maintenance
      OT device is in maintenance and currently is off line.
      In stock
      OT device is in stock in your facilities.
      Installed
      OT device is installed in your facilities.
      Pending Install
      OT device is pending installation in your facilities.
      Pending repair
      OT device is pending repair but is not online yet.
      Retired
      OT device is retired.
      Stolen
      OT device has been stolen.
      Note:

      The values in this field are mapped to Life Cycle Stage and Life Cycle Stage Status fields on the CI form.
      BR Support group string Name of the primary support group for this OT device. Examples: Door Support, Corporate IT Support.
      Table 7. Columns BS to BW
      Column Required column name Type Description and example
      BS Transformed name string

      Users must not fill this column.

      By default, the transformed name value is populated using transformed column system properties.

      A user cannot edit the Transformed name.

      For system properties, see Review the system properties used by the Service Graph Connector for Microsoft Excel.
      BT Type string Type of OT device/configuration item (CI). Examples: PLC, DCS
      Note:
      • For a listing and explanation of valid CI types, see Operation Technology (OT) extension classes.
      • For an OT device, you should create entries in the following spreadsheet columns, even though they are not required:
        • type
        • os_version
      BU Validation comments string

      Users must not fill this column.

      By default, Validation comments are populated after the validations are run on the staging table records that are imported from excel.

      Validation comments are not updated when records are imported.

      User cannot edit the Validation comments.
      BV Validation state string

      Users must not fill this column.

      By default, the validation state is populated when the data is imported in the staging table.

      Status of the OT device:
      Pending validation
      Default state when records are imported into the staging table.
      Invalid
      Cannot uniquely create a CI record in the CMDB.
      Partially valid
      One of the Transformed Name, MAC Address 1, and Serial number has no value. All the other fields (correlation id, control module parent id) have values.
      Valid
      All identifiers are present and are ready for import​.
      Imported
      Completed the import of the data from the staging table to the Import set table.

      User cannot edit the Validation state.
      BW Vendor string Name of the vendor of the OT device.
      Table 8. Columns 1 to 8
      Column Required column name Type Choice columns if applicable Description and example
      1 Backup configuration status choice list Backup Enabled, Backup Disabled, Unknown, Not Applicable, Planned, Not Planned Indicates whether the CI has been configured in the backup service or appliance with relevant policies.

      Examples: Backup Enabled
      2 Backup execution mode choice list Manual, Automatic, Manual or Automatic, Unknown Indicates whether the backup is configured to run automatically on a periodic basis, or if it is manually executed on an as-needed basis.

      Examples: Manual, Automatic
      3 Backup source id string Backup service source identifier for a device, which identifies the device in external or internal backup services. Backup source id can include host_id, vcenter_id, instance_id, db_id.

      Examples: AdvWrks2008R2Backup
      4 Last backup attempt glide_date_time Date and time of the last backup attempt made for a device.

      Examples: 2024-06-18 09:53:37
      5 Last successful backup glide_date_time Date and time of the last successful backup made for a device.

      Examples: 2024-06-18 09:53:37
      6 Backup recovery point objective glide_duration Represents the amount of time that can elapse between backups and the amount of data lost.

      Examples: 90 12:00:00
      7 Backup managed by string Email ID of the user responsible for managing the backup.

      Examples: firstname.lastname@example.com
      8 Backup managed by group string Name of the primary support group responsible for managing the backup.

      Examples: App Engine Admins
    2. After populating the Microsoft Excel spreadsheet, save it in a known location for easy access to upload.