Using the TeamDynamix Asset Import Utility

Table of Contents

1. Overview
2. Prerequisites
3. Quick Start Guide
4. Downloading and Running the Utility
   1. Minimum Server Requirements
   2. Command-line Switches
   3. Example Usage
   4. Scheduling the Utility
   5. Asset Import Previewer
   6. Logging
5. Creating the Asset Import Utility Configuration XML file
   1. XML Overview
   2. File structure
   3. TDX API Connection Details
   4. External DB Connection Details
   5. Database Query
   6. Field Mappings

Overview

The TeamDynamix Asset Import Utility is a .NET command-line program which can:

1. Load import settings/configuration from a specified XML file,
2. Connect to an external database and run a SQL query to extract data,
3. Map the returned data to specified TDX Asset record fields,
4. Separate the mapped data into batches and submit each batch to the TeamDynamix Web API for processing,
5. Log activity to a file for later review. Logs are generated in the directory where the utility is located. Log files will rollover at 5MB and a total of 5 (rolling) log files will be preserved.

What This Article Will Not Cover

This article is not a guide for the following things:

• How to troubleshoot issues with customer server command-line/terminal syntax.
• How to troubleshoot issues with customer server task scheduler systems.
• How to troubleshoot database connectivity issues.
• How to write or troubleshoot SQL queries or SQL syntax/errors.

Prerequisites

In order to download the Asset Import Utility, you must have access to an Assets/CI application and permission to import assets.

In order to use the Asset Import Utility to import asset records, you must first have Vendor, Product Type, and Product Model records configured in your TeamDynamix Assets app. The Asset Import Utility will not create or update Vendor, Product Type, or Product Model records.

Quick Start Guide

1. Download and install the utility on a server with access to your external database: Downloading and Running the Utility
2. Download the example import.xml file from this article's attachments
3. Review each of the article sections below, editing the example import.xml file as needed for your configuration: Creating the Asset Import Utility Configuration XML file
4. Run the import previewer to test and troubleshoot your configuration: Asset Import Previewer
5. If successful, run the import utility in Sandbox
6. If successful, run the import utility in Production

Downloading and Running the Utility

In order to download the Asset Import Utility, you must have access to an Assets/CI application and permission to import assets. Download the TeamDynamix Import Utility MSI from the Downloads application in TDNext within your organization's TeamDynamix environment.

Minimum Server Requirements

The following minimum server requirements need to be met to run this utility:

• Windows Server 2008 R2 SP1 (or higher) or Windows 7 SP1 (or higher)
• .NET Framework 4.7
• At least 2GB of RAM and a dual core CPU

Command-line Switches

The utility has the following command-line switches:

Example Usage

To run the utility, navigate to the installation location (default location is C:\Program Files (x86)\TeamDynamix Import Utility ) and call TeamDynamix.ImportUtility.Importer.exe via the Windows Command Prompt.

View the Help Menu
TeamDynamix.ImportUtility.Importer.exe -h or TeamDynamix.ImportUtility.Importer.exe --help

Run in Test Mode
Run the importer without either --add or --edit flags to test that the system will properly query the database, perform mappings, and submit items to the web API without error. Even though items will not be imported, the batch size restriction will still be enforced.

TeamDynamix.ImportUtility.Importer.exe -c "C:\TeamDynamix\AssetImport\config.xml"

Run in Non-Test Mode
Run the importer with both the --add and --edit flags to perform a full import process. To ensure that duplicate assets will not be added, first ensure that the appropriate external identifier has been set on all relevant assets.

TeamDynamix.ImportUtility.Importer.exe -c "C:\TeamDynamix\AssetImport\config.xml" --add --edit

Run in Non-Test Mode With Logging
Run the importer with the --l flag and a path to the logging config file (see below) to log to an external file instead of to the console.

TeamDynamix.ImportUtility.Importer.exe -c "C:\TeamDynamix\AssetImport\config.xml" -l “C:\TeamDynamix\AssetImport\logconfig.xml” --add --edit

Scheduling the Utility

To run the utility on a schedule, use Windows Task Scheduler (or a similar scheduler utility) to run the program with the appropriate arguments (command-line switches) and on the desired schedule.

If you are using Windows Task Scheduler, here are some helpful hints for configuring the Action:

• Action: Start a Program
• Program/Script: use the Browse button to navigate to and select the TeamDynamix.ImportUtility.Importer item
• Add Arguments: your command line switches
  • E.g., if the XML configuration file is called AssetImport.xml and you want to add assets but not edit, your "add arguments" should be
    -c "AssetImport.xml" --add
  • E.g., if the XML configuration file is called ConfigFile.xml and you want to edit assets but not add, your "add arguments" should be
    -c "ConfigFile.xml" --edit
  • E.g., if the XML configuration file is called MyXMLFile.xml and you want to add and edit assets, your "add arguments" should be
    -c "MyXMLFile.xml" --add --edit
• Start In: the path to where your XML configuration file is located (suggested location is C:\Program Files (x86)\TeamDynamix Import Utility )
  • E.g., if the XML configuration file is located in C:\users\admin\Documents, your "start in" should be
    C:\users\admin\Documents
  • E.g., if the XML configuration file is located in C:\Program Files (x86)\TeamDynamix Import Utility , your "start in should be"
    C:\Program Files (x86)\TeamDynamix Import Utility

Asset Import Previewer

Included in the utility install directory is the Asset Import Utility Previewer app. This app allows you to test a configuration file and see the returned data from your SQL query as well as the associated JSON data sent to the TDX Web API. The previewer will also highlight any errors to help troubleshoot column name or data format issues.

Logging

By default, the utility logs to the terminal output, but you can also save logs to a file by providing a path to a log4net config file. An example log4net config file that creates 5mb rolling log files is available in this article's attachments. For more information on creating log4net configuration files, see http://logging.apache.org/log4net/release/manual/configuration.html.

Creating the Asset Import Utility Configuration XML file

The configuration XML file is a critical component of the Asset Import Utility. Within the configuration file, you will be able to specify all of the settings, connection details, field/data mappings, and logic needed for the utility to import assets from an external source into TeamDynamix.

XML Overview

XML is a structured document format and markup language for storing and describing data. Each piece of data in an XML document is known as an element and is wrapped in a pair of opening and closing tags identifying the piece of data, like so:

<name>Jackson</name>

Each element can either contain a value or contain other elements, allowing for more complex representations of data:

<person>
  <name>Jackson</name>
  <pet>Cat</pet>
  <country>USA</country>
</person>

Comments are indicated by surrounding the comment with a special tag:

<!-- This is a comment -->

For brevity often only the element name is used in the documentation below, but within your configuration file each element needs to have matching opening and closing tags in order to be valid XML.

File structure

The configuration file is broadly composed of four main sections, each containing multiple XML elements containing configuration data plus additional elements to structure the document:

• TeamDynamix API connection details
• External database connection details
• The SQL query to run on the external database
• TDX field mappings for data returned by the query

These sections and their elements are described in more detail below. Each section is described independently, but they should all be included within your configuration file like the example below:

Example file structure

<?xml version="1.0" encoding="utf-16"?>
<ImportConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ObjectType>Asset</ObjectType>

<!-- TDX CONNECTION DETAILS -->
<ApiCredentials>
  <BaseApiUrl>...</BaseApiUrl>
  <ApplicationID>...</ApplicationID>
  <WebServicesBeid>...</WebServicesBeid>
  <WebServicesKey>...</WebServicesKey>
  <ApiRequestTimeoutSeconds>...</ApiRequestTimeoutSeconds>
</ApiCredentials>
<BatchSize>...</BatchSize>

<!-- DB CONNECTION DETAILS -->
<ConnectorType>...</ConnectorType>
<ConnectionString>...</ConnectionString>
<DatabaseCommandTimeoutSeconds>...</DatabaseCommandTimeoutSeconds>

<!-- DB QUERY -->
<Query>
...
</Query>

<!-- FIELD MAPPINGS -->
<ExternalIdColumnName>...</ExternalIdColumnName>
<Mappings>
  <SelectedMapping>...</SelectedMapping>
  <SelectedMapping>...</SelectedMapping>
  ...
</Mappings>

<LastExecutedUtc>2023-01-01T00:00:00Z</LastExecutedUtc>
<LastExecutedAsUtc>false</LastExecutedAsUtc>
</ImportConfiguration>

TDX API Connection Details

This section includes settings and details used by the utility to connect to your TeamDynamix environment.

...
<ApiCredentials>
  <BaseApiUrl>https://example.teamdynamix.com/TDWebAPI/</BaseApiUrl>
  <ApplicationID>000</ApplicationID>
  <WebServicesBeid>00000000-0000-0000-0000-000000000000</WebServicesBeid>
  <WebServicesKey>00000000-0000-0000-0000-000000000000</WebServicesKey>
  <ApiRequestTimeoutSeconds>100</ApiRequestTimeoutSeconds>
</ApiCredentials>

<BatchSize>1000</BatchSize>
...

External DB Connection Details

This section includes settings and details used by the utility to connect to your external database of assets.

  <ConnectorType>SqlServerConnector</ConnectorType>
  <ConnectionString>Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;</ConnectionString>
  <DatabaseCommandTimeoutSeconds>30</DatabaseCommandTimeoutSeconds>

Database Query

This section is used to define the actual query run against your external database of assets. There is just a single <Query> element that should contain the text of your SQL query to be run on your external database.

...
<Query>
  Select
    *
  From
    MyAssetTable
  Where
    LastUpdatedDate &gt;= @LastExecuted
</Query>
...

Field Mappings

This section is the most important part of the configuration file. Here you will define the relationship mappings between the data returned from the database query and the fields on a TDX asset record. Within this section you will have a <Mappings> element containing several <SelectedMapping> elements, each representing an individual relationship between a field in TDX and a column of data from your query.

External IDs

In order to identify assets that have already been created by the Asset Import Utility and prevent creating duplicates, the system requires an External ID field that uniquely identifies the asset in the external database. For example, Microsoft SCCM has a "SMS_Unique_Identifier" column that it uses to store a unique identifier for each system in its database. The ExternalIdColumnName element is used to identify the column returned by the query that represents this field.

Configuring Mappings

Within the <Mappings> element you will need to configure several mappings, each represented by <SelectedMapping> elements, to describe the relationship between the data returned by the query and the available fields on an Assset record in TDX. Each <SelectedMapping> must contain a <DestinationFieldId> element to indicate which field in TDX will be updated by the mapping, and typically will also contain either a <DirectMapColumnName> or <NameMatchColumnName> element to indicate which column of data returned by the query will be used:

...
<ExternalIdColumnName>...</ExternalIdColumnName>
<Mappings>
  <SelectedMapping>
    <DestinationFieldId>SerialNumber</DestinationFieldId>
    <DirectMapColumnName>...</DirectMapColumnName>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>StatusID</DestinationFieldId>
    <NameMatchColumnName>...</NameMatchColumnName>
    <DefaultValue>...</DefaultValue>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>CustomAttribute-00000</DestinationFieldId>
    <DirectMapColumnName>...</DirectMapColumnName>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>CustomAttribute-99999</DestinationFieldId>
    <NameMatchColumnName>...</NameMatchColumnName>
  </SelectedMapping>
</Mappings>
...

Standard Fields vs Custom Attributes

Each selected mapping must have a <DestinationFieldId> element to indicate which TDX field to update. For standard/out-of-the-box fields on a TDX Asset record (like serial number, asset tag, name, etc) use the appropriate value from the list of standard field IDs below. For example:

<SelectedMapping>
  <DestinationFieldId>ProductModelID</DestinationFieldId>
  <NameMatchColumnName>...</NameMatchColumnName>
</SelectedMapping>

For custom attributes, use a value formatted like "CustomAttribute-00000" to indicate a custom attribute, replacing "00000" with the ID of the custom attribute. Attribute IDs can be found in TDAdmin > Applications > [asset app] > Asset Attributes. For example:

<SelectedMapping>
  <DestinationFieldId>CustomAttribute-00000</DestinationFieldId>
  <DirectMapColumnName>...</DirectMapColumnName>
</SelectedMapping>

DirectMapColumnName vs NameMatchColumnName

Each selected mapping must also have a <DirectMapColumnName> or <NameMatchColumnName> element to indicate which column of data from your query should be used to update the associated TDX field, and how to update the data. For either of these elements, use the SQL column name or column alias from your query as the element value. There are two different elements that can be used:

• NameMatchColumnName is used for choice-based fields in TDX, like the asset owner, asset location, or any choice-based custom attributes like dropdown or radio-button. When namematch is used the utility will do a lookup of the available choices and select the choice in TDX that matches the value returned from the query.

  <SelectedMapping>
    <DestinationFieldId>...</DestinationFieldId>
    <DirectMapColumnName>YourSQLColumnNameOrAliasHere</DirectMapColumnName> 
  </SelectedMapping>

• DirectMapColumnName is typically used for text-based fields in TDX, like the asset name, asset serial number, or any text-based custom attribute like textarea or textbox. When directmap is used the utility will simply directly insert whatever values are returned from the query into the mapped field, like a copy-and-paste.

  <SelectedMapping>
    <DestinationFieldId>...</DestinationFieldId>
    <NameMatchColumnName>YourSQLColumnNameOrAliasHere</NameMatchColumnName> 
  </SelectedMapping>

  Some standard fields require the data returned by the query to be in a specific format when using NameMatchColumnName to look up values. For example, ProductModelId requires a format of Supplier Name|Model Name (note the pipe | character separator expected in the column value). You may need to concatenate two columns from your database to generate this value:

  SELECT manufacturername + '|' + modelnamec AS CombinedManufModel

Setting Default Values

In addition to (or instead of) indicating the query column to use for mapping, you can specify a default value. This is particularly useful if you want to set a fallback value or standard value for a field, and is used alongside <NameMatchColumnName> mappings for choice-based fields.

Default values are used during import in two cases:

• A new asset is being created during the import, or
• An existing asset is being updated, but a matching choice for the value in NameMatchColumnName was not found.

If the asset already exists and DirectMapColumnName is used to set the value or NameMatchColumnName finds a matching choice, the default value will not be used.

To set a default value, add a <DefaultValue> element within your <SelectedMapping> element with the choice ID (for choice-based fields) that you want to set as a default value. You can also add a <DefaultText> element in order to make it easier to remember what a choice ID represents. It has no effect on how the import functions otherwise.

<SelectedMapping>
  <DestinationFieldId>StatusID</DestinationFieldId>
  <DefaultValue>219</DefaultValue>
  <DefaultText>Detected by Scanner</DefaultText>
</SelectedMapping>

<ExternalIdColumnName>ID</ExternalIdColumnName>
 
<Mappings>

  <SelectedMapping>
    <DestinationFieldId>SerialNumber</DestinationFieldId>
    <DirectMapColumnName>...</DirectMapColumnName>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>Name</DestinationFieldId>
    <DirectMapColumnName>...</DirectMapColumnName>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>StatusID</DestinationFieldId>
    <NameMatchColumnName>...</NameMatchColumnName>
    <DefaultValue>...</DefaultValue>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>CustomAttribute-00000</DestinationFieldId>
    <DirectMapColumnName>...</DirectMapColumnName>
  </SelectedMapping>

  <SelectedMapping>
    <DestinationFieldId>CustomAttribute-99999</DestinationFieldId>
    <NameMatchColumnName>...</NameMatchColumnName>
  </SelectedMapping>

</Mappings>