Basic example of loading data into CA CMDB with ADT Workflow

Products

• CA IT Asset Manager
• CA Software Asset Manager (CA SAM)
• ASSET PORTFOLIO MGMT- SERVER
• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

Description:

The following steps describe the basic approach to creating an ADT Workflow to load data into the CA CMDB.

Solution:

The help in relation to ADT Mapper Workflow states the following:

The purpose of a workflow is to automate tasks by scheduling a job containing one or more programs to run at a later time outside the Mapper program. Once a workflow has been designed and tested from the Workflow palette, you can generate it to a special file with the extension of wfx. This file contains all the information necessary to run the workflow. The Data Transformer Executor application is used to run wfx files from a command line prompt.

A standard naming convention is used during the generate process. The file, named for the workflow program, is stored in the bin directory of the Advantage Data Transformer application. A folder titled with the metadata store DSN is created, and a Status folder is then created under the DSN folder. Each workflow is stored according to the metadata store and the status. This location cannot be changed for generating the workflows. However, once the file has been generated, you can move the file anywhere as long as the programs referenced can still be found from the point of execution.

Taking into account the definition of an ADT Workflow lets use the information provided in the Advantage Data Transformer Getting Stared Guide prepare and load some Configuration Item data into CA CMDB.

The same approach for importing the data manually can be adopted and then automated via a Workflow.

1. Creating Excel Spreadsheets for data load
   
   A Microsoft Excel Spreadsheet template file exist on which you can base your data load file on. If you have installed CA CMDB on top of Service Desk installation its location will be:
   
   C:\Program Files\CA\Service Desk\cmdb\data\federationadapters\cidata.xls
   
   Most users will use subset of this file to load in their data. For example you may want to load data that comes under the family Hardware.Workstation.
   
   Below is some sample data (Figure 1) that will be uploaded to the CA CMDB via the ADT Workflow:
   
   

   <Please see attached file for image>

   
   Figure 1
   
   This was created taking a copy of the cidata.xls file mentioned above and modifying it with intention of loading just Hardware.Workstation family.
   
   Once you have created your spreadsheet you must create an ODBC System Data Source Name (DSN) for the newly created spreadsheet:
   
   
1. Start the ODBC Data Source Administrator.
   
   
2. Click the System DSN tab.
   
   

   <Please see attached file for image>

   
   Figure 2
   
   
3. Click the Add button (Figure 2)
   
4. Select the Microsoft Excel Driver (*.xls) (Figure3)
   
   

   <Please see attached file for image>

   
   Figure 3
   
5. Enter in a Data Source Name, description then the click the Select Workbook button to navigate to the spreadsheet you created and loaded data into.
   
   

   <Please see attached file for image>

   
   Figure 4
   
   
6. Log into ADT Mapper so that the data from the spreadsheet can be scanned:
   
7. Select File -> Scanners -> ODBC
   
   
8. Expand the Microsoft Excel Driver (*.xls) node beneath All ODBC Data Sources and navigate to the spreadsheet System DSN you created earlier then click the 'Ok' button. (Figure 5)
   
   

   <Please see attached file for image>

   
   Figure 5
   
   
2. Create and set up source table
   
   

You either use existing application, the CMDBFederationAdapters or create your own application to keep these separate from the default. Here a new application then program has been created beneath Applications on the programs tab. (Figure 6)

<Please see attached file for image>

Figure 6

This was done doing the following:

1. Right click Applications on the ADT program tab
   
   
2. Select 'New...' from the dropdown menu then select Application
   
   
3. Once created the new application has been created right click it and select 'New...' from the dropdown menu then select Program
   
   
4. Once the program has been created click on the data tab then drag and drop the Excel spreadsheet into the program mapping palette which is to the right of the 'Browse Metadata Store window.
   
   
5. Right click the source table, click on the profile tab and make sure the option to override the default profile name is ticked. The value within the field should be cmdb_profile_xls
   
   

   <Please see attached file for image>

   
   Figure 7
   
   

• Create and setup target table
  
  
  1. For your newly created problem we will add the target table.
  
  
  
• Click back on the data tab

• Expand the XMLServer

• Open the CMDB_FederationAdapters folder

• Drag and drop the adapter you will using as the target table, in this instance it was the HardwareWorkstation.

• Right click the table and select the Target Table option the dropdown menu.

• Click the table again and select Properties.

• Click on the profile tab and make sure the option to override the default profile name is ticked. The text value in the field should be cmdb_profile_xml

<Please see attached file for image>

Figure 8

• Mapping attributes
  
  
  1. Now that the program mapping palette contains a source and target table their columns can be mapped (Figure 9).
     
     
  2. This is done by a click on a column in the source table and dragging to an equivalent column in the target table.
     
     
  3. Once all the columns have been mapped right click each table so that the filter column option can be selected to reduce the number of columns displayed.
     
     

     <Please see attached file for image>

     
     Figure 9
     
     
• Add the GRLoader.exe as Workflow Program Type
  
  The GRLoader is not in the list of Program Types installed out-of-the-box it must added via the ADT Workflow Administrator.
  
  To open the Workflow Administrator, go to the Tools menu in the mapper and Select Workflow Administrator. ADT comes with a default set of Program Type Definitions that you can use/modify. You can also create your own set of Definitions and leave the standard ones in place. One you have finished adding/modifying the Program Type Definitions or if you chose the default definitions, you can then scan those definitions into the Metadata store so they are accessible by the Mapper application.
  
  So to add the GRLoader as program type please to the following:
  1. Launch the Workflow Administrator
     
     
  2. Select Edit -> New Program Type
     
     
  3. Click of the browser button for 'Enter default folder of executable (optional)'. Navigate to the GRLoader.exe, select it and click the open button.
     
     
  4. This will populate the 'Enter file name of executable' field
     
     
  5. The program definition name, description and version can then all be entered in manually (Figure 10)
     
     
  6. Now click on the Program Type Parameters to add the required parameters for GRLoader which -u (Userid for logon), -p (Password for logon), -s (Server URL including port) and -i ( Input XML file). All the possible parameters can be view by typing grloader -h from the DOS prompt:
     
     
     

     <Please see attached file for image>

     
     Figure 10
     
  7. All the above were given a parameter type of 'Text' apart from the password which was given the type 'Password' (Figure 11)
     
     
     

     <Please see attached file for image>

     
     Figure 11
     
     
  8. So to make this available to the ADT Mapper you must select File -> 'Scan into Metadata Store...'
     
     
  9. You will be prompted with popup message asking you whether you the question 'All previous scanned Workflow Program Type Definitions will be deleted. This may invalidate existing workflows. These Workflow Program Types will be overwritten with definitions' These program definition types will be list below this message and will have the option to click 'Ok' or 'Cancel'. Click 'Ok'.
     
     
  10. You can now close the Workflow Administrator and you should now see the GRLoader as Workflow Program Type within the ADT program tab.
      
• Create a new Workflow
  
  1. Create a new Workflow by right clicking Workflows on the Programs table of the Browse Metadata Store then select 'New' followed by 'Workflow'. Enter in a Workflow name then click 'Apply' button (Figure 12):
     
     

     <Please see attached file for image>

     
     Figure 12
     
     
  2. Once you have created the definition you can drag and drop the mapper program for the Hardware.Workstation spreadsheet into the Workflow program palette. Do the same for the GRLoader Workflow Program Type.
     
     
  3. Link the two by click on the newly created mapper program and when you see a horizontal white arrow dragging it onto the GRLoader (Figure 13).
     
     

     <Please see attached file for image>

     
     Figure 13
     
     
  4. Then right click the GRLoader object then select properties and click on the variables tab and populate the user id, password, URL and XML variables with correct information. You need to create a dummy XML file because the XML variable will not accept a file that does not exist.
     
     
  5. Now generate your Workflow right clicking the palette and selecting 'Generate Workflow'. This should create a wfx in a folder specified by the Workflow tab of the output window:
     
     C:\Program Files\CA\Advantage Data Transformer\bin\IDB\DEV\WF_Load.wfx
     
     
• Run ADT Workflow as scheduled job
  1. Click on the Schedule Workflow icon

     <Please see attached file for image>

     on the toolbar. This will bring up the Schedule Workflow dialog (Figure 14).
     
     

     <Please see attached file for image>

     
     Figure 14
     
     
  2. Click on the schedule tab to enter when you would like this workflow to run. Then back on the Task tab make sure the correct user id and password are used to run the scheduled task. Click the 'Ok' button
     
     
  3. Check that a schedule task and been created by selecting Start>Programs>Accessories>System Tools and click on Scheduled Tasks. You should see your workflow Scheduled. Start>Programs>Accessories>System Tools and click on Scheduled Tasks. You should see your workflow Scheduled. Start -> Programs -> Accessories -> System Tools and click on Scheduled Tasks. You should see your workflow Scheduled (Figure 15).
     
     
     

     <Please see attached file for image>

     
     Figure15
     
  4. Once the scheduled task has run you can check that the CI data has been successfully loaded by logging into CMDB and searching for the data you have loaded via a scheduled task.

Environment

Release:
Component: ARGIS