Hi there, Since there was no answer about how to avoid the deletion of escalation times when tickets reach closing states, we decided to create a new table called sla_times and a mysql trigger to fill it with the latest escalation times. Below you'll find the code, any comment about it is much appreciated :) CREATE TABLE IF NOT EXISTS `sla_times` ( `tn` varchar(50) NOT NULL, `fecha1eraRespuesta` datetime NOT NULL, `fechaActualizacion` datetime NOT NULL, `fechaSolucion` datetime NOT NULL, UNIQUE KEY `tn` (`tn`) ) DELIMITER $$ DROP TRIGGER IF EXISTS `otrs`.`copiar_tiempos_SLA`$$ CREATE TRIGGER `copiar_tiempos_SLA` AFTER UPDATE ON `ticket` FOR EACH ROW BEGIN IF NEW.escalation_update_time !=0 THEN REPLACE INTO sla_times (tn,fecha1eraRespuesta,fechaActualizacion,fechaSolucion) SELECT t.tn, FROM_UNIXTIME(t.escalation_response_time), FROM_UNIXTIME(t.escalation_update_time), FROM_UNIXTIME(t.escalation_solution_time) FROM ticket t,ticket_state ts,ticket_state_type tst WHERE t.tn=NEW.tn and t.ticket_state_id=ts.id and tst.id=ts.type_id and tst.id!=3; END IF; END $$ DELIMITER ; Greetings, Leonardo Certuche On Mon, Jan 4, 2010 at 4:13 PM, Leonardo Certuche < leonardo.certuche@itcon-ltda.com> wrote:
Hi again,
I just found that Lokesh Katta Subramanyam reported the same at http://www.mail-archive.com/otrs@otrs.org/msg27661.html If anyone has found a way to keep these escalation values in the ticket table or to query them from the ticket_history table and want to share it with us, I'd deeply appreciated it :)
Thanks in advance,
Leonardo Certuche
On Mon, Jan 4, 2010 at 3:36 PM, Leonardo Certuche < leonardo.certuche@itcon-ltda.com> wrote:
Hello OTRS list,
I wish the best for you all in this new year :)
I have the following question regarding escalation times on OTRS::ITSM:
When a new ticket is created, corresponding values for escalation_response_time, escalation_update_time, and escalation_solution_time are stored in the ticket table. Once the ticket goes to a closed status, these three values become zero. I would like to keep these values after the ticket is closed so we can include them in our reports. Is it possible?
Thanks million,
Leonardo Certuche