On Fri, 2004-04-30 at 23:05, Tyler Hepworth wrote:
L. Mark Stone wrote:
Is there a way to change the Customer ID for every existing ticket on a per queue basis? (We have thousands of tickets in our database already, so changing each one manually is not very attractive!)
You are right manual labor is not attractive. Fortunately, SQL is our friend and makes an automated process easy.
Here is an SQL query that will select the queue name and customer id for each ticket associated with a particular queue
select queue.name, ticket.customer_id from queue, ticket where queue.id = ticket.queue_id and queue.name = 'ClientA-Mgt'
This demonstrates how easy it is to find the customer id for every ticket in a queue you specify. Change the statement a little bit and you can easily update the customer_id:
** Warning this code is not tested. Use at your own risk. **
update ticket set customer_id = 'ClientA-Mgt' where queue.id = ticket.queue_id and queue.name = 'ClientA-Mgt'
** You have been warned **
Change queue.name = ' ' for each of your three companies and run the query for each. Voila! You have updated thousands of tickets in a matter of seconds (or destroyed them if you are not careful ;-).
To make this even easier (what agent wants to manually set each customer id as a ticket comes in?) you could create a simple perl script that contains the sql code in it. Then, set the script in a cron to run at periodic intervals. Your tickets will automatically be changed for you at the specified interval and you won't have to lift a finger.
Cheers,
Tyler
Hi Tyler! We are almost there... The first select statement below worked fine: SELECT queue.name, ticket.customer_id FROM queue, ticket where queue.id = ticket.queue_id and queue.name = "First Client Name::Incoming" But the statement that will do the work I need generates errors. Here's the statement, based on the one you gave me: update ticket set customer_id = ClientIDName where queue.id = ticket.queue_id and queue.name = "First Client Name::Incoming" Here are the errors returned by OTRS: Error: Unknown table 'queue' in where clause, SQL: 'update ticket set customer_id = ClientIDName where queue.id = ticket.queue_id and queue.name = "First Client Name::Incoming"' Of course the table queue exists in the database otrs, so I am quite confused at this point (not being a SQL programmer...) Thanks! Mark P.S. Note that I have changed the customer ID and the queue name to protect the client's privacy. -- _____________________________________________________________ A Message From... L. Mark Stone Reliable Networks of Maine, LLC 477 Congress Street, 5th Floor Portland, ME 04101 Tel: (207) 772-5678 Web: http://www.rnome.com