<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1491" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2><SPAN
class=229410221-04072005>Hello:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=229410221-04072005></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=229410221-04072005>I've encountered
some difficulties with RTx-shredder, so I wrote the following SQL script which
resolved my problem in removing deleted tickets and I would like to share
it.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=229410221-04072005><FONT face=Arial
size=2>==================================================================================</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2>-- this script will delete all tickets and
corresponding objects/records where the status of the ticket is "deleted"<BR>--
this script can be modified as needed<BR>-- the tables affected in ticket(s)
cleanup are:<BR>--
attachments, cachedgroupmembers, groups, objectcustomfieldvalues, transactions
and tickets<BR>-- and should be executed in the same order<BR>-- note the
relationship between each table<BR>--</FONT></DIV>
<DIV><FONT><SPAN class=229410221-04072005></SPAN><FONT face=Arial size=2>-<SPAN
class=229410221-04072005>-</SPAN><BR>delete attachments where transactionid in
(select id from transactions where objecttype like 'RT::Ticket' and
objectid<SPAN class=229410221-04072005> </SPAN>in (select effectiveid from
tickets where type like 'ticket' and status like 'deleted'));<BR>--<BR>delete
cachedgroupmembers where groupid in (select id from groups where domain like
'RT::Ticket-Role' and instance in<SPAN class=229410221-04072005> </SPAN>(select
effectiveid from tickets where type like 'ticket' and status like
'deleted'));<BR>--<BR>delete groups where domain like 'RT::Ticket-Role' and
instance in (select effectiveid from tickets where type like 'ticket' and status
like 'deleted');<BR>--<BR>delete objectcustomfieldvalues where objecttype like
'RT::Ticket' and objectid in (select effectiveid from tickets where type like
'ticket' and status like 'deleted');<BR>--<BR>delete transactions where
objecttype like 'RT::Ticket' and objectid in (select effectiveid from tickets
where type like 'ticket' and status like 'deleted');<BR>--<BR>delete tickets
where type like 'ticket' and status like 'deleted';<BR></FONT></FONT></DIV>
<DIV><FONT><FONT><SPAN class=229410221-04072005><FONT face=Arial
size=2>============================================================================</FONT></SPAN></FONT></FONT></DIV>
<DIV><FONT><FONT><SPAN class=229410221-04072005><FONT face=Arial
size=2></FONT></SPAN></FONT></FONT> </DIV>
<DIV><FONT><FONT><SPAN class=229410221-04072005><FONT face=Arial
size=2>Iris</FONT></SPAN></DIV></FONT></FONT></BODY></HTML>