View source for Shredder

== Introduction ==

As of 3.8.0 the Shredder extension is built into RT.

This page documents how to get it working quickly for basic uses.

Shredder has [[#WebUI|web (WebUI)]] and [[#CLI|command line (CLI)]] interfaces. Both are equivalent in terms of available search plugins and options to pick objects.

== WebUI ==

Only users with SuperUser rights can shred through WebUI.

The easiest way to shred tickets (particularly tickets, users, attachments) is to build a custom search with your desired criteria in the WebUI. You will then have a chance to review and select specific objects then remove them from the database while a backup SQL dump is created.

Note that while the interface indicates it accepts DOS-like wildcards (* and ?) these are translated to the standard SQL wildcards of % and _ internally, and you may specify them directly if you prefer. Specifically this means that the claim * matches non-empty sequences is misleading since % will match null.

'''Don't use''' to delete many objects as shredder is quite slow and may hit browser or server timeout that will abort operation. A large number of target tickets may also result in a query to the web server that is larger than the maximum allowable URI length. Use the CLI instead.

== CLI ==

You can use shredder from command line, here is a few commands to help you start:

 perldoc RT/Shredder.pm
 rt-shredder --help
 rt-shredder --plugin help-Tickets
 rt-shredder --plugin help-Users

See a few [[#Examples examples below]].

== Examples ==
Examples presented with shell commands, but the same can be performed in the [[#WebUI |WebUI]].

=== Shred Deleted Tickets by Status and Age ===
You can run the following command by hand and see the results.
 rt-shredder --plugin "Tickets=query,Status = 'Deleted' AND LastUpdated &lt; '30 days ago';limit,100" --sqldump /{somepath}/shredder-restore-tickets.sql

=== Shred Users with no Tickets ===
Users with no tickets are users who have had their tickets deleted -- spam senders, or users whose tickets have been moved to another user.
 rt-shredder --plugin "Users=no_tickets,1;status,any;replace_relations,Nobody;limit,5" --sqldump /{somepath}/shredder-restore-users.sql --force

=== Shred multiple Scrips ===
 /opt/rt4/sbin/rt-shredder --plugin "Objects=Scrip,26;Scrip,28;Scrip,53"


=== Daily cron script to purge deleted tickets and unlinked users ===

'''NOTE:''' The example above to '''delete users with no tickets''' deletes '''new admin users''' who have not yet been assigned or linked with any tickets!

For example, we would create an RT account for a new employee. The cron would then delete their account overnight every day until they had been assigned a ticket.
(Fortunately it is easy to restore a deleted account from the .sql files.)

To prevent new users getting clobbered, I have modified the user query to exclude users of group "Staff" (in our case) and only delete '''unprivileged''' users. This works on RT5.0.5. (Untested on earlier versions.)

   "Users=not_member_of,Staff;member_of,unprivileged;no_tickets,1;status,any;replace_relations,Nobody;limit,50"

You will need to modify this script/query for your use. 

* The limits I have used work fine in our installation, but you may need to reduce (especially for the first run where it would delete lots of tickets or users.)
* For testing purposes, remove the --force option. As it's designed to be run from cron, it does not prompt before deleting.

<source lang="bash">
#!/bin/bash

# robl 20190822
# Script to expunge tickets and all related objects marked as "deleted" after 30 days.
# Database cleanup tasks.
#
# Backup .sql files are retained.

# Set as appropriate:
RT_SHREDDER="/usr/local/sbin/rt-shredder"
BACKUPDIR="/srv/rt5/var/rt-shredder"

# Needs bzip or similar installed:
BZIP="/usr/bin/bzip2"


# Check things exist:

if [ ! -x "$RT_SHREDDER" ] ; then
        echo "ERR: could not execute $RT_SHREDDER."
        exit 1
fi


if [ ! -d "$BACKUPDIR" ] ; then
        echo "ERR: $BACKUPDIR does not exist, trying to create..."
        mkdir -p "$BACKUPDIR"
        chown www-data:www-data $BACKUPDIR
fi

if [ ! -d "$BACKUPDIR" ] ; then
        echo "ERR: $BACKUPDIR could not be created."
        exit 1
fi

# Keep running rt-shredder until we get 0 tickets returned:

while :
  do
    FILEDATE=$(date +%Y-%m-%d-%H%M%S)
    SHREDDED=$($RT_SHREDDER --force --plugin "Tickets=query,Status = 'Deleted' AND LastUpdated < '30 days ago';limit,1000" \
               --sqldump ${BACKUPDIR}/${FILEDATE}-shredder-restore-tickets.sql 2>&1 | grep "RT::Ticket" | wc -l)

    if [ $SHREDDED -ge 1 ] ; then
       echo "$FILEDATE : Shredded $SHREDDED tickets."
       sleep 1
    fi

    if [ $SHREDDED -eq 0 ]; then
      break
    fi

  done


# Purge users with no tickets.
# Spam senders, or users whose tickets have been moved to another user.)

while :
  do
    FILEDATE=$(date +%Y-%m-%d-%H%M%S)
    SHREDDED=$($RT_SHREDDER --force --plugin "Users=not_member_of,Staff;member_of,unprivileged;no_tickets,1;status,any;replace_relations,Nobody;limit,50" \
               --sqldump ${BACKUPDIR}/${FILEDATE}-shredder-restore-users.sql 2>&1 | grep "RT::User" | wc -l)

    if [ $SHREDDED -ge 1 ] ; then
       echo "$FILEDATE : Shredded $SHREDDED users."
       sleep 1
    fi

    if [ $SHREDDED -eq 0 ]; then
      break
    fi

  done


#
# Cleanup operations
#

# Delete any 0 length .sql files left by rt-shredder:
for f in ${BACKUPDIR}/*.sql ; do
    [ -e "$f" ] && /usr/bin/find ${BACKUPDIR} -name '*.sql' -size 0 -exec rm {} \;
    break
done

# Bzip any remaining .sql files:
for f in ${BACKUPDIR}/*.sql ; do
    [ -e "$f" ] &&  ${BZIP} -q ${BACKUPDIR}/*.sql
    break
done

# Remove old backup files after a period of time:
for f in ${BACKUPDIR}/*.bz2 ; do
    [ -e "$f" ] &&  /usr/bin/find ${BACKUPDIR} -name '*.bz2' -ctime +364 -exec rm {} \;
    break
done

</source>

Example for /etc/cron.d/rt5:

<source>
# Purge tickets marked as deleted and related records:
0 23 * * * www-data  /srv/rt5/scripts/RTPurgeDeletedTickets.sh
</source>

== Shredding many tickets ==

You may also be interested in the information in [[ShredderControl]].

== Shred ALL TICKETS ==

'''WARNING WARNING WARNING:'''

If for some reason you want to reset your entire RT instance's TICKETS AND TICKET DATA ONLY (and keep Scrips, Custom Fields, etc), you could do something like the following. This was useful for me when I wanted to take our production RT instance and duplicate it onto a development box but not have the huge database full of tickets and ticket-related data.

=== With shredder and shell script ===

Bourne shell syntax is shown below:

<source lang="bash">
  cd /tmp
  while :
  do
  date
  SHREDDED=`rt-shredder --plugin "Tickets=query,id > 0;limit,100" --force --sqldump foo.sql 2>&1 | grep RT::Ticket | wc -l`
  echo "Shredded roughly $SHREDDED tickets."
  sleep 3 # let the system get a breath
  rm -f foo.sql # we don't care about restoring what we shredded in this case
  if [ $SHREDDED -eq 0 ]; then
      break
  fi
  done
</source>

=== With rt-validator ===

Delete all tickets with SQL command:

 DELETE FROM Tickets;

Use rt-validator to delete records that are broken now:

 ./sbin/rt-validator -c --resolve

=== With rt-delete-tickets-mysql from RT-Extension-Utils ===

Mark all tickets with status deleted with SQL command:

 UPDATE Tickets SET Status = 'deleted';

Use the tool to delete tickets and everything related then check consistency:

  ./sbin/rt-delete-tickets-mysql
  ./sbin/rt-validator -c

Afterward, you will probably want to do the "Shred Users with no Tickets" run as described above as well.

== Speed up shredding process ==
You need to set up the database indexes according to [https://docs.bestpractical.com/rt/4.2.16/RT/Shredder.html#Database-Indexes Shredder documentation (RT 4.2.16)]:
  CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
  CREATE INDEX SHREDDER_CGM2 ON CachedGroupMembers(ImmediateParentId,MemberId);
  CREATE INDEX SHREDDER_CGM3 on CachedGroupMembers (Via, Id);
  CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId, GroupId);
  CREATE INDEX SHREDDER_TXN1 ON Transactions(ReferenceType, OldReference);
  CREATE INDEX SHREDDER_TXN2 ON Transactions(ReferenceType, NewReference);
  CREATE INDEX SHREDDER_TXN3 ON Transactions(Type, OldValue);
  CREATE INDEX SHREDDER_TXN4 ON Transactions(Type, NewValue);
  CREATE INDEX SHREDDER_ATTACHMENTS1 ON Attachments(Creator);

For newer RT versions ([https://docs.bestpractical.com/rt/4.4.6/RT/Shredder.html#Database-Indexes 4.4.6] and also [https://docs.bestpractical.com/rt/4.4.6/RT/Shredder.html#Database-Indexes 5.0.4]) you may need more indexes:
  CREATE INDEX SHREDDER_LINKS1 ON Links(Target);
  CREATE INDEX SHREDDER_ACL1 ON ACL(ObjectType, ObjectId);
  CREATE INDEX SHREDDER_OCFV1 ON ObjectCustomFieldValues(ObjectType, ObjectId);



== Shredder for RT 3.6 and older ==

For older releases extension available from [http://search.cpan.org/dist/RTx-Shredder/lib/RTx/Shredder.pm CPAN].
However, it's '''highly''' recommended to upgrade RT first and use built in solution.
[[Category:Shredder]]
[[Category:Documentation]]