This script is interesting. I’m not a perl programmer but I’m wondering if it can be modified to delete ALL tickets from ALL queues so RT would start assigning tickets with #1 again. If it is, how would I go about it? If there is a script like this already available, please lead me to one.
Thanks!
-Jonathan
Message: 5Date: Tue, 17 Feb 2004 09:09:41 +0100
From: Dirk Pape pape-rt@inf.fu-berlin.de
Subject: Re: [rt-users] removing deleted tickets
To: matthew zeier mrz@intelenet.net, rt-users@lists.fsck.com
Message-ID: 2147483647.1077008981@eremix
Content-Type: text/plain; charset=“us-ascii”
Hallo matthew,
–Am Montag, 16. Februar 2004 10:41 Uhr -0800 schrieb matthew zeier
mrz@intelenet.net:
Anyone have a method (script) to remove deleted tickets and all it’s
components from the db?
mine is attached. It works for RT 3 upto 3.0.9. You need to change the
first line to the pah of your perl binary.
Dirk.
-------------- next part --------------
#!/export/perl/bin/perl
to use with RT2 and MySQL
edited by Tony Aiuto tony@ics.com to deal with Links
edited by Jonas Lincoln lincoln@unit.liu.se to work with RT3 and mysql
edited by Dirk Pape pape-rt@inf.fu-berlin.de to cope with Role-Groups (RT 3)
use strict;
my $date = shift;
my $ticketnbr = 0;
if ( ! $date ) { $date=/bin/date -I -d "3 months ago"
; }
print “$date\n”;
Replace this with your RT_LIB_PATH
use lib “/export/rt3/lib”;
Replace this with your RT_ETC_PATH
use lib “/export/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 = “SELECT id FROM Tickets WHERE Status=? AND LastUpdated < '$date'”;
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 $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 $d_groups = $dbh->prepare( ‘SELECT id FROM Groups WHERE Domain=“RT::Ticket-Role” AND Instance=?’);
my $grpmbrs = $dbh->prepare( ‘DELETE FROM GroupMembers WHERE GroupId=?’);
my $groups = $dbh->prepare( ‘DELETE FROM Groups WHERE Domain=“RT::Ticket-Role” AND Instance=?’);
my $stticket = $dbh->prepare( ‘DELETE FROM Tickets WHERE id=?’);
while ( my ($ticketid) = $sth->fetchrow_array() ) {
print "\nticket $ticketid is dead and last updated before $date. 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 TicketRole groups for this ticket
print "TicketRole groupmembers. ";
$d_groups->execute($ticketid);
while ( my ($groupid) = $d_groups->fetchrow_array() ) {
$grpmbrs->execute($groupid);
}
print "TicketRole groups. ";
$groups->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;
winmail.dat (7.48 KB)