User Guide: Asset Import

Overview

After installing Asset Import (KB53 - Install: Asset Import), this user guide will provide details on configuring and running the Asset Import Connector

Related Articles

• KB55 - User Guide: Asset Import

• KB2535 - User Guide: Understanding Asset Import Logging

• KB53 - Install: Asset Import

Asset Import Ordering

The order of importing class information only applies if Relationships intend to be mapped. Data cannot be imported in to a class with a relationship, if that relationship data is not already imported or created.

When a class has the same relationship, the order is not important. The relationship for Users are not shown below since it only depends on having the User (Domain and Username) information. The 1-to-Many relationships have also been left out of this article, however you can read up on 1-to-Many relationships on out blog here: https://cireson.com/importing-to-many-relationship-data-with-asset-management-import/.

If multiple .csv files are to be used that have relationships mapped to each other, ensure that all data is imported without mapping any relationships. Once all the data is imported, the connector can then be edited to incorporate the relationships and resync the connector. This way, the import of the class properties can be done in any order, and then the updating of the relationships can be done in any order.

Follow the import order below to import the properties and relationships at the same time:

Creating a New Connector

Following the installation, licensing and configuration of the Asset Import app, SCSM Administrators will now see a new connector type within the Administration\Connectors area.

To create a new Connector, perform the following steps:

• Navigate to Administration\Connectors.

• Within the Tasks menu, select Create Connector -> Asset Management Import Connector.

• Update the fields within the connector to import data for any Configuration Item class available in SCSM.

General Details

Connector Name - Name of connector, this name can be changed later if needed and is used partly to name the workflow log file.

Management Pack - Management Pack to store the connector workflow.

Note: The following data source selectors will require different inputs based on the option selected. For example, SQL data source will require a connection string, and a LDAP query will require a username and password. Default source is CSV.

Source CSV format file with header row - Location of CSV file, can be a local path (on the Workflow Server) or network share, accessible to the Workflow Account, see "Using a CSV Source" below.

Note: The first line in the CSV file must be a header row containing column names.

Obtain source data from Microsoft SQL Server instead of a CSV file - Retrieve data from SQL server rather than a CSV file, for SQL sources, see "Using an SQL Source" below.

Obtain source data using an ODBC File DSN - Retrieve data from an ODBC File DSN, see " " below.

Obtain source data using an LDAP query - Retrieve data from an LDAP query, see " " below.

Target Class - Configuration Item based target class that the data will be imported into.

Note: Select a Combination Class (Type Projection) if the source data contains data that requires to be imported into relationships of the selected class. For large import processes, the workflow processing speed can be improved slightly by selecting a Combination Class that most closely contains the relationships that is to be mapped.

Warning: If the source data does not contain any relationship data, Do Not select a combination class.

Workflow log folder path - Default UNC or local folder path for output log files, the path must be valid on the Workflow Server and be accessible to the Workflow Account.

Test mode - Connector will run and create logs for inspection but will not commit any changes.

Info: It is recommend to run new connectors using this mode first to allow for reviewing the results in the log file before proceeding.

This connector can create new items - Enable this to allow the connector to create new items.

This connector can update existing items - Enable this to allow the connector to update existing items.

This connector will DELETE ALL matching items only - Enable this to ONLY remove items.

This connector will update multiple existing items matching specified custom keys - Enable this to update multiple items with specified custom keys.

Do not replace \n with a linefeed - In description or notes sections, a \n denotes a line break. Enable this to not add in line breaks. 

Source Configuration

After providing a Connector Name and a Management pack for the workflow storage, there are 4 different options to provide source data into the connector. Only one can be used per connector. 

Using a CSV Source

Browse to the location of the .CSV file that contains the asset data to import and select the Encoding Format of the file.

The selected path can be either a local path (on the SCSM workflow server) or a network share that has read permissions by the Workflow account.

The first line of the CSV file must contain the header row information for the data contained within.

After configuring the CSV source, proceed to the next section titled "Connector Data Mapping Settings".

How to use the Master Spreadsheet

The Master Spreadsheet can be downloaded from here.

This spreadsheet will help identify the available data fields that can be imported, organize all the data from multiple sources, and assist with the creation of the .CSV files to import with the Asset Import connector.

It will be the one source that all asset data from SharePoint lists, exports from other systems, or other excel files can be entered into and normalized.

Steps to using the Master Spreadsheet:

1. Data entry: Fill out the spreadsheet

2. Create .csv file:

   1. Right-click on the tab that is to be exported

   2. "Copy" to new Workbook

   3. On the new file, delete Column A and Rows 1-5

   4. Delete any rows that may have empty cells with borders

   5. Save the file as .CSV

   6. Close excel tab, do not Save

3. Follow “Using a CSV Source”, to import the .CSV files

Notes:

• Relationship fields are not required.

• The tabs of the excel spreadsheet are in the order the asset classes appear in the Asset Management views.

• When mapping properties to column headings in the Import Connector wizard, the Asset Status and Object Status enum properties will never have a value mapped to them.

• Do not enter the currency symbol in any of the amount fields (for example '$' or '£').

• If user names are to be imported, to any of the applicable spreadsheets, make sure that there is a domain and username or neither. If one or the other is entered, the import will produce an error.

Using an SQL Source

To use Microsoft SQL Server as a data source instead of a CSV file, select "Obtain source data from Microsoft SQL Server instead of a CSV file".

Click "..." to configure a SQL Connection String. Specify the SQL Server (or use SERVER\INSTANCE format to use a named instance), database and authentication settings. If Windows Authentication is used, the Workflow Account must have read access to the configured database. Click Test to enable the OK button if the connection was successful:

Note: If Windows Authentication is used, this test is performed using the current user credentials and not those of the Workflow Account. In this case, the test simply confirms that the server and database are valid and can be connected to. Later, if the Workflow Account cannot connect, an error will be logged in the log file and the connector will show an error status. If the current user has no rights to the database, and these cannot be granted, SQL authentication must be used.

Click OK to use the tested connection string.

Next, enter a SQL Query that will return at least one row, then click Execute Query to continue:

After configuring the SQL Source, proceed to the next section titled "Connector Data Mapping Settings"

Using an ODBC Source

For ODBC Server data source:

1. Create a File Data Source Name (DSN) that contains the Server, Database and username for the data source.

2. Browse the file system and select the File DSN. The SCSM Workflow account must have read access to the File DSN.

3. Enter the File DSN Password for the username within the File DSN.

4. Enter the SQL query that will be used to extract the data required for this connector.

5. Click Execute Query to test the query and gather field name requirements for class property mapping. The SQL Query Results field will show the number of row returned if the query was successful.

After configuring the ODBC Source, proceed to the next section titled "Connector Data Mapping Settings"

Using an LDAP Source

For an LDAP data source:

1. Enter the LDAP Server or Namespace and the LDAP Port (If required).

2. If the SCSM Workflow account does not have read access to the LDAP source, enter alternative credentials with the required rights.

3. Enter the LDAP Attributes that are required to be returned separated by commas.

4. Enter an LDAP search starting path to reduce the search scope as required.

5. Enter any LDAP Filter needed to refine the results to the specific required data.

6. Click Execute Query to test the query and gather field name requirements for class property mapping.
   The LDAP Query Result field will show the number of row returned if the query was successful.

After configuring the LDAP Source, proceed to the next section titled "Connector Data Mapping Settings"

Connector Data Mapping Settings

After selecting a source, configuration will continue with mapping the fields that are required

Property/Data Type

The data type is for reference of the input data type the property will expect. All keys and required properties, including Display Name, must be mapped. Whilst Display Name is not a key or required property as such, not setting it can cause issues, therefore the Asset Import app requires it to be set also. Typically this can be mapped to the same column as "Name" or a similar "naming" property.

Optionally, properties shown under "Optional Properties" can also be mapped. If a combination class is selected on the previous page, then Relationship Mappings may also be configured (see below).

Mapped keys are used to find existing instances of the specified class to update. If no instance is found, a new instance will be created.

If a key is an auto-increment key, it must be mapped as per normal keys. Values in the mapped column for auto-increment keys are only used to find and update existing instances. It is not possible to specify an auto-increment key value to be used to create a new instance. To create a new instance, this column should be left blank in the input data.

Mapped To

Contains available column headers from the specified source CSV file header row, or the SQL Query column names.

Relationship Mappings

If a Combination Class is selected, relationships can be mapped. In the above image, the Cost Center Has Organization relationship only has one valid target class, Organization, and this class only has one key. The reason for this is because Target Class\Key\Data Type is a text box for this relationship. In this example the relationship has been mapped to Organization Name.

The relationship Owned By User has multiple possible target classes, or, one possible target class with more than one key (which is the case in this example). The reason for this is because Target Class\Key\Data Type is a drop-down list containing available target classes.

In the image above, Domain User or Group has been selected. This added beneath it Domain User or Group\Domain\string and Domain User or Group\User Name\string, which are the keys for Domain User or Group. These are mapped to Owned By Domain and Owned By Username.

Workflow Schedule

This connector will be run manually, disable workflow and set a default schedule - Select this option if the workflow is to be triggered manually.

Note: Selecting this option will apply a default schedule, leave the connector disabled.

Enable this workflow to run the connector on a regular schedule or interval 

• Daily, on a specified schedule

• Frequency, on an interval

Note: It is possible to Enable and\or Disable these schedules later buy editing the connector.

Create

Click to create the connector, a results page will say if the connector was created successfully, or show any error if not.