Changing group and contact information on call request tickets en-masse via db load commands

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

Introduction:

There may be a requirement for a given set of cases to be moved from one group to another en masse.  This document discusses how to perform such an action.

Instructions: 

The following pdm_extract/load commands should run from an Admin command prompt:

Before anything, back up the Call_Req table.  Go to an Administrative Command Prompt on the SDM Primary/Background Server and run:
pdm_extract Call_Req > cr-backup.dat

Backup file will be saved to "cr-backup.dat".

To perform the change:

  1. Identify the uuid values of the contacts and groups you need to use:
    pdm_extract -f "Select id, last_name from ca_contact where last_name = '<group_name>'"

    In this case, you would get an entry such as:

    TABLE ca_contact
    id last_name
    { "8A2310B2DB17C94DA3E0C03068C4217C" ,"Group1" }

  2. Run the above command to obtain the id's for the affected contact, the original group and the new group you wish to change on the call requests. You should get three total UUID values. For the affected contact, you can also run:

    pdm_extract -f "Select id, last_name, first_name from ca_contact where last_name = '<last_name>' and first_name = '<first_name>'"

  3. Pull all of the tickets that name that group and contact as assignee and output to a text file output.dat

    pdm_extract -f "Select id, persid, group_id from Call_Req where group_id = U'' and assignee = U''" > output.dat

    Example:
    pdm_extract -f "Select id, persid, group_id from Call_Req where group_id = U'8A2310B2DB17C94DA3E0C03068C4217C' and assignee = U'10279E68D6FF114599845D7419729412'"

    Result:
    TABLE Call_Req
    id persid group_id
    { "400101" ,"cr:400101" ,"8A2310B2DB17C94DA3E0C03068C4217C" }
    { "400060" ,"cr:400060" ,"8A2310B2DB17C94DA3E0C03068C4217C" }

  4. Edit output.dat and replace all of the UUID's that come from the old group with the UUD's from the new group. The edit can be done with a text editor and doing a find/replace of the string entries should perform the task well. Save the changes to output.dat

  5. Finally, load the changes in output.dat:
    pdm_load -f output.dat

Additional Information:

Please make sure to test this in a testing environment, to make sure you are familiar with the instructions given.  The above instructions involve direct data manipulation, which, if done improperly, may result in data corruption.  As a result, please make sure you have performed all necessary backups of the installation in advance of executing the above steps in a production environment.

See also:

TEC480416
I want to change the Status / Priority / Problem / Parent / Assignee of all the tickets of a particular person / Group. How can I do this?

TEC1264004
How do I add the Group column to the Request List form for purposes of "Edit-In-List"? (useful to add the group field as an "Edit-In-List" action)