See below. This script was based on Steve Poirier’s ‘cleanup’ script, but
has been modified to kill what I am calling ‘Crazy’ tickets – ones that
for whatever reason have gotten >200 transactions associated with them
(this would never happen naturally in our environment, YMMV).
Also kills ‘dead’ tickets as well as optimizes all the tables. I run this
twice a day.
Dave
#!/usr/bin/perl
to use with RT2 and MySQL
use strict;
use DBI;
definition of variables
my $db=“rt2”;
my $host=“localhost”;
my $user=“rt_user”;
my $password=“”;
my $ticketnbr = 0;
connect to MySQL database
my $dbh = DBI->connect (“DBI:mysql:database=$db:host=$host”,
$user,
$password)
or die “Can’t connect to database: $DBI::errstr\n”;
prepare the query for crazy tickets
my $sth = $dbh->prepare( "
select Ticket, count(id) ct FROM Transactions GROUP BY Ticket HAVING ct>200");
$sth->execute( );
proceed with deletion
while ( my @row = $sth->fetchrow_array( ) ) {
my $ticketid = $row[0];
print "Ticket $ticketid is crazy. Deleting: “;
my $str = $dbh->prepare( "
SELECT id FROM Transactions where Ticket=$row[0]”);
$str->execute( );
delete Attachments for this ticket
print "Attachments. ";
while ( my @row2 = $str->fetchrow_array( ) ) {
my $transid = $row2[0];
$dbh->do( "
DELETE FROM Attachments WHERE TransactionId=$transid");
}
delete Ticket in Transactions table
print "Transactions. ";
$dbh->do( "
DELETE FROM Transactions WHERE Ticket=$ticketid;");
delete Ticket in ticket in Watchers table
print "Watchers. ";
$dbh->do( "
DELETE FROM Watchers WHERE Value=$ticketid;");
delete Ticket in ObjectKeywords table
print "ObjectKeywords. ";
$dbh->do( "
DELETE FROM ObjectKeywords WHERE ObjectId=$ticketid;");
delete Ticket from the Tickets table
print "Ticket. ";
$dbh->do( "
DELETE FROM Tickets WHERE id=$ticketid;");
$ticketnbr++;
}
print “Deleted $ticketnbr crazy tickets.\n”;
reset this.
$ticketnbr = 0;
prepare the query for dead tickets
my $sth = $dbh->prepare( "
SELECT id FROM Tickets where Status=‘dead’;");
$sth->execute( );
proceed with deletion
while ( my @row = $sth->fetchrow_array( ) ) {
my $ticketid = $row[0];
print "Ticket $ticketid is dead. Deleting: “;
my $str = $dbh->prepare( "
SELECT id FROM Transactions where Ticket=$row[0]”);
$str->execute( );
delete Attachments for this ticket
print "Attachments. ";
while ( my @row2 = $str->fetchrow_array( ) ) {
my $transid = $row2[0];
$dbh->do( "
DELETE FROM Attachments WHERE TransactionId=$transid");
}
delete Ticket in Transactions table
print "Transactions. ";
$dbh->do( "
DELETE FROM Transactions WHERE Ticket=$ticketid;");
delete Ticket in ticket in Watchers table
print "Watchers. ";
$dbh->do( "
DELETE FROM Watchers WHERE Value=$ticketid;");
delete Ticket in ObjectKeywords table
print "ObjectKeywords. ";
$dbh->do( "
DELETE FROM ObjectKeywords WHERE ObjectId=$ticketid;");
delete Ticket from the Tickets table
print "Ticket.\n";
$dbh->do( "
DELETE FROM Tickets WHERE id=$ticketid;");
$ticketnbr++;
}
print “Deleted $ticketnbr dead tickets.\n”;
reset this.
$ticketnbr = 0;
prepare the query for orphaned attachments
my $sth = $dbh->prepare( “SELECT at.TransactionId, tr.id
FROM Attachments at LEFT JOIN Transactions tr ON at.TransactionId=tr.id
WHERE tr.id IS NULL”);
$sth->execute( );
proceed with deletion
while ( my @row = $sth->fetchrow_array( ) ) {
my $atid = $row[0];
print "Attachment $atid is orphaned. Deleting.\n";
$dbh->do( "DELETE FROM Attachments WHERE TransactionId=$atid");
$ticketnbr++;
}
print “Deleted $ticketnbr orphaned attachments.\n”;
$dbh->do(“OPTIMIZE TABLE Tickets”);
print “Optimized Tickets.\n”;
$dbh->do(“OPTIMIZE TABLE Transactions”);
print “Optimized Transactions.\n”;
$dbh->do(“OPTIMIZE TABLE Attachments”);
print “Optimized Attachments.\n”;
$dbh->do(“OPTIMIZE TABLE Watchers”);
print “Optimized Watchers.\n”;
$dbh->do(“OPTIMIZE TABLE ObjectKeywords”);
print “Optimized ObjectKeywords.\nDone!\n\n”;
exit;On Tue, 14 Jan 2003, John Giles wrote:
I have RT2 on RedHat 8.0 with MySQL.
I’ve been setting my SPAM mail to the ‘DEAD’ status. Now that
I’ve setup my SPAM filter I’d like to clean up all of the
unwanted tickets.
How can I safely remove all the ‘DEAD’ tickets.
I don’t want to set the tickets to ‘RESOLVED’ because there was
no work performed on the tickets.
Is this advisable ?? Has anyone done any similar clean-up ??
John Giles
-john@trdlnk.com
rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users
Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
RT 2.1.62 should fix it.