Prepare your Pre-import OT Worksheet Entry Review tool for Service Graph Connector import
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
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
| 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. |
| 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. |
| 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:71Note: 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:
|
| 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. |
|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.|
| 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:
|
| 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:
|
| 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:
|
| 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, SN998Note: 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:
|
| 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 |
| 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:
No assigned status.
OT device is absent in your facilities.
OT device is in maintenance and currently is off line.
OT device is in stock in your facilities.
OT device is installed in your facilities.
OT device is pending installation in your facilities.
OT device is pending repair but is not online yet.
OT device is retired.
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. |
| 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, DCSNote:
|
| 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:
Default state when records are imported into the staging table.
Cannot uniquely create a CI record in the CMDB.
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.
All identifiers are present and are ready for import.
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. |
| 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 |
- After populating the Microsoft Excel spreadsheet, save it in a known location for easy access to upload.
Parent Topic:Create an import task