Migrating from a BMC Footprints database

Introduction

Precision Bridge supports migration of data from a source database into any supported target, and it is by this method that migration of data from BMC Footprints can be supported, provided that precision Bridge has connectivity (via JDBC/ODBC) to the database.

More modern Footprints instances (version 12+) as well as older versions (such as version 11) can be supported. There are a few key things to be aware of when using a footprints database as a source.

Configuring Footprints Database Connections

BMC Footprints typically runs on an Oracle or SQL Server database. The database usually has multiple schemas, so you will need to configure the Precision Bridge connection to access all schemas, since Precision Bridge by default only shows objects owned by the connecting user. For SQL Server, this requires setting the Schema field in the connection to ALL:

Mapping Tables without Primary Keys

Precision Bridge requires all supported tables to have a primary key, however some tables in BMC Footprints do not have one defined, and these tables will not be shown in Precision Bridge by default. To work around this, you can add a configuration file to your Precision Bridge Configuration folder defining what the primary key should be used for specific tables. Note that a primary key can be made up of multiple fields (a composite key) as long as the combination of these field values is unique for every for every row.

To add this configuration file:

1. Navigate to the Precision Bridge configuration folder for your user. This is located at:
   <path to user folder>\AppData\Roaming\PrecisionBridge8.4\dbConfig

2. Create or open a file named TableConfig_Custom.xml

In this file, you can define the primary key fields to be used for any tables that do not have them explicitly defined in the database. See the example below:

<TABLE_CONFIG>
  <!--Each TABLE element must have:-->
  <!--   - A dbname attribute, which is the name of the table in the Database-->
  <!--   - At least one KEY_FIELD element. The key field elements identify the fields that together give a unique key for the table.-->
  <!--     Each KEY_FIELD element should have a dbname attribute, which is the name of the column in the Database -->
  <!--     Precision Bridge will only be able to support attachment migration for these tables if the table has a SINGLE primary key defined.-->
  <!--     Information in this configuration file will override any primary key information extracted from the database itself-->
  <!--     An example is given below:-->

  <TABLE dbName="MASTER1_TIMETRACKING">
     <KEY_FIELD dbName="mrID"/>
      <KEY_FIELD dbName="mrGENERATION"/>
  </TABLE>
</TABLE_CONFIG>

You can add as many <TABLE> elements as needed within the <TABLE_CONFIG> element. The dbName attribute should be the database name of the table. Each table element must have at least one <KEY_FIELD> element defined. Each key field element must be the name of a column within that table. In the example above we are stating that for the table MASTER1_TIMETRACKING, the primary key fields should be assumed to be mrID and mrGENERATION.

After saving this file, re-open your Precision Bridge Project, flushing the metadata cache (on the options tab) if required. The additional tables should now be shown.

Attachment Retrieval

In Footprints, attachments are stored in a file system on the footprints server, but can be accessed via an authenticated URL.

This process is different depending on what version of BMC Footprints you are using. We will consider the 2 main supported versions:

Footprints 20

This section is likely to apply to earlier versions of BMC Footprints as well, down to version 12.

Tickets in Footprints can have associated attachments. To configure retrieval and migration of these, go to the options tab of the form mapping that maps tickets. In the Attachment option, select Attachments from URL:

• Select the file_info form as the Attachments Form
• Select id as the parent form id
• Select the authentication method as BMC Footprints

Move to the Attachments Source Fields tab. Here we identifiy the fields that will provide attachment metadata.

• Select file_info_uri as the field for content path and file name.
• Select file_info_size as the field for the attachment size
• Parent ID can be left blank. This id a part of the file_info_uri values. The filter required to extract the correct attachment records for each parent will be configured on the advanced tab.

Move to the Advanced Tab. Here we identify how to use the metadata to identify and extract the attachments.

• The Content Path Format is used to build the final URL that will be used to extract each attachment. Set it to:
   <footprints server base url>/footprints/servicedesk/api/item/attachment?uri=[content_path_url_encoded]
  Where <footprints server base url> is the name of your footprints server. You can usually identify this from the URL you use to log into footprints.
• The File Name Regex is used to extract a filename and extension from the URL provided. It must be a valid regular expression with at least 1 group (group 1 will be used to collect the file name on a match) This string works on Footprints 12+ URLs:
  \/([^\/]+\.[^\/?]+)
• Filter Condition. This is an SQL formatted condition that will retrieve the attachments for a given ticket. It must include the [parent_id] marker, which will be replaced at execution time. Set this filter to: 
  file_info_uri LIKE '%/[parent_id]/%'
• The file name format can be left empty, unless there is a need to prepend other strings to the filename

This configuration should allow attachments to be retrieved using file_info attachment metatata for Footprints Version 20.

Footprints 11

Footprints 11 has a very different database structure. The configuration for ticket attachments should be as follows:

 

• In this case, the attachment metadata is stored on the ticket table itself. In this case the MASTER1 table(1 is the project or workspace id)
• The ID field for the parent table is mrID (the primary key)
• Use the BMC Footprints 11 authentication method (note this is different than the BMCFootprints authentication method, which is intended for more reccent versions of Footprints.

On the Attachment Source Fields Tab:

• Set the Parent ID field to mrID
• Set the File Name field to mrATTACHMENTS. Note that this only provides a list of attachment file names. These will be separated and a download URL constructed for each file.

On the Advanced Tab:

• Set the Content Path Format to:
  [footprints_base_url]/MRcgi/MRdownloadAttachment.pl?USER=[footprints_user]&PROJECTID=1&MRP=[footprints_mrp]&MR=[parent_id]&ATTACHMENT_NAME=[file_name_url_encoded]
  Note that the project ID integer might need to be changed. It should match the ID of the project or workspace you are migrating data from, as identified by the prefix on the ticket table name.
  The markers in green will be replaced at execution time with the correct values for each attachment.
• Set the File Name Extraction Regex to ([^\r\n]+). This regular expression will split the list of file names into individual file names and an attachment will be created for each one in the list.
• The file name format can be left empty, unless there is a need to prepend other strings to the filename.

This configuration should allow attachments to be retrieved using attachment metadata in the ticket record for Footprints Version 11.

Execution Options - Attachment Authentication

If you have configured attachments in your project (see above) and have specified either Footprints or Footprints 11 authentication, you will need to enter these authentication details when you execute, in the Execution options that are displayed:

Go to the Attachments tab, then to the URL Authentication tab.

• Enter the base URL. This is the URL to your footprints server (root)
• Enter a Footprints username/password for a user that has read access the the tickets and attachments you are migrating.

Note that if the authentication is invalid, you will get an error during initialisation of the execution and it will be aborted.

Related Articles

Configuring a SQL Server Database Connection

Migrating Attachments From Database Metadata

Introduction to Execution Options