This documentation supports the 23.3 version of BMC Helix ITSM.To view an earlier version, select the version from the Product version menu.

Migrating Foundation data from BMC Helix ITSM to a new environment

Use SQL queries to export your foundation data and prepare the Onboarding wizard spreadsheets for migrating Foundation data from an existing BMC Helix ITSM environment to a different AR System environment such as migrating foundation data from a previous version to the latest version of BMC Helix ITSM. Make sure that the exported data is formatted correctly so that the data can be loaded successfully into the Data Management Onboarding wizard.

SQL queries for exporting the Foundation data

Consider the following points when using SQL queries for exporting Foundation data:

  • When using the following queries, replace the Replace_Company_Name string with your Company name.
  • When using queries in Oracle, replace the SQL function CHAR( ) with CHR( ).
  • You must ensure that the exported data is formatted correctly so it can be loaded successfully into the Data Management Onboarding wizard.
  • Microsoft Excel interprets new line characters as a record separator. If there is new line character embedded in your data, you will need to change it to a different character; for example, a space. The SQL query for the Location template in the following SQL queries table contains an example of how to replace a new line or carriage return character with white space or a semicolon. For example, REPLACE (GroupNotificationEmail,CHAR(10),';') replaces a new line or carriage return character with a semicolon.
  • Modify the WHERE clause in the queries to restrict the data to your requirements

The following SQL queries table shows the SQL queries that apply to each Data Management foundation template spreadsheet:

SQL queries

Template

Tab

SQL query

Company.xlsm

Company

select Company,Company_Type,'' from Com_Company;

Organization.xlsm

People Organization

select distinct Organization,Department from CTM_People_Organization where Company='Replace_Company_Name';

SupportGroup.xlsm

Support Group

select Support_Organization,Support_Group_Name,case when Support_Group_Role='Help Desk' then 0 else NULL end, case when Vendor_Group=1 then 0 else NULL end,REPLACE
(REPLACE(GroupNotificationEmail,CHAR(10),';'),CHAR(13),';'),'',Description

from CTM_Support_Group where Status=1 and Company='Replace_Company_Name' group by Support_Organization,Support_Group_Name,
Support_Group_Role,Vendor_Group,GroupNotificationEmail,Description;

Location.xlsm

Location

select max(Region),max(Site_Group),Site,max(REPLACE(REPLACE(Street,CHAR(10),' '),CHAR(13),' ')),max(Country),max(State_Province),max(City),max(Zip_Postal_Code),max(Site_Alias),max(Description)

from SIT_Site_Alias_Company_LookUp where Site=Site_Alias and  Status_STL=1 and Company='Replace_Company_Name' group by Site;

PeopleTemplate.xlsm

People Template

select Remedy_Login_ID from CTM_People where Remedy_Login_ID is not null and Profile_Status=1 and Company='Replace_Company_Name';

PeopleTemplate.xlsm

Permission Group

select Permission_Group,viaTIL_License_Type,Remedy_Login_ID from CTM_People_Permission_Groups where Permission_Group_Type = 1 and Permission_Group !='General Access';

PeopleTemplate.xlsm

Support Group Association

select Support_Organization, Support_group_name,Default_x,Login_ID from CTM_Support_Group_Association a, CTM_Support_Group b where a.Support_Group_ID=b.Support_Group_ID  and a.Status=1

and Login_ID in (select Remedy_Login_ID from CTM_People where Profile_Status=1 and Company='Replace_Company_Name');

PeopleTemplate.xlsm

Functional Roles

select Support_Organization, Support_group_name,Support_Group_Association_R001,Login_ID from CTM_Support_Group_Association a, CTM_Support_Group b

where a.Support_Group_ID=b.Support_Group_ID and a.Status=1 and Login_ID in (select Remedy_Login_ID from CTM_People where Profile_Status=1 and Company='Replace_Company_Name');

People.xlsm

People

select Last_Name,First_Name,max(Corporate_ID),max(Internet_E_mail),max(Site),max(Organization),max(Department),max(Remedy_Login_ID), max(Password),
max(a.License_Type),max(Remedy_Login_ID) from CTM_People a LEFT OUTER JOIN User_x b on Remedy_Login_ID=Login_Name

where Company='Replace_Company_Name' and Profile_Status=1 group by Last_Name,First_Name,Remedy_Login_ID;

People.xlsm

Additional Support Groups 

select Login_ID,a.Support_Organization,a.Support_Group_Name from CTM_Support_Group a,CTM_Support_Group_Association b where a.Support_Group_ID=b.Support_Group_ID and b.Status=1 and Login_ID in (select Remedy_Login_ID from CTM_People

where Profile_Status=1 and Company='Replace_Company_Name');

People.xlsm

Additional Functional Roles

select Login_ID,a.Support_Organization,a.Support_Group_Name,FunctionalRoleAlias from CTM_Support_Group a,CTM_SupportGroupFunctionalRole b where a.Support_Group_ID=b.Support_Group_ID and b.Status=1 and Login_ID in (select Remedy_Login_ID from CTM_People

where Profile_Status=1 and Company='Replace_Company_Name') and a.Company='Replace_Company_Name';

OperationalCatalog.xlsm

Operational Categorization

select distinct Service_Categorization_Tier_1,Service_Categorization_Tier_2,Service_Categorization_Tier_3,'' from CFG_Service_Catalog;

ProductCatalog.xlsm

Product Categorization

select distinct Menu_Category,b.Menu_Label_1,Product_Categorization_Tier_1,Product_Categorization_Tier_2,Product_Categorization_Tier_3,Product_Name,Manufacturer,Suite_Definition_, case when Company = '- Global -' then 0 else NULL end from PCT_ProductCompanyAssocLookup a, PCT_MenuItems b where b.Menu_Type = 'CITYPE' and b.Menu_Value_1 = a.CategorizationSchemaKeyword

 and b.Locale = 'en' and b.Menu_Label_1 != 'Service';

Assignment.xlsm

Assignment

select distinct Assignment_Event,Support_Organization,Assigned_Group,Organization,Department,Region,Site_Group,Site,Product_Categorization_Tier_1,Product_Categorization_Tier_2,Product_Categorization_Tier_3,Product_Categorization_Tier_1,
Product_Categorization_Tier_2,Product_Categorization_Tier_3,

Product_Name,Description from CFG_Assignment where Company='Replace_Company_Name';

Importing delimited data from a text file into Microsoft Excel

When you execute queries by using your database client, text files are generated that contain delimited data.  You must import this delimited data into an Excel file because the Data Management Onboarding wizard can only load data from Excel files.

  1. Start Microsoft Excel
    (Windows) Click Start > All Programs >  Microsoft Office.
  2. Select File > Open.
  3. In the dialog box select, All Files (*.*) from the drop-down menu to make the text files visible and select the desired file name.
  4. In the Text Import Wizard - Step 1 of 3 window, select the Delimited radio button and click Next.
  5. In the Text Import Wizard - Step 2 of 3 window, select the Other check box and enter the caret symbol (^) as the delimiter in the text box.
    The queries use the caret symbol (^) because commas or the vertical bar symbol (|) are often used in the data for other purposes.
  6. Click Next.
  7. In the Text Import Wizard - Step 3 of 3 window, select Text and click Finish.
  8. Save this file for future use in XLS format to copy and paste data between spreadsheets.

Copying and pasting data between spreadsheets

  1. Open a Data Management template spreadsheet, for example, Company.xlsm.
  2. Open the XLS file where you have save the exported data for Company, for example, Export_Company.xlsx.
  3. Select all the data from the exported spreadsheet.
  4. In the open Data Management template spreadsheet, right-click cell number 4 in column A and select the Paste Special option.
    221_Company_spreadsheet.png

  5. In the Paste Special window, select Text and click OK.

    If the Paste Special window does not appear do not paste the data. Instead, open a text editor (for example, Notepad), paste your data into the text editor, copy the text, and paste it into the spreadsheet by using the instructions for the Paste Special option.

  6. Save the template spreadsheet after you have pasted the data.

Verifying your data before using spreadsheets to load data into the Onboarding wizard

  • Verify that all cells are formatted as text cells.
    The tip that is highlighted in green should appear in the top left corner of the cell for numeric data.
  • Verify that all mandatory cells are complete (contain valid data), including cases where cells are only mandatory if other cells are completed.
  • Verify that only permitted values are entered.
    Check the cells which must contain one of the values shown in a column heading.
  • Verify that no fields exceed the permitted length for the required value.
    Field sizes can change if you have extended a field in your legacy system.
  • Delete empty or null rows at the base of the spreadsheet.
    These rows might cause the scroll bar to extend.
  • Validate that none of the Excel sheets have been deleted or renamed.
  • Verify text fields do not contain leading or trailing blank characters.

Replacing NULL values

If you are using Microsoft SQL Server as your database client, your exported data might contain NULL values, which are shown as NULL string values in your spreadsheet columns, and which are considered invalid data. You must replace the data as shown in the example below.

  1. Open the Data Management template that contains the invalid data.
    221_null_data.png
  2. Replace the NULL values with an empty string.
  3. Save the Excel workbook that contains the template. The spreadsheet template is now ready to load data into the Onboarding wizard.

Using database clients to execute queries

Use the SQL queries provided in this section from your Microsoft SQL Server or Oracle server database client to export foundation data from your BMC Helix ITSM environment.

If you are using database clients with graphical user interfaces, then the Copy and Paste menu options can be used to move the data into your spreadsheets.

Microsoft sqlcmd

Use Microsoft SQL Server to execute queries by using SQL Server Management Studio and performing the following actions:

  1. Right-click on the result and choose Select All data, right-click again, and select Copy (or press Ctrl+C).
  2. Paste this data in text format (otherwise the data will contain errors) into the appropriate spreadsheet template provided with Data Management.

If you use a database-specific command line utility to export data, data formatting options are required. For example, if you use the SQL Server sqlcmd utility, use the following formatting options in the attached MSSQL_Export_Foundation.batfile:

sqlcmd -l 65534 -W -h -1 -s "^" -o Company.txt -S DB-SERVER -d DB-NAME -U LOGIN-ID -P LOGIN-PASSWORD -Q "SET NOCOUNT ON;select a.Company,Company_Type,Company_Alias from Com_Company a,COM_Company_Alias b where a.Company_Entry_ID=b.Company_ID;" 

sqlcmd options definitions  

Options

Purpose

-s “^”

Defines the ^ character as the column data separator.
This character is used because commas and vertical bar (|) characters are normally used by customers in their data. The column data separator splits data into additional columns if the separator character is already used in any of your column values. Choose your own separator if the ^ character is already present in the your data.

-l <LINE SIZE>

Defines line size

-W

Trims the blank spaces at end of the column data

-h 1

Does not print the result heading

-o <FILE NAME>

Provide the output file name for the result when this information is entered.

-S <DB-SERVER>

Provides database server name

-d <DB-NAME>

Provides database name to run query on

-U <LOGIN-ID>

Provides database user login name

-P <LOGIN-PASSWORD>

Provides password

-Q

Provides command line SQL query

SET NOCOUNT ON

Does not print the row count of the text result before the select command.

Oracle SQL*Plus

If you are using the Oracle SQL*Plus client, use the following formatting options in the attached ORACLE_Export_Foundation.sqlbatch file:

sqlplus <userlogin>/<password> @ <ORACLE_SID> @ ORACLE_Export_Foundation.sql

SQL*Plus options definitions

Options

Purpose

SET COLSEP "^"

Defines the ^ character as the column data separator

SET FEEDBACK OFF

Does not print the number of rows for the result at the end of the result

SET ECHO OFF

Suppresses the display of commands as they execute

SET PAGES 0

Suppresses the display of all headings, page breaks, and titles in the result

SET LINESIZE 32767

Wraps the result when there are at least 32767 characters

SET HEADING OFF

Does not print the result header

SPOOL Company.txt

Spools the result in the name file for each query

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*