[rt-users] Safely removing all 'DEAD' tickets

[David C. Troy]

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
#
# Steve Poirier - steve at inet-technologies.com
# 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 at trdlnk.com
> _______________________________________________
> rt-users mailing list
> rt-users at 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
>