Deletion of Calls, Emails and Attachments before a certain date

Deletion of Calls, Emails and Attachments

This article provides instructions on how to delete the following data and files which have been in the system from before a certain date.

▪ Call records which were closed more than a certain number of years ago ▪ Call File Attachments for the identified set of closed calls ▪ Emails sent or received before a certain date ▪ Email Attachments of the identified set of emails

Before Starting

▪ Suitable backups should be in place before deleting and data or files ▪ A period of downtime will be required while the data is being deleted

Deletion of Closed Call records

Please follow the relevant section based on the database type for SwData. The instructions will remove calls which were closed over 10 years ago. The 10 year period is calculated by 10*365*24*60*60.

It is therefore possible to alter this calculation to cater for a different number of years; simply change the 10 for a different digit. For example, the following calculation will remove data which is over 3 years old. 3*365*24*60*60

Supportworks SQL (SwSQL)

The following SQL commands should be run using Interactive SQL

use sw_systemdb;
delete updatedb_call_index from updatedb_call_index join swdata.opencall on swdata.opencall.callref = sw_systemdb.updatedb_call_index.callref where swdata.opencall.status > 15 and swdata.opencall.closedatex <
(unix_timestamp() - (10*365*24*60*60));

delete system_cfastore from system_cfastore join swdata.opencall on swdata.opencall.callref = sw_systemdb.system_cfastore.callref where swdata.opencall.status > 15 and swdata.opencall.closedatex <
(unix_timestamp() - (10*365*24*60*60));

use swdata;
delete updatedb from updatedb join opencall on opencall.callref = updatedb.callref where opencall.status > 15 and swdata.opencall.closedatex < (unix_timestamp() - (10*365*24*60*60));
delete from opencall where status > 15 and swdata.opencall.closedatex < (unix_timestamp() - (10*365*24*60*60));

Microsoft SQL

For MS SQL, the first set of commands below should be run against the MS SQL database in a tool of your choice; the second set should be run against the Supportworks cache database using Interactive SQL:

▪ Run the following against the MS SQL database. The first statement will return a call reference which will then need to be used in subsequent queries. The text [the call reference returned from the first SQL statement] should be replaced with the call reference number.

select top 1 callref from opencall where status > 15 and logdatex < (DATEDIFF(SECOND, '19700101',
GETUTCDATE()) - (10*365*24*60*60)) order by callref desc
delete from updatedb where callref < [the call reference returned from the first SQL statement]
delete from opencall where callref < [the call reference returned from the first SQL statement]

▪ Run the following statements in Interactive SQL

use sw_systemdb;
delete from updatedb_call_index where callref < [the call reference returned from the first SQL statement];
delete from system_cfastore where callref < [the call reference returned from the first SQL statement];

Deletion of call file attachments

The file attachments which are linked to each of the call records which have been deleted above now need to be removed from the server.

▪ Navigate to the folder "<Supportworks Install path>\Supportworks Server\data\cfa_store"

Each folder contains the file attachments for 1000 calls. The attachments for the first 1000 calls are in a folder called "0001". Within each folder, the file name includes the call reference. Based on this information, the relevant folders and files can therefore be deleted.

Deletion of emails from the database

Within the following SQL statements, the date before which data should be deleted should be entered in the format yyyy-mm-dd hh:mm:ss

e.g. 2008-05-15 23:59:59

FOR EXAMPLE The if the required date is 15th May 2008, the first SQL message would become

delete _system_filestore
from _system_filestore
join shared__helpdesk_attachments on shared__helpdesk_attachments.attachId = _system_filestore.fileId
join shared__helpdesk_mailbox on shared__helpdesk_mailbox.msgIdNumeric =
shared__helpdesk_attachments.attachMsgIdNumeric
where shared__helpdesk_mailbox.msgDate < '2008-05-15 23:59:59';

▪ The following statements, once altered with the required date where necessary, should be run in Interactive SQL.

use sw_messagestore;
delete _system_filestore from _system_filestore
join shared__helpdesk_attachments on shared__helpdesk_attachments.attachId = _system_filestore.fileId
join shared__helpdesk_mailbox on shared__helpdesk_mailbox.msgIdNumeric = shared__helpdesk_attachments.attachMsgIdNumeric
where shared__helpdesk_mailbox.msgDate < '[date]';
delete shared__helpdesk_attachments
from shared__helpdesk_attachments
join shared__helpdesk_mailbox on shared__helpdesk_mailbox.msgIdNumeric =
shared__helpdesk_attachments.attachMsgIdNumeric
where shared__helpdesk_mailbox.msgDate < '[date]';
delete shared__helpdesk_recipients
from shared__helpdesk_recipients
join shared__helpdesk_mailbox on shared__helpdesk_mailbox.msgIdNumeric =
shared__helpdesk_recipients.recipMsgIdNumeric
where shared__helpdesk_mailbox.msgDate < '[date]';
delete shared__helpdesk_mailbox
from shared__helpdesk_mailbox
where shared__helpdesk_mailbox.msgDate < '[date]';

Deletion of email attachments

▪ Navigate to the folder "<Supportworks Install path>\Supportworks Server\data\_mb_attstore" ▪ Delete all files which are older than the required date.