Extracting Contact Data for Service Desk r11 and above with pdm_extract
Article ID: 53861
Updated On:
Products
Issue/Introduction
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" ,"" ,"" ,"[email protected]" ,"" ,"" ,"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.
Environment
Component:
Feedback
