The Connector Solution (Data Connector) is a component of the Symantec Management Platform that lets you transfer data between external data sources and the configuration management database (CMDB). The ability to transfer data lets you leverage the data that already exists in the CMDB or other applications. Data transfers can be scheduled, so updates can be regularly, and automatically made to keep data current. Data Connector supports many data formats: OLEDB, ODBC, LDAP, XML, and CSV. Data Connector transfers data through the use of data source definitions and data transfer rules.
In my example that follows, I will be importing a Microsoft Excel Spreadsheet (.xlsx) using OLEDB as the data source to the CMDB.
Create a Data File (Excel Spreadsheet)
First, a data file must be created populated with values to be imported. It is up to you on how to do this, whether this is manually entered data or data created through an external process. If a data file is to be used, the first row can be the column names, which are not required but are recommended to enable the Import Rule to more easily match up the columns to the data classes. If these are the same names as data classes, the Import Rule will automatically select these during the import. In this example, I will use a Microsoft Excel Spreadsheet of new computers.
Create Data Source for MS Excel Spreadsheet
Now that we have an Excel spreadsheet or data file to be import, create a Data Source to read the Microsoft Excel spreadsheet. The following steps details how to create a new Data Source using the connector:
1. Copy the MS Excel Spreadsheet you created to the Notification Server.
2. Open the Symantec Management Console and select Setting -> All Setting -> Notification Server -> Connector
3. Expanded the Connector container
4. Right click on the container called Data Sources and select New -> OLEDB Data Source and enter an appropriate name such as “MS Excel Spreadsheet of Computers Data Source”
5. Select the OLEDB Data Source Type as MS Excel 2007 (*.xlsx). Refer to Figure 2 below.
6. For the MS Excel file, browse and find the spreadsheet that contains the new computers to be imported. Refer to Figure 2 below.
7. After the MS Excel file is selected, select the Worksheet name. Refer to Figure 2 below.
8. Under the Import/Export Options, check the box to Allow Import as shown in Figure 2 below.
9. Also make sure the Pre-process Import Data is checked.
10. Click on the View import Data … button to validate that you can read the source spreadsheet (see Figure 3 below)
11. Click Save Changes to save the Source Import and Close the Data Source.
Create Import/Export Rule to Import Computers
Now that we can read the Excel spreadsheet or data file, we need to create an Import/Export Rule to import the new computer resources into the CMDB using the Microsoft Excel spreadsheet as the data source. The following steps details how to create an import using the connector:
1. Right click on the Import/Export Rules and select New -> Resource Import Export Rule.
2. Name the rule as needed such as “Import New Computers from Excel”
3. The first configuration step is to choose the data source that has been previously defined (as in the steps above). If you used the name of the data source that I chose, the select the data source called “MS Excel Spreadsheet of computers Data source” from the Data Source dropdown menu.
4. Choose the data source
5. After selecting the data source the base import rule will appear
6. To configure the import rule in the section Column Mappings:
a. For the Resource Type: Select Computer and the page will refresh and all the data classes and association mapping for the computer will appear. These fields will be used to properly configure the imported computer resource. Ensure that computer is picked; in general, do not use a generic or wrong type for a specific type, such as "Asset" instead of "Computer". Many user interface elements, such as drop-down lists, will display a pop-up help window when the mouse is hovered over them.
b. For the Resource Lookup Key, select Resource Name from the dropdown menu.
c. The page with refresh showing the Name dropdown menu beside the Resource Lookup Key will appear. Select the Computer Name column from spreadsheet in the dropdown menu.
Note:
The Resource Name field will automatically be set to the Computer Name column in the data file. This will usually need to be set to be the correct column. The "Resource name" drop-down list value is generally automatically set based on the "Resource lookup key" and Name drop-down list values. In some cases this is optional and can be left blank but in general, it is usually best that this is filled out to ensure data is brought in and configured correctly. When this field is automatically set, the user does not need to manually assign a data class for the resource's Name.
d. In the "Data class mappings" section, set any data classes to match their appropriate columns from the Excel Spreadsheet. If the spreadsheet uses the column names in the first row, and if the column names match data class names, these will automatically be set. For the example notice that the Serial Number is automatically mapped to the Serial Number column in the spreadsheet because they are the same name. If the expected data classes are not present, click on the "Select data classes" link to manually pick and choose these.
e. In summary, configure the rule using the following setting (see Figure 6 below):
i. Resource Type: Computer
ii. Resource Lookup Key: Resource Name
iii. Name: Computer Name (column from spreadsheet)
iv. Removed Assets: Left Unchanged in CMDB
v. Resource Name: Computer Name (column from spreadsheet)
vi. Serial Number: Serial Number (column from spreadsheet)
7. Click Save the Changes to the Import Rule
8. In the Run History section you can test the import rule before executing the real thing. This step is highly recommended. Find and press the Test Rule button. The Test Rule will show you the result. Figure 7 shows the Connector Rule Run Status dialog that will appear when the Test Rule completes. If you want more details click View Log.
9. The Test Rule run is a very important step to help find and fix errors in the source spreadsheet. The Test Rule can be executed multiple times and each time the log file is saved. Verbose logging is selected by default by can be unchecked.
10. The Import Rule can be scheduled or run by command by selecting the Run Now button. As in the Test Rule the import will gave you a status of imported machines. When you are ready to import the computers, run the import rule once and check the log for errors.
11. In the Run History for each occurrence there is a log and can be accessed via the View Log button. It will show a log.
The new computers have been added to the CMDB and the Notification Server. You verify that the computers have been imported into the Notification Server by selecting in Manage -> Computers in the Symantec Management Platform.
Conclusion
The Connector Solution can save an organization hundreds of hours on manually entering data into the CMDB directly and help pull information from a variety of sources. Using a Microsoft Excel Spreadsheet as in this example is just one of many solutions to import new data into the CMDB and Notification Server.