Delete Organization and its users as well as tickets

emphatic1

I'm looking for a quick method to delete all users and tickets from a single organizational group. I've already tried deleting the users however that leaves the tickets as orphans (without a user)

emphatic1

@ntozier basically this is the issue i'm trying to resolve

ntozier

When you deleted a user, did you select the tick box to delete all their tickets?

If you didn't have that checked then then you are going to have to figure out a way to find all the orphaned tickets and delete them, or give their ownership to another user.

emphatic1

The tickets are not orphaned now, I had backed up before making the changes, the ask now is how do delete the users inside that org as well as all their tickets, its over 1000 users as such manually doing it would not be feasible.

Basically what i did was to find all users that didn't have an organization to begin with and load them into one called No Org
UPDATE ost_user SET org_id=16 WHERE org_id=0;

Now I would like to delete all of those users along with their tickets.

ntozier

Sadly there is no easy way to do this in the ui.

Programmatically if you can create a list of all the users... you could write a script to nuke each piece step by step.
I've asked for better User Management features, and was thinking that it would make a great plugin.

emphatic1

UI Is limited, was hoping you might have a bit more SQL knowledge than i do on the matter I am rather rusty haven't done much SQL scripting in years,

I went ahead and got that plugin from software mods however its still a bit lacking...

Any assistance would be greatly appreciated.

emphatic1

@ntozier with your vast knowledge of the application can you point me in the right direction here

ntozier

Sorry for the delay in the response. I wanted to make sure I had time to write the length response that this was going to require.

Deleting a ticket and/or user at the sql level is a lot more involved than just deleting all tickets with a specific user id.
The problem is that there are a number of other tables that have entries based on the existence of that ticket or user.
the ost_form_entry_values might have data. the ost_thread, ost_thread_collaborator, ost_thread_entry, ost_thread_entry_email, ost_thread_event, and ost_thread_referral tables might also have entries. ost_user, ost_user__cdata, ost_user_account, and ost_user_email. All of those things would have to be purged also.

You can get the user id from ost_user. Then you can use that to delete information from:
ost_user__cdata (you could always just drop this table, it will be recreated)
ost_user_account
ost_user_email

With the ticket_id you then delete entries from the following:
ost_ticket
ost_thread,
ost_thread_collaborator,
ost_thread_entry,
ost_thread_entry_email,
ost_thread_event,
ost_thread_referral

To delete a ticket something like this:

SET @ticket_id = 0;
SET @thread_id = (SELECT `id` FROM `ost_thread` WHERE `object_id` = @ticket_id AND `object_type` = 'T');
SET @thread_entry_id = (SELECT `id` FROM `ost_thread_entry` WHERE `thread_id` = @thread_id);

DELETE FROM `ost_ticket` WHERE `ticket_id` = @ticket_id;
DELETE FROM `ost_ticket__cdata` WHERE `ticket_id` = @ticket_id;
DELETE FROM `ost_thread` WHERE `object_id` = @ticket_id AND `object_type` = 'T';
DELETE FROM `ost_thread_collaborator` WHERE `thread_id` = @thread_id;
DELETE FROM `ost_thread_entry` WHERE `thread_id` = @thread_id;
DELETE FROM `ost_thread_entry_email` WHERE `thread_entry_id` IN(@thread_entry_id);

just replace @ticket_id with the actual ticket_id from ost_ticket

So with that you should have a pretty good starting point I think.

emphatic1

This would be fine if I was deleting a few tickets... I'm not.

What I'm looking to do is delete all users and associated ticket that are currently in a specific organization, tickets wise we're looking at close to probably 5000 hence you can probably see my dilemma.

ntozier

Right. You would need to write a script to get all the users in the Org, then get each users tickets, delete the tickets until they are all gone, then delete the user. Moce on to the next user and repeat. You would also have to make it do it in batches so that the script doesn't time out when deleting a large number of tickets.

Or would have to take the queries above and design one monster query that will do it for you, and hope that it doesn't exceed the query execution timer in MySQL/MariaDB.

emphatic1

I gave up on this 😅

codeaddict

emphatic1

Here is a mysql query I created (with the help of ChatGPT) to mass delete certain tickets and all associated data, proceed with caution as this is very dangerous... and i'm also not sure if I missed any other tables (besides handling attachments)

START TRANSACTION;

-- Store the ticket IDs, related object IDs, and associated thread IDs for data to be deleted in a temporary table
CREATE TEMPORARY TABLE temp_ids_to_delete
(
    temp_id   INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT,
    object_id INT,
    thread_id INT
);

INSERT INTO temp_ids_to_delete (ticket_id, object_id, thread_id)
SELECT ot.ticket_id, th.object_id, th.id AS thread_id
FROM ost_ticket ot
         JOIN ost_thread th ON ot.ticket_id = th.object_id
-- You can modify the where selection to match your needs
WHERE ot.ticket_id = 313;

-- Delete associated records from ost_thread_entry_email first since it queries live data
DELETE
FROM ost_thread_entry_email
WHERE thread_entry_id IN (SELECT id FROM ost_thread_entry WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete));


-- Delete from ost_ticket for specified ticket ID
DELETE
FROM ost_ticket
WHERE ticket_id IN (SELECT ticket_id FROM temp_ids_to_delete);

-- Delete from ost_ticket__cdata for specified ticket ID
DELETE
FROM ost_ticket__cdata
WHERE ticket_id IN (SELECT ticket_id FROM temp_ids_to_delete);

-- Delete from ost_thread row(s) for specified object ID
DELETE
FROM ost_thread
WHERE id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete associated records from ost_thread_entry
DELETE
FROM ost_thread_entry
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete associated records from ost_thread_collaborator
DELETE
FROM ost_thread_collaborator
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);


-- Delete from ost_thread_event
DELETE
FROM ost_thread_event
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete from ost_thread_referral
DELETE
FROM ost_thread_referral
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);


-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_ids_to_delete;

COMMIT;

KevinTheJedi

codeaddict

That doesn't include deleting all associated form data, etc. as well.

Cheers.