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

Bulk reassigning calls

I have a user set up that has an email address that is shared amongst many staff that work on the helpdesk.  We tried to use OSTicket with this "shared email user" but, as I predicted, the number of calls has increased and now those that use this shared account want to move to having their own OSTicket account so that they get appropriate notifications.  When they email in to OSTicket the message still has their own personal email footer despite it coming from a shared address 

I went to delete the shared email user account but it says its going to remove that users tickets too, approx 60 tickets.  I don't want to delete the previous tickets.
In the database I can search through the threads in xxx_thread_entry (93 entries) and find the "real" user for each thread from their email footer text in the body field of the record.  I was going to use this to update the userid for the ticket entry so that there are no tickets left against the shared users user id. However I can;'t see how the threads and tickets are linked.  Where I expected to find a ticket ID in the thread table there was none, nor can I find a many to many join table of ticketIDs and thread IDs.  

How are threads and tickets linked?

Am i over complicating this?  Is there an easier way?

I've got a duplicate system on v1.10 to test this on... and a backup

Comments

  • You should be able to just modify the ticket table with the new user id. Threads are linked to that table by the ticket ID, not user. Obviously try it on your test setup first, good plan!
  • Grizly
    Thanks for that. 
    I need to look in the first thread entry for each ticket to check the user name from their email footer which is in the body text.  Each ticket currently with a user ID of the shared user could be one of about a dozen people.  I can't see how the threads are linked to the ticket (which fields are used?) so that I can update each ticket to the user whose name appears in the footer of the first thread of that ticket.  I can't see the ticket ID in the thread tables.


    Chris
  • The actual text is in the thread entry values table I think, joined with thread entry, joined with ticket.. or thread then ticket. Quite convoluted indeed. I'm a few beers in and it's bedtime or I'd fire up a computer and have a proper look, if you still need a hand in the morning I'll help you out.
  • Hey, thanks for getting back so quickly. ;-)  I can pick this up later in the week if you have time please.
    I'll take another look now and see if I can work it out.  Based on what you've just said I'm looking in roughly the right location....  If I can get the join for my select to links the tickets and threads I should be sorted.

    hmmm beer! Its lunchtime here!  I'd better not! :-(

    C
  • Found it.  the Ticket ID in the thread_entry  table is cunningly disguised in a field named "thread_id"...

    Thanks again!
  • In case anyone is wanting to do the same here's how I reallocated tickets in bulk. 

    Note : I tested this on my update test bed using 1.10 and it seems ok.  I tried applying the same to my v1.9 database and there are enough differences that this does not work on 1.9. 

    As always:
    Make a backup, your mileage may vary, do this at your own risk, don't try this at home, all stunts are performed by professionals in controlled circumstances etc and all the other usual disclaimers.  Only do this is you are confident you can fix it again by yourself afterwards.  

    1) Reallocating tickets to another user where that users name is in the thread body :
    set @olduserid = 27;
    set @newuserid = 29;
    set @newusersName = "%Forest%";
    update ost_ticket ot
    set ot.user_id = @newuserid
    where ot.user_id = @olduserid and ot.ticket_id in (
       select thread_id from ost_thread_entry ote
       where ote.body like @newusersName 
       and ote.user_id = @olduserid);

    I've just used an example here.  In my actual query I used the HTML of their email footer that appears in the body of the thread. You need to work out what is unique in your situation.

    The old user may also be a collaborator in some tickets so use this to remove them from that too

    set @olduserid = 27;
    set @newuserid = 29;
    update `ost_thread_collaborator` otc
    set user_id = @newuserid 
    WHERE `user_id` = @olduserid;

    At this point you should be able to go into your user directory in the Agent interface and there should be no tickets against your old user so you should be able to delete their entry.

    You may also have some other threads allocated to the old user id that you want to tidy up.

    set @olduserid = 27;
    set @newuserid = 29;
    set @newuserFullName = 'Forest';
    update `ost_thread_entry` ote
    set user_id = @newuserid , poster = @newuserFullName
    WHERE `user_id` = @olduserid ;
  • Awesome scripts mate, thanks for sharing them!
Sign In or Register to comment.