Space banner

 

This documentation supports the 19.08 version of Remedy IT Service Management Suite.

To view an earlier version, select the version from the Product version menu.

Best practice use case: Migrating foundation data from BMC Remedy ITSM to a new Remedy environment

The following use case shows how you can use SQL queries to export your foundation data and prepare your Onboarding wizard spreadsheets for migrating foundation data from an existing BMC Remedy ITSM environment to a different BMC Remedy environment; for example, from BMC Remedy ITSM to BMC Helix ITSM. You must ensure that the exported data is formatted correctly so it can be loaded successfully into the Data Management Onboarding wizard.

This topic contains the following information:

SQL queries for exporting the foundation data

Notes


  • 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';

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

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');
People.xlsmPeople

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';
Assignment.xlsmAssignment

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 so that you can 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.

    Note

    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

The following issues should be addressed:

  • 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

You can 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

You can 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  

OptionsPurpose
-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. You can 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, you can 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

OptionsPurpose
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

IBM DB2 client

If you are using the DB2 client, you can use the following formatting options that are in the attached DB2_Export_Foundation.sql batch file. To execute the SQL batch file, connect to the database by using the following command:

db2 connect to AR-DATABASE user DB-USER using DB-PASSWORD

After you are connected to the database, the SQL batch file can be executed by using the following command in the same active terminal session:

db2 -vtf DB2_Export_Foundation.sql

DB2 command options

OptionsPurpose

EXPORT TO Company.txt

Export result to named file

OF DEL MODIFIED BY NOCHARDEL COLDEL ^

Separate columns by caret symbol (^)

Loading your foundation data

After you have created your spreadsheets, you can now load the data by using the Onboarding wizard. For information, see Migrating foundation data to a new Remedy Environment

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Joerg Kirchhoff

    In Onboarding_Sample_PeopleTemplate.xlsm tab Functional Roles the given SQL Query for migrating data results in records with the information for "Member" or "Associate Member" in the column for Functional Role Alias. That does not match the information given in that column description in cell number C1. Is this an error in the query or in the spreadsheet? Regards Joerg

    Feb 11, 2020 02:29
    1. Jyoti Nerkar

      Hello Joerg,


      Thanks for this comment. I will discuss this with an SME and revert.

      Regards,

      Jyoti

      Feb 17, 2020 11:42
      1. Jyoti Nerkar

        Hello Joerg,


        I discussed this scenario with the R&D team. We recommend you to contact the customer support team at BMC because this is a specific use case of data management for migrating data and the customer support team can assist you with the entries in the spreadsheets.


        Hope this helps.


        regards,

        Jyoti

        Apr 01, 2020 01:19