This documentation supports the 22.1 version of Action Request System.
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 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

TemplateTabSQL query
Company.xlsmCompanyselect Company,Company_Type,'' from Com_Company;
Organization.xlsmPeople Organizationselect distinct Organization,Department from CTM_People_Organization where Company='Replace_Company_Name';


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;

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.xlsmPeople Templateselect Remedy_Login_ID from CTM_People where Remedy_Login_ID is not null and Profile_Status=1 and Company='Replace_Company_Name';
PeopleTemplate.xlsmPermission Groupselect Permission_Group,viaTIL_License_Type,Remedy_Login_ID from CTM_People_Permission_Groups where Permission_Group_Type = 1 and Permission_Group !='General Access';
PeopleTemplate.xlsmSupport 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.xlsmFunctional 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');

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.xlsmAdditional 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.xlsmAdditional 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.xlsmOperational Categorizationselect distinct Service_Categorization_Tier_1,Service_Categorization_Tier_2,Service_Categorization_Tier_3,'' from CFG_Service_Catalog;
ProductCatalog.xlsmProduct 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';

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 (in Microsoft 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.
  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, 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.

  1. Open the Data Management template that contains the invalid data.
  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 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:

  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.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  

-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
-WTrims the blank spaces at end of the column data
-h 1Does 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
-QProvides command line SQL query
SET NOCOUNT ONIf 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

SET COLSEP "^"Defines the ^ character as the column data separator
SET FEEDBACK OFFDoes not print the number of rows for the result at the end of the result
SET ECHO OFFSuppresses the display of commands as they execute
SET PAGES 0Suppresses the display of all headings, page breaks, and titles in the result
SET LINESIZE 32767Wraps the result when there are at least 32767 characters
SET HEADING OFFDoes not print the result header
SPOOL Company.txtSpools the result in the name file for each query
Was this page helpful? Yes No Submitting... Thank you