[rt-users] Dead and Resolved Tickets Pruning

Paulo Matos pjsm at fct.unl.pt
Thu Apr 29 09:39:01 EDT 2004


	Hi Pedram!

On Wed, 28 Apr 2004, Pedram M wrote:

> I have had RT2 setup for quite a while now, and receive over 300 email's per
> day, so the database is getting quite huge.
> I wanted to know if anyone has a good idea on how to prune all the dead and
> resolved tickets that are like over 3 months old.
> Because Im sure I dont need those anymore.

This is one of the "Most Wanted Features" of RT. Look at the list.

I'm attaching a tool that I found on the list...

Note that this tool is NOT supported by RT devels nor maintained by anyone 
to my knowledge... use it at your own risk. Read the comments inside the file.

Hope this helps...

--
	Paulo Matos
 ----------------------------------- ----------------------------------
|Sys & Net Admin                    | Serviço de Informática           |
|Faculdade de Ciências e Tecnologia | Tel: +351-21-2948596             |
|Universidade Nova de Lisboa        | Fax: +351-21-2948548             |
|P-2829-516 Caparica                | e-Mail: pjsm at fct.unl.pt          |
 ----------------------------------- ----------------------------------
-------------- next part --------------
#!/usr/bin/perl
#
# purge-dead.pl v 0.05 2004.04.29
#
# Changelog:
#
# * 2004.04.29 Paulo Matos <paulo.matos at fct.unl.pt>
# - set version 0.05 to file
# - added changelog and some comments
#
# * Jonas Lincoln <lincoln at unit.liu.se>
# - edited to work with RT3 and mysql
#
# * Tony Aiuto <tony at ics.com>
# - edited to deal with Links
#
# * Tim Allwine <tallwine at oreilly.com>
# - edited to use with RT2 and MySQL
#
# * Steve Poirier <steve at inet-technologies.com>
# - initial script
#
#
# COMMENTS
#
# - This tool is NOT supported by RT developers nor maintained by anyone until
# the moment! It has been living from one contrib here another there...
# 
# - Think *TWICE* before using it... this messes directly with the Database!
#
# - If you found a major bug on it use rt-users list to report it, those who
# are using this would appreciate it and the bug might be solved.
#
use strict;
my $ticketnbr = 0;

# Replace this with your RT_LIB_PATH
use lib "/service/rt3/lib";
# Replace this with your RT_ETC_PATH
use lib "/service/rt3/etc";

use RT::Interface::CLI  qw(CleanEnv loc
			   GetCurrentUser GetMessageContent);
use RT::Group;
use RT;

#Clean out all the nasties from the environment
CleanEnv();

#Load etc/config.pm and drop privs
RT::LoadConfig();

#Connect to the database and get RT::SystemUser and RT::Nobody loaded
RT::Init();

(my $x=RT::Handle->new)->Connect;

my $dbh = $x->dbh() or die "Couldn't connect to RT database";

my $sql = q{SELECT id FROM Tickets where Status=?};
my $sth = $dbh->prepare($sql);
$sth->execute('deleted');


# proceed with deletion

my $d_sth     = $dbh->prepare('SELECT id FROM Transactions where Ticket=?');
my $stid      = $dbh->prepare('DELETE FROM Attachments WHERE TransactionId=?');
my $sttrans   = $dbh->prepare( 'DELETE FROM Transactions WHERE Ticket=?');
#my $stwatch   = $dbh->prepare( 'DELETE FROM Watchers WHERE Value=?');
my $stobjects = $dbh->prepare( 'DELETE FROM TicketCustomFieldValues WHERE Ticket=?');
my $stlinks1  = $dbh->prepare( 'DELETE FROM Links WHERE LocalBase=?');
my $stlinks2  = $dbh->prepare( 'DELETE FROM Links WHERE LocalTarget=?');
my $stticket  = $dbh->prepare( 'DELETE FROM Tickets WHERE id=?');

while ( my ($ticketid) = $sth->fetchrow_array() )  {
    print "\nticket $ticketid is dead. Deleting:  ";
    $d_sth->execute($ticketid);

# delete Attachments for this ticket
    print "Attachments. ";
    while ( my ($transid) = $d_sth->fetchrow_array() )  {
	$stid->execute($transid);
    }

# delete Ticket in Transactions table
    print "Transactions. ";
    $sttrans->execute($ticketid);

# delete Ticket in ticket in Watchers table
#    print "Watchers. ";
#    $stwatch->execute($ticketid);

# delete Ticket in ObjectKeywords table
    print "ObjectKeywords. ";
    $stobjects->execute($ticketid);

# delete Ticket from the Links table
    print "Links1. ";
    $stlinks1->execute($ticketid);
    print "Links2. ";
    $stlinks2->execute($ticketid);

# delete Ticket from the Tickets table
    print "Ticket. ";
    $stticket->execute($ticketid);

    $ticketnbr++;
}
print "\nDone. Deleted $ticketnbr tickets.\n\n";

exit;



More information about the rt-users mailing list