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.
SQL queries for exporting the 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.
|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';|
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),';'),'',Descriptionfrom 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.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=1and Login_ID in (select Remedy_Login_ID from CTM_People where Profile_Status=1 and Company='Replace_Company_Name');
select Support_Organization, Support_group_name,Support_Group_Association_R001,Login_ID from CTM_Support_Group_Association a, CTM_Support_Group bwhere 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_Namewhere 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_Peoplewhere 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_Peoplewhere 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;|
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.CategorizationSchemaKeywordand 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.
- 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 so that you can 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
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.
- 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
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.
You can 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
|-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|
|-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|
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
|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|
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