<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.6944.0">
<TITLE>Re: [rt-users] removing deleted tickets</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText83239 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>I saw them. But If I
drop the database, wouldn't it remove all my settings for each
queues/users/script settings? I only want to remove all tickets and
nothing more.</FONT></DIV></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>-Jonathan<BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Josiah Ritchie
[mailto:jritchie@bible.edu]<BR><B>Sent:</B> Tue 2/17/2004 10:35 AM<BR><B>To:</B>
Jonathan Chen; rt-users@lists.bestpractical.com<BR><B>Subject:</B> Re:
[rt-users] removing deleted tickets<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>See the replies to message "Dropping the RT database". I think
that's what you</FONT> <BR><FONT size=2>want.</FONT> </P>
<P><FONT size=2>JSR/</FONT> </P>
<P><FONT size=2>Jonathan Chen scripted ::</FONT> </P>
<P><FONT size=2>>This script is interesting. I'm not a perl
programmer but I'm</FONT> <BR><FONT size=2>>wondering if it can be modified
to delete ALL tickets from ALL queues so</FONT> <BR><FONT size=2>>RT would
start assigning tickets with #1 again. If it is, how would I</FONT>
<BR><FONT size=2>>go about it? If there is a script like this already
available, please</FONT> <BR><FONT size=2>>lead me to one.
</FONT><BR><FONT size=2>> </FONT><BR><FONT size=2>>Thanks!</FONT>
<BR><FONT size=2>> </FONT><BR><FONT size=2>>-Jonathan</FONT> <BR><FONT
size=2>>Message: 5</FONT> <BR><FONT size=2>>Date: Tue, 17 Feb 2004
09:09:41 +0100</FONT> <BR><FONT size=2>>From: Dirk Pape
<pape-rt@inf.fu-berlin.de></FONT> <BR><FONT size=2>>Subject: Re:
[rt-users] removing deleted tickets</FONT> <BR><FONT size=2>>To: matthew
zeier <mrz@intelenet.net>, rt-users@lists.fsck.com</FONT> <BR><FONT
size=2>>Message-ID: <2147483647.1077008981@eremix></FONT> <BR><FONT
size=2>>Content-Type: text/plain; charset="us-ascii"</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>Hallo matthew,</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>--Am Montag, 16. Februar 2004 10:41 Uhr
-0800 schrieb matthew zeier</FONT> <BR><FONT
size=2>><mrz@intelenet.net>:</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>>> Anyone have a method (script) to remove deleted
tickets and all it's</FONT> <BR><FONT size=2>>> components from the
db?</FONT> <BR><FONT size=2>></FONT> <BR><FONT size=2>>mine is attached.
It works for RT 3 upto 3.0.9. You need to change the</FONT> <BR><FONT
size=2>>first line to the pah of your perl binary.</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>Dirk.</FONT> <BR><FONT
size=2>>-------------- next part --------------</FONT> <BR><FONT
size=2>>#!/export/perl/bin/perl</FONT> <BR><FONT size=2>>#</FONT>
<BR><FONT size=2>># Steve Poirier - <A HREF="<A
href="mailto:steve@inet-technologies.com">mailto:steve@inet-technologies.com</A></FONT>
<BR><FONT size=2>><<A
href="mailto:steve@inet-technologies.com">mailto:steve@inet-technologies.com</A>>
">steve@inet-technologies.com</A></FONT> <BR><FONT size=2>># edited
by Tim Allwine <A HREF="<A
href="mailto:tallwine@oreilly.com">mailto:tallwine@oreilly.com</A></FONT>
<BR><FONT size=2>><<A
href="mailto:tallwine@oreilly.com">mailto:tallwine@oreilly.com</A>>
">tallwine@oreilly.com</A></FONT> <BR><FONT size=2>># to use with
RT2 and MySQL</FONT> <BR><FONT size=2>># edited by Tony Aiuto
<tony@ics.com> to deal with Links</FONT> <BR><FONT size=2>># edited by
Jonas Lincoln <lincoln@unit.liu.se> to work with RT3 and</FONT> <BR><FONT
size=2>>mysql</FONT> <BR><FONT size=2>># edited by Dirk Pape
<pape-rt@inf.fu-berlin.de> to cope with</FONT> <BR><FONT
size=2>>Role-Groups (RT 3)</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>>use strict;</FONT> <BR><FONT size=2>>my $date = shift;</FONT>
<BR><FONT size=2>>my $ticketnbr = 0;</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>>if ( ! $date ) { $date=`/bin/date -I -d "3 months ago"`;
}</FONT> <BR><FONT size=2>>print "$date\n";</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>># Replace this with your
RT_LIB_PATH</FONT> <BR><FONT size=2>>use lib "/export/rt3/lib";</FONT>
<BR><FONT size=2>># Replace this with your RT_ETC_PATH</FONT> <BR><FONT
size=2>>use lib "/export/rt3/etc";</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>>use RT::Interface::CLI qw(CleanEnv loc</FONT>
<BR><FONT
size=2>>
GetCurrentUser GetMessageContent);</FONT> <BR><FONT size=2>>use
RT::Group;</FONT> <BR><FONT size=2>>use RT;</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>#Clean out all the nasties from the
environment</FONT> <BR><FONT size=2>>CleanEnv();</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>#Load etc/config.pm and drop
privs</FONT> <BR><FONT size=2>>RT::LoadConfig();</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>#Connect to the database and get
RT::SystemUser and RT::Nobody loaded</FONT> <BR><FONT
size=2>>RT::Init();</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>>(my $x=RT::Handle->new)->Connect;</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>>my $dbh = $x->dbh() or die "Couldn't
connect to RT database";</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>>my $sql = "SELECT id FROM Tickets WHERE Status=? AND LastUpdated
<</FONT> <BR><FONT size=2>>\'$date\'";</FONT> <BR><FONT size=2>>my $sth
= $dbh->prepare($sql);</FONT> <BR><FONT
size=2>>$sth->execute('deleted');</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>># proceed with deletion</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>>my $d_sth =
$dbh->prepare('SELECT id FROM Transactions where</FONT> <BR><FONT
size=2>>Ticket=?');</FONT> <BR><FONT size=2>>my
$stid = $dbh->prepare('DELETE FROM Attachments
WHERE</FONT> <BR><FONT size=2>>TransactionId=?');</FONT> <BR><FONT
size=2>>my $sttrans = $dbh->prepare( 'DELETE FROM Transactions
WHERE</FONT> <BR><FONT size=2>>Ticket=?');</FONT> <BR><FONT size=2>>my
$stobjects = $dbh->prepare( 'DELETE FROM TicketCustomFieldValues</FONT>
<BR><FONT size=2>>WHERE Ticket=?');</FONT> <BR><FONT size=2>>my
$stlinks1 = $dbh->prepare( 'DELETE FROM Links WHERE
LocalBase=?');</FONT> <BR><FONT size=2>>my $stlinks2 =
$dbh->prepare( 'DELETE FROM Links WHERE LocalTarget=?');</FONT> <BR><FONT
size=2>>my $d_groups = $dbh->prepare( 'SELECT id FROM Groups
WHERE</FONT> <BR><FONT size=2>>Domain="RT::Ticket-Role" AND
Instance=?');</FONT> <BR><FONT size=2>>my $grpmbrs =
$dbh->prepare( 'DELETE FROM GroupMembers WHERE</FONT> <BR><FONT
size=2>>GroupId=?');</FONT> <BR><FONT size=2>>my $groups
= $dbh->prepare( 'DELETE FROM Groups WHERE</FONT> <BR><FONT
size=2>>Domain="RT::Ticket-Role" AND Instance=?');</FONT> <BR><FONT
size=2>>my $stticket = $dbh->prepare( 'DELETE FROM Tickets WHERE
id=?');</FONT> <BR><FONT size=2>></FONT> <BR><FONT size=2>>while ( my
($ticketid) = $sth->fetchrow_array() ) {</FONT> <BR><FONT
size=2>> print "\nticket $ticketid is dead and last updated
before $date.</FONT> <BR><FONT size=2>>Deleting: ";</FONT> <BR><FONT
size=2>> $d_sth->execute($ticketid);</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>># delete Attachments for this
ticket</FONT> <BR><FONT size=2>> print "Attachments.
";</FONT> <BR><FONT size=2>> while ( my ($transid) =
$d_sth->fetchrow_array() ) {</FONT> <BR><FONT
size=2>>
$stid->execute($transid);</FONT> <BR><FONT size=2>>
}</FONT> <BR><FONT size=2>></FONT> <BR><FONT size=2>># delete Ticket in
Transactions table</FONT> <BR><FONT size=2>> print
"Transactions. ";</FONT> <BR><FONT size=2>>
$sttrans->execute($ticketid);</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>># delete TicketRole groups for this ticket</FONT> <BR><FONT
size=2>> print "TicketRole groupmembers. ";</FONT>
<BR><FONT size=2>> $d_groups->execute($ticketid);</FONT>
<BR><FONT size=2>> while ( my ($groupid) =
$d_groups->fetchrow_array() ) {</FONT> <BR><FONT
size=2>>
$grpmbrs->execute($groupid);</FONT> <BR><FONT size=2>>
}</FONT> <BR><FONT size=2>> print "TicketRole groups.
";</FONT> <BR><FONT size=2>>
$groups->execute($ticketid);</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>># delete Ticket in ObjectKeywords table</FONT> <BR><FONT
size=2>> print "ObjectKeywords. ";</FONT> <BR><FONT
size=2>> $stobjects->execute($ticketid);</FONT>
<BR><FONT size=2>></FONT> <BR><FONT size=2>># delete Ticket from the Links
table</FONT> <BR><FONT size=2>> print "Links1. ";</FONT>
<BR><FONT size=2>> $stlinks1->execute($ticketid);</FONT>
<BR><FONT size=2>> print "Links2. ";</FONT> <BR><FONT
size=2>> $stlinks2->execute($ticketid);</FONT> <BR><FONT
size=2>></FONT> <BR><FONT size=2>># delete Ticket from the Tickets
table</FONT> <BR><FONT size=2>> print "Ticket. ";</FONT>
<BR><FONT size=2>> $stticket->execute($ticketid);</FONT>
<BR><FONT size=2>></FONT> <BR><FONT size=2>>
$ticketnbr++;</FONT> <BR><FONT size=2>>}</FONT> <BR><FONT size=2>>print
"\nDone. Deleted $ticketnbr tickets.\n\n";</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>>exit;</FONT> <BR><FONT size=2>></FONT> <BR><FONT
size=2>>------------------------------</FONT> <BR><FONT size=2>></FONT>
<BR><FONT size=2>></FONT> <BR><FONT size=2>> </FONT><BR><FONT
size=2>></FONT> </P><BR>
<P><FONT size=2>-- </FONT><BR><FONT size=2>System Administrator</FONT> <BR><FONT
size=2>Washington Bible College/Capital Bible Seminary</FONT> <BR><FONT
size=2><A href="http://www.bible.edu">http://www.bible.edu</A></FONT> </P>
<P><FONT size=2>Sound words, I know, Timothy is to use,</FONT> <BR><FONT
size=2>And old wives' fables he is to refuse</FONT> <BR><FONT size=2>But yet
grave Paul him nowhere doth forbid</FONT> <BR><FONT size=2>The use of parables,
in which lay hid</FONT> <BR><FONT size=2>That gold, those pearls, and precious
stones that were</FONT> <BR><FONT size=2>Worth digging for, and that with
greatest care.</FONT> <BR><FONT size=2>-- From "The Author's Apology For His
Book"</FONT> <BR><FONT size=2> "The Pilgrim's Progress" by John
Bunyan</FONT> </P></DIV>
</BODY>
</HTML>