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 Action Request System environment. For example, from BMC Remedy ITSM to BMC Helix ITSM . You must ensure 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.

Start Microsoft Excel (in Microsoft Windows, click Start > All Programs > Microsoft Office).
Select File > Open.
In the dialog box select, All Files (*.*) from the drop-down menu to make the text files visible and select the desired file name.
In the Text Import Wizard - Step 1 of 3 window, select the Delimited radio button and click Next.
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.
Click Next.
In the Text Import Wizard - Step 3 of 3 window, select Text and click Finish.
Save this file for future use in XLS format to copy and paste data between spreadsheets.

Copying and pasting data between spreadsheets

Open a Data Management template spreadsheet, for example, Company.xlsm.
Open the XLS file where you have save the exported data for Company, for example, Export_Company.xlsx.
Select all the data from the exported spreadsheet.
In the open Data Management template spreadsheet, right-click cell number 4 in column A and select the Paste Special option.
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.
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, which 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.

Open the Data Management template that contains the invalid data.
Replace the NULL values with an empty string.
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 BMC Remedy foundation data from your BMC Remedy 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:

Right-click on the result and choose Select All data, right-click again, and select Copy (or press Ctrl+C).
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.bat file:

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>	Required to provide the output file name for the result
-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	If this option is used, the query 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.sql batch 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