I built a MySQL query that easily helps me find all duplicate tickets in the database:
Feel free to use this by the way, this will work with anyone using MySQL. This was used on osTicket 1.11 with no issue:

SELECT t.ticket_id FROM ost_ticket__cdata t WHERE EXISTS 
(SELECT 1 FROM ost_ticket__cdata WHERE ticket_id <> t.ticket_id AND subject = t.subject)
ORDER BY t.ticket_id,

This returns the ticket number and count of duplicates.
I wish to build a script that then takes this data and deletes all duplicates of the ticket, but keeps the ticket with the lowest ticket# (keeps the first version of the ticket that comes in).

Could someone point me to the function/PHP file that is used in osTicket when tickets are deleted? I would like to use this function to run the delete on the duplicates.

ADDITIONAL:

If you want to run this on the command line, and want it to output the ticket# and subject information into a text file you can run the query like so, or even put it in a PHP script to run on a cronjob or something:

SELECT t.ticket_id, t.subject FROM ost_ticket__cdata t WHERE EXISTS 
(SELECT 1 FROM ost_ticket__cdata WHERE ticket_id <> t.ticket_id AND subject = t.subject) 
ORDER BY t.ticket_id INTO OUTFILE '/var/lib/mysql-files/duplicates.txt';

Obviously adjust the path for your mysql-files location accordingly. You may change the location of the mysql-files in the MySQL configuration file (my.cnf). I believe SQL is weird and only lets you output to certain locations on your server, and it also CANNOT over-write files or append to files, so if you run this on a cron you need to make sure it deletes the old/previous file and then writes a new one.

Ticket::delete

Should be defined in class.ticket.php

Very welcome. ?

Let me know how you make out. I would be interested in seeing something like this become a plugin. ?

    ntozier
    Ideally that'll be the case!

    Basically my current concept is to run the MySQL query I have up there, have it export to a txt file containing a line-by-line list of the ticket_id's that are listed as duplicates.

    The PHP script will check the ticket_id's do a comparison on the ticket_created date and then run the ticket::delete function on the respective ticket_id that has the earliest date.

    <?php
include_once(INCLUDE_DIR.'class.ticket.php');
$read = fopen("duplicates.txt", "r");
if ($read) {
        while (($line = fgets($read)) !==false) {
        $line::delete;
        // process the line read.
        }
fclose($read);
} else {
echo "No more lines";
}
?>

    But i'm having a hard time translating the class.ticket.php delete function. Does it take ticket_id's as a variable for deletion? Is there a better way to approach this?

    Only reason I wanted to use the delete function as opposed to deleting the entries in the database directly, is because I'm afraid there are certain tables and entries tied to the tickets and their respective ticket_id's and I don't want to break the system.

    I surmise that the delete function in the class, handles all of that for me?

      You can delete just the ticket record and leave everything else and it'll be fine.

      If you wanted to delete the ticket(s) via PHP however, you'll have to look them up via the ticket_id, and then run the delete function on the object.

      Cheers.

      Sorry I have never used that function and don't actually know.

      Write a Reply...