Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[MOD] Archive & Delete Old Tickets

edited December 2013 in Mods and Customizations
Getting sick of the volume of old tickets clogging up your DB? Want to keep the text of the messages for posterity but don't want to individually print each one to PDF?

Try this!
http://pastebin.com/Fj8Zy3DV

Save to root ostickets directory, run from command-line. (Like, via cron), When you are happy that it is backing them up properly, simply uncomment the $t->delete(); line, to begin pruning the db.

Required: You must set your staff ID or Username into the Staff constructor near the top, and specify the Backup location define..
ie:
define('BACKUPDIR','/home/backups/oldtickets/');
define('DELETEMONTHS', 6); //change to the number of months you want to delete.
define('MY_USER_ID', Grizly);

TODO: Get attachments inline or something?

Comments

  • Thanks for taking time and writing this
  • edited December 2013
    I've got attachments working!

    The Archive feature is a big hit with the users, I've segmented them based on Department and am including custom data (order number, subject etc) in the filename for ease of searching.

    You might want to run it a few times, the setup has changed a bit, read the comments, by default it doesn't delete anything..


    Caveats:
    • Requires gs/GhostScript to merge PDFs, should disable function if gs not available.
    • Requires GD to convert png's into jpgs, for some reason gs/pdf can't support png's alpha channels
    • Requires linux! Changes its own priority and the gs calls etc are only designed for linux.

    YMMV! Caveat Emptor etc.. 


    Created a GIST for it: https://gist.github.com/clonemeagain/7905624

    Or:

    Save this http://pastebin.com/FvbN7gRt as /include/class.attachment.file.php
    Save this as http://pastebin.com/yjQbAPqR /remove_obsolete_tickets.php

    Tested with a few thousand nodes, not super-fast, but it does work.. Added support to convert docx/xlsx etc into text and attach to node, not sure if you want that or not, but as I'm already merging PDF's and inserting images into the exported file, thought why not!


    * YOU WILL NEED TO CONFIGURE THE FOLLLOWING:
    *
    *  Specify your osTicket username, probably best to use an Admins username for this. DO NOT NEED PASSWORD. (Haven't tried with non-admin username)
    */
    define ( 'MY_USER_ID', 'Grizly' );
    /**
     * Any closed ticket older than this many months will be archived/deleted.
     */
    define ( 'DELETEMONTHS', 1 );
    /**
     * Which subfolder would you like to start archiving tickets into?
     */
    define ( 'BACKUPDIR', getcwd () . DIRECTORY_SEPARATOR . 'backups' . DIRECTORY_SEPARATOR );
    /**
     * Change to false to show more output, makes cron send emails which you may not care about.
     */
    define ( 'CRONFRIENDLY', true );
    /**
     * Change to true to allow anyone on the internet to initiate..
     * not recommended unless you have a remote-cron requirement.
     */
    define ( 'REMOTECRONENABLED', false );
    /**
     * Where do you want your temporary files stored..
     * could be /tmp/ostickets/ or anywhere writable by the user.
     */
    define ( 'URI_FOLDER', getcwd () . DIRECTORY_SEPARATOR . 'scp' . DIRECTORY_SEPARATOR . 'restricted' );
    /**
     * Change this to true, to start deleting.
     * ;-) This will start actually deleting tickets, so, make sure its backing them up properly first.
     */
    define ( 'ACTUALLY_DELETE', false );
  • Dammit, finally found a ticket with an xls file attached, and it seems that isn't covered.. I mean, I'm glad people aren't emailing around docx files, but I would like to test the code!
  • Even Better Version on gist: https://gist.github.com/clonemeagain/7905624

    I've rearranged it all OOP like, hooked it into class.ticket.php and now it overwrites the normal staff ticket-deletion mode.. so, should anyone delete a ticket, first, it will create an export to your defined folder and then delete it.. save you from potential loss of data!
  • I like this idea. I'd like to see this implemented into the new plugin system
  • I'll read up on it.. ;-)
  • Will the original pastebin that was created by Grizly actually delete files once you specify the age?
  • The original tries hard to cover your ass.. As such, you'll need to uncomment line 58.

    Tested on 1.7/1.8, and tickets sure get deleted!

    Should really try it on the new one.
  • The company I work for is running 1.7 or 1.8 at this time I believe and they have a system that is overloaded with over 31k tickets and they want rid of them all after they are 1 month old so I have changed the code to allow one month then delete and backup function is currently commented out so I'll give this a try, thanks so much for the expedient reply Grizly and for the original code that may make my life a lot easier :)
  • Works great in 1.7/1.8 for removing old tickets..been running it since this morning and it's still going strong at over 31k tickets deleted so far.
  • Well now I feel bad, I've never actually turned on the "delete" part, and I found a slight performance flaw in the implementation..

    osTicket likes to "check for orphan chunks" when you delete a file attachment.. which is good, I like that, cleaning up is good.. but if you are deleting a bunch of tickets at once.. then, checking for orphans after every single delete is a massive drain on the db.. :-(

    So, I just (temporarily), disabled that, by modifying class.file.php, and simply returning true immediately:

        /* static */ function deleteOrphans() {
    return true;

    Now, you'll want to turn that back on when your bulk delete is done (by removing the "return true;" line) .. it shouldn't affect individual delete's.. but, its a bit of a bad thing if you are running that kind of union query every single time.. wow. painful.

    Also, it pays to run "OPTIMIZE" over your tables if you have any "overheads" showing.. that will also really speed up a bulk delete. And its generally a good thing anyway. I might schedule an optimize every saturday night or something.

  • Will this work in current version
  • No idea, haven't tested it beyond 1.7.x really.. We are still "in the process" of upgrading servers. I suppose I should revisit all my [mods] after the upgrade.
  • Hey Grizly, Thanks for the hard work putting this together. I have a Db with about 94k tickets and even with the return true; on delete orphans it is incredibly slow. Maybe 2 tickets/second. That's with PDF backup commented out on your original script.

    Just a heads up I tweaked the script slightly for anyone trying to do this on a newer version.
    $select = 'SELECT ticket_id FROM ' . TICKET_TABLE . " WHERE status_id=3 AND closed < DATE_SUB(NOW(), INTERVAL " . DELETEMONTHS . ' MONTH)';

    I'm going to try to track down the performance bottleneck.
  • @mkaatman, what kind of server/db are you running mate, that sounds very slow. 
    What size is your attachments/chunks table? (>10GB?)
    How big are these tickets? (num attachments, num comments etc) 
    How big are the generated PDFs? (avg)
    The orphans thing is important for the db, but it's a bit much after each ticket, hence the return true line, you are supposed to put it back afterwards, then delete another ticket manually to remove the now unlinked chunks.

    It will be slow if you have a slow database/php/filesystem etc.. as it extracts each attached file from the database, saves them to the filesystem, then joins them and the text into a pdf (where attachment is a pdf, merges that into the output)..  It was designed to run as low priority system task via cron, not an interactive "fast as you can" app.. but if there is a serious bottleneck, let us know mate! 
    I've basically set-it-and-forgot-it really, it just runs on my server every week no problems. We get less than a thousand tickets a week, so its just those ancient few hundred that need pruning.. Obviously the first time you run it there would be quite a few though. Hmm. Certainly seems to run faster on MySQL 5.5+
  • I just upgraded to osTicket v1.9.12 and would like to add the mod "[MOD] Archive & Delete Old Tickets" on - gist:https://gist.github.com/clonemeagain/7905624.

    I need some help and advice

    I believe the first section of 7905624 titled "add to main after requires of classes.php" used to mean add the code in the osticket\main.inc.php file.

    I believe that in v1.9.12 it now has to be added to the osticket\bootstrap.php file.

    with the require once at line 180 here:

        function loadCode() {
            #include required files
        require_once(INCLUDE_DIR.'class.mod_archive.php');
            require_once INCLUDE_DIR.'class.util.php';

    AND the "defines" also in the osticket\bootstrap.php file starting on line 59 here:
        function defineTables($prefix) {
            #Tables being used sytem wide
            define('SYSLOG_TABLE',$prefix.'syslog');

    But, I am unsure how to change the five (5) "defines" like this one:
    define ( 'BACKUPDIR', ROOT_DIR . 'backups' . DIRECTORY_SEPARATOR );

    to match the NEW code

    I hope I have included enough information.
    Any help would be greatly appreciated.
  • Is there ANYONE here that can assist me ???
  • You should contact the author of the mod for support.
    https://gist.github.com/clonemeagain/7905624.
  • OK thanks I will do that
  • I should really update it.. :-| 

    FYI: I never got a message from you, so I hope you don't think I was ignoring you.

  • I'm currently making this a plugin.. which might be ok, but might take a bit. 

    Open to suggestions/improvements! Here's the repo (not ready for use yet)

Sign In or Register to comment.