Create a Data Connector import rule that creates or updates resources

Products

• IT Management Suite

Issue/Introduction

How to create a Data Connector import rule that creates or updates resources

Environment

ITMS 8.x

Resolution

This article describes how to create and configure a Data Connector import rule that creates or updates resources. This requires the user's data and a Data Connector data source. As the complete instructions for how to create an import rule are lengthy, this article breaks these down for conciseness into individual sections. Because of this, references to related articles are withheld in the individual sections but later provided in the Related Articles section. For more information on how to use Data Connector Connector or how to create data sources, refer to the following article:

181160 Using Data Connector to import data into the Symantec Management Platform database

Part 1: Assign the data source to use and configure data filtering.

1. In the Symantec Management Platform Console, click on the Settings button > All Settings.
2. Click on to expand the folders Notification Server > Connector > Import/Export Rules.
3. Right-click on the Import/Export Rules folder > New > Resource Import Export Rule. (Data Connector can also import data in other ways, such as to filters or organizational groups, however, these are not discussed in this article.)
4. Click on the New Import Export Rule name and change this to a new name. For example "Import rule to import computers from a .CSV file".
5. Click on "Add description", if desired, to add an optional description.
6. Click on the "Data source" drop-down list and select the data source to use, such as previously created from one of the data source articles referenced in HOWTO79380. For example, "Data source used for importing computers from a .CSV file". Note: Every time the user changes the "Data source" selection (or the resource type in Part 2, step 1), the resource type, data classes, and associations are cleared and reset to their defaults, losing any mappings that the user may have previously configured. Ensure that the correct data source is selected, therefore, before further configuring the import rule.
7. If data is to be filtered using a basic SQL script:
   
   a. In the "Data filter" field, type the basic SQL script to use. For example:
   
   "[date] < '09-05-2012'
   
   If the [date] column specified by the data source has a value that is less than September 5th, 2012, this record will be provided to the import rule to import. If not, it will not be provided to the import rule and will therefore not be imported.
   
   b. Click on "Show data". If the data no longer appears as expected, correct the basic SQL script until it does.
   
   Note: When setting up the import rule, it is not recommended to immediately set up its data filtering SQL script as that may further complicate the configuration. This can be skipped and later added after the import rule is configured and working. 

Part 2: Configure the resource type and resource lookup key.

1. Click on the "Resource type" drop-down list and select the resource type that will be imported. For example, select Computer for computers. Notes: (1) More generic resource types can be selected, such as Asset or Resource, however, these are not generally recommended unless the user is specifically bringing in these parent resource types or wants to make the import more generalized to accommodate multiple resource types. For the latter, it is recommended to use a separate data source and import rule for each resource type to be brought in. (2) As previously mentioned, every time the user changes the "Resource type" selection, the data classes and associations are cleared and reset to their defaults, losing any mappings that the user may have previously configured. Ensure that the correct resource type is selected, therefore, before further configuring the import rule. Also, if the import rule is saved or the user goes elsewhere in the Console before all mappings are completed, this will also reset the data classes and their associations.
2. Click on the "Resource lookup key" drop-down list and select the key to use. For example, Serial Number. Tip: The resource lookup key works best when the key has all unique values for each record. As some of the listed keys are not necessarily unique, such as Resource Name, try to pick one where it is unique and all current and imported records have unique values for this field.
3. Click on the drop-down list that then appears directly to the right of the "Resource lookup key" drop-down list and select the column name from the data that contains the key values to use. For example, select Serial Number. Notes: (1) By default, the first column name from the data is automatically selected in this drop-down list, but this is most likely not the correct column name to use. (2) Custom resource lookup keys can be created and used. For information on how to create custom resource lookup keys, refer to the following article:
   
   Creating a Resource Lookup Key

Part 3: Decide if resources will be created, updated, or deleted by the import and assign the resource name field.

1. When resources are imported using a Data Connector import rule, they can be created, updated, or even deleted. Enable or disable these as desired. The following describes how these options work:
   
   "Create resources if doesn't exist" - This is enabled by default. If the resource lookup key based on the assigned column it uses cannot find the key, for example, if a specific serial number isn't found in any current record, then the import rule will create a new record. Turn this off if it is not desired to create new resources.
   
   "Allow import resources of child resource types" - This is disabled by default and is not commonly used. This can be enabled if importing generic resource types such as Asset or Resource.
   
   "Update existing resources" - This is enabled by default. If the resource lookup key based on the assigned column it uses finds the key, for example, if the serial number is found for a current record, then the import rule will update that record. Turn this off if it is not desired to update current resources.
   
   "Removed assets" - The default is "Left unchanged in CMDB". Only change this if it is desired to delete records or change their status that was previously imported by this same import rule and its data source, which now are no longer part of the data. For example, yesterday's import brought in two computers, PC1, and PC2. Today's brought in PC3 and PC4 but did not include PC1 or PC2 in the data to update. If this setting is anything but "left unchanged", it will either delete or change the status of PC1 and PC2 as they were not part of today's data.
    
2. Click on the "Resource name" drop-down list and select the column name to use to populate the resource's name field. For example, select Name. Tip: If any resource lookup key value other than Resource Name is selected from Part 2, step 2, this field requires being set. If Resource Name was, however, picked in Part 2, step 2, this field automatically is set to that and cannot be changed. Be sure to therefore set this to the correct column name that contains the values to set to the resource's name.
3. The import rule so far should now look similar to the following example:
   
   

Part 4: Configure data classes to populate. 

1. In the "Data class mappings" section, decide which data classes will be populated by the data. The term "data class" refers to a field, such as a serial number, comment, etc., that is directly part of the resource being imported. When the resource type is picked in Part 2, step 1, this automatically adds the default data classes. The user then decides which data classes to populate, based on what data is being imported. For example, if a computer's serial number is to be populated, it is expected that the data contains a Serial Number column with valid serial number values. As this is an optional field, the user can decide to or not to assign it for import while creating the import rule. Tips: (1) If the data column names match a data class name, the data class mappings will automatically use this column name. For example, if a column name is Serial Number, this is the same name as the data class Serial Number and will automatically be used. (2) If the user wants to import resources but isn't sure what data classes to use, one way of determining this is to make an export rule for the resource type. This will automatically fill out all of the data classes used by that resource type. When run, assuming that there is data for the resource type in question, the output file will then include column names and "sample" data. The user can then evaluate these data classes to determine how they want to set up an import rule.
2. It is not common to use non-standard data classes that aren't default. However, if these are needed, such as when using a custom data class (which will never appear in the data class mappings unless manually added), click on the "Select data classes" link and then select the non-standard or custom data classes to use.
3. Some data classes require a foreign key. A foreign key refers to where the data class has a link (similar to how an association works) to another resource type. For example, Cost Center Ownership requires both the Owner data class to be set, which is a foreign key reference to a Cost Center resource, and also the Ownership Percentage data class, a numerical value of 1 to 100. When setting a foreign key, the user must specify also its Update Mode using the provided drop-down list. This provides three different update modes:
   
   * Update. This should generally be used for any data class that is single-rowed. For example, Asset Owner is usually a single rowed data class, but can have multiple ownerships if this has been changed (not default). If this is used with multi-rowed data classes, this could result in duplicates or incorrect values.
   
   * Append. Append adds additional values after the current value for multi-rowed data classes. This should not be used for single-rowed data classes. For example, Asset Owner is normally a single rowed data class. Setting the Update Mode to Append would attempt to add a second (and so on) Asset Owner. This will result in no changes to the current Asset Owner unless the Asset Owner is set to have multiple ownerships, and then a second (and so on) Asset Owner will then be added. For multiple line items, such as for purchase orders, this should be used.
   
   * Replace. This replaces the current value with the new value. This will work similarly to Update and can be used for a single rowed data class, such as Asset Owner. 
   
   Note: An import rule does not create foreign keys when importing a resource type, such as Computer, but only links the imported record to those foreign keys that already exist.

Part 5: Configure associations and/or reverse associations to populate.

1. In the "Association mappings" and "Reverse association mappings" sections, decide which associations or reverse associations will be populated by data. The term "association" refers to a link to another resource type. For example, Location is an association reference to a Location resource. The term "reverse association" refers to the linked resource's association back to the "parent" resource, if applicable for that resource type, which is not commonly set by an import rule. When the resource type is picked in Part 2, step 1, this automatically adds the default associations and reverse associations. Tip: Unlike data classes, associations and reverse associations must be manually populated by the user. Note: An import rule does not create associations or reverse associations when importing a resource type, such as Computer, but only links the imported record to those associations or reverse associations that already exist.
2. It is not common to use non-standard data associations or reverse associations that aren't default. However, if these are needed, such as when using a custom association or reverse association (which will never appear in the association mappings or reverse association mappings unless manually added), click on the "Select associations classes" link and then select the non-standard or custom associations or reverse associations to use.
3. To configure an association or reverse association, click on its "Lookup key" drop-down list and select Resource Name.
4. In the "Key column mappings" for the association or reverse association, click on its Name drop-down list and select the column name, this must be added too for data classes.
5. Click on the "Save changes" button. Note: All data classes and associations that aren't populated by the user will be removed once the import rule is saved. When the user next comes back to the import rule, only those data classes, and associations that were populated remain. If the user needs to re-add removed data classes or associations, use the "Select" links to manually re-add these as necessary. If many were removed, it might be easier to change the resource type, then re-select the desired resource type, to reset the default data classes and associations.
6. The import rule should now look similar to the following completed example after the user leaves and then comes back to the import rule:

Note: For multi-rowed data classes, such as cost items, special consideration is needed in the data to ensure that each record is brought in for the data class. 

In the "Associations mappings" and "Reverse associations mappings" sections:

a. Click on the "Lookup key" drop-down list for any association or reverse association mappings to set. Use the value selected for the "Resource lookup key" from step 6b. For example, set the Location association mapping's "Lookup key" to Resource Name. Tip: The "Lookup key" is not the column that will be used for the association, this is the "Key column mappings" value from step 8b. Do not set both of the "Lookup key" and "Key column mappings" to be the same value (which normally cannot be done anyway) as this will result in a failed lookup assignment.
b. Click on the "Key column mappings" drop-down list and select the column to use. For example, set the Location association mapping's "Key column mappings" to "Location", which is a column from the data file. Another example, is if Users are imported, Asset Owner can be selected to set the Computer ownership, etc. Tip: The "Key column mappings" field will automatically be set to the first column in the data file. This will usually need to be set to the correct column.

Note: When importing Asset Owners or Cost Centers, both the data class mapping and associations must be set or this will fail. The following article describes how to correctly do this:

Click on the "Save changes" button. Note: If the Source for data classes or the Key column mappings for associations/reverse associations are not filled out when Save is used, and the import rule is later returned to from another area in the Console, the partially completed items will be automatically removed. Save does not record partially entered items. The user should always complete these therefore to avoid having them automatically removed upon saving. Information about this can be found here:

 Part 3 Troubleshooting 

1. If a data filter is used, this can only contain basic SQL commands. This may be present and have worked in the past, but an Import Rule may later stop working or skip bringing in certain data classes. To check if the data filter is the issue, #1 remove it temporarily. If the issue that the Import Rule sees disappears the data filter code is the issue. #2 Click on the data filter's "Show data" link. If this returns the error "Failed to apply data filter. An error occurred applying data filter '<data filter>'. (etc.)", there is some fundamental issue with the data filter now, such as invalid column name, etc. The error will often list what the specific issue is. Correct this so that no error occurs before continuing to try to use the Import Rule.
2. If during the setup for the Import Rule, the user changes column names (add, rename, delete) in the data file, the Data Source must be re-selected to refresh the mappings for the Import Rule.
3. When using the "Select data classes" link, data classes not specific to the resource type will not appear for selection.
4. When using the "Select data classes" link, custom data classes will not appear for selection if they have not been assigned to the resource type.
5. Data classes, association and reverse association values must be set to the expected format. For example, the Serial Number data class is a nvarchar (alphanumeric) value of no greater than 100 characters. If the user attempts to import more than 100 characters, this data class assignment may fail and will not be truncated. Likewise, the values must be of the same type. For example, the Accounting Information > Return Date data class is a datetime value. If values are not as expected, the column or row will be skipped.
6. When setting the value of a data class that is a foreign key, the foreign key's value must already exist. For example, for the Cost Items > Accounting Code foreign key, any specified values must exist exactly as spelled in Manage > Assets > Financial Types > Accounting Code. If these are not present, the column or row will be skipped.
7. When setting the value of a data class that is a foreign key, the Update Mode may need to be changed if multi rowed items are used, such as cost items. 
8. When setting an association or reverse association, the resources to associate to must already exist. The Import Rule does not create these. (A separate Import Rule can be used beforehand, however, to first create these.) For example, when setting the Location association to a location of New York, this location must exist in Manage > Assets > Organizational Types > Location. If these are not present, the column or row will be skipped. 
9. If errors occur trying to save the Import Rule, these are usually caused by something from the above instructions not being set, left out by accident or misconfigured. Double check that everything has been configured, then retry clicking on the "Save changes" button.