Extracting Contact Data for Service Desk r11 and above with pdm_extract

Document ID : KB000053861
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

How to get a plain text extract of a single contact from Service Desk on any platform, which would be suitable for use in a basic report, or for reload into a third party application or back into Service Desk after modification.

Solution

Service Desk r11.n stores individual Contact information in two separate tables in the MDB. The first is the common contact information, which is shared by all CA applications in the "ca_contact" table. The second is the Service Desk/CA CMDB specific information stored in "usp_contact."

These two tables are linked via the "id" field. To produce a text extract containing all of the fields relating to one contact, both tables are needed. Such an extract could be used as a report, or form the basis of a data load into other applications, or be modified and reloaded back into Service Desk.

When extracting data, it is often best to start with the ca_contact table, as it contains details - such as the Contact name - which likely will be known and unique. A Contact in Service Desk refers to record stored in these tables, whether it be for an Analyst, Employee, Administrator or even a Group.

The following sequence will extract Service Desk Contact information to a text file.

Use "pdm_extract -h" for help on the options. The parameter "-c" for CSV output is sometimes useful, as is the "-r" which produces a column of data. Usually the standard output is fine for most purposes though.

Example

Step 1. Extracting Data from the ca_contact Table.

For example, we want to extract the entire Contact record belonging to "Jill Smith."

pdm_extract -f "select * from ca_contact where first_name like 'Jill' and last_name like 'Smith'" > ca_contact_JS.txt

NB Note that single and double-quotes are paired. There is a single quote followed by a double-quote after "Smith"

The output in the file will contain all fields in this table and look similar to this:

TABLE ca_contact 
        admin_organization_uuid alias alt_phone_number alternate_identifier comment 
        company_uuid contact_type cost_center creation_date creation_user delete_time 
        department email_address exclude_registration fax_number first_name floor_location 
        id inactive job_function job_title last_name last_update_date last_update_user 
        location_uuid mail_stop middle_name mobile_phone_number organization_uuid 
        pager_email_address pager_number pri_phone_number room_location supervisor_contact_uuid 
        userid version_number 
        { "" ,"" ,"" ,"redky01" ,"" ,"" ,"2307" ,"" ,"06/13/2008 11:42:51", 
        "administrator" ,"" ,"" ,"redky01@noaddress.com" ,"" ,"" ,"Kyle" ,"", 
        "D21A2A363655B2448026BF3FAB896860" ,"0" ,"" ,"" ,"Redgrove", 
        "07/17/2008 16:33:41" ,"redky01" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", 
        "redky01" ,"3" } 

Simpler extracts are possible if not all fields are required. For instance, if only information in the usp_contact table is of interest, only the "id" will be required to link to it:

pdm_extract -f "select id from ca_contact where first_name like 'Jill' and last_name like 'Smith'" > ca_contact_id_JS.txt

Output is:

TABLE ca_contact 
        id 
        { "9BA91F1004067742B4AE167E5A58141C"} 

A basic extract with some key fields is:

pdm_extract -f "select first_name, middle_name, last_name, inactive, id from ca_contact where first_name like 'Jill' and last_name like 'Smith'" > ca_contact_JS.txt

Output is:

TABLE ca_contact 
        first_name middle_name last_name inactive id 
        {"Jill" ,"" ,"Smith" ,"0" ,"9BA91F1004067742B4AE167E5A58141C"} 

Step 2. Extracting Data from usp_contact Table.

The second stage is using the "id" extracted above to query the usp_contact table.

Take the id and add "U" to the front. i.e. U'9BA91F1004067742B4AE167E5A58141C'

pdm_extract -f "select * from usp_contact where id like U'9BA91F1004067742B4AE167E5A58141C'" > usp_contact_JS.txt

NB Again the last two characters are a single apostrophe followed by a double quote.

The output will look similar to this:

TABLE usp_contact 
        c_acctyp_id c_available c_cm_id1 c_cm_id2 c_cm_id3 c_cm_id4 c_domain c_email_service 
        c_nx_ref_1 c_nx_ref_2 c_nx_ref_3 c_nx_string1 c_nx_string2 c_nx_string3 
        c_nx_string4 c_nx_string5 c_nx_string6 c_parent c_schedule c_service_type 
        c_timezone c_val_req c_vendor c_ws_id1 c_ws_id2 c_ws_id3 c_ws_id4 global_queue_id 
        id ldap_dn 
        { "2402" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", 
        "" ,"" ,"" ,"GMT+0100" ,"" ,"" ,"" ,"" ,"" ,"" ,"", 
        "9BA91F1004067742B4AE167E5A58141C" ,"" } 

At this point, a full extract of both tables contains all of the information related to this Contact.

Further Information

See the documentation provided with Service Desk for further information on the pdm_extract command.

Here is the help extract from the command for USRD r11.2.

C:\>pdm_extract -h

Usage: C:\PROGRA~1/CA/SERVIC~1\bin\rgen or pdm_extract

    [ -h (help) ] 
    [ -f (format string) ] 
    [ -c (CSV output) ] 
    [ -e (CSV output with double quotes) ] 
    [ -r (informal report output) ] 
    [ -v (verbose) ] 
    [ -s (saved_script) ] 
    [ -u (no headers) ] 
    [ -d (use dataent.fmt) ] 
    [ -x (locale sensitive numeric formats) ] 
    [ -X (extract all tables except table list) ] 
    ALL | database table names .... 

See also www.support.ca.com for more information on pdm_extract, for example Technical Document: TEC428520.