How to remove ticket data from the database

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	Any Supportworks ESP Version

On some occassions you might find that a highly sensitive call was logged and is now visible to all analysts. You could hide this call by creating a data dictionary and associating this call to that data dictionary in the database and ensuring that no one else has access to that call or you could delete only that sensitive call from the system using the guide below:

To begin with we will have to stop all Supportworks services on the Supportworks server configuration and the Swsql server service on the windows services panel. Then we will have to take a backup of the entire Swdata and Sw_systemdb databases before beginning so to do this you will need to arrange some downtime. For now can we just find the places where this call ref is recorded. Can you please open the Interactive SQL and run the following commands to make sure the callref is found in these tables:

the first command is use swdata;

Press Enter

Thereafter run these commands one by one and let me know which tables have this callref value.

select * from bpm_oc_auth where fk_callref = x;
select * from bpm_oc_hist where fk_callref = x;
select * from bpm_oc_task where fk_onflycallref = x;
select * from calltasks where callref = x;
select * from cmn_rel_opencall_oc where fk_callref_m = x;
select * from cmn_rel_opencall_oc where fk_callref_s = x;
select * from cmn_rel_opencall_ci where fk_callref = x;
select * from cmn_rel_opencall_kb where fk_callref = x;
select * from extoc_newequip where opencall = x;
select * from extoc_newstarter where opencall = x;
select * from itsm_oc_uri where fk_callref = x;
select * from itsm_opencall_problem where callref = x;
select * from itsm_opencall_rfc where callref = x;
select * from opencall where callref = x;
select * from updatedb where callref = x;

Also check the following tables:

use sw_systemdb;

press Enter

select * from updatedb_call_index where callref = x; 
select * from opencall_sla where callref = x;
select * from opencall_sla_evts where callref = x;
select * from opencall_sla_exc where callref = x;

Could you also check \program files\hornbill\Supportworks server\data\cfa_store\ and see if there are any attachments matching that call ref value.

These tables are the main ones which will contain call reference information and these are the tables to clear in case you wish to delete the record of one sensitive call.