Hello, After correcting some buggy behaviors, here is the latest working version of the trigger to store escalation times in a separate table before they're reset when tickets reach closing states. Any comment is much appreciated: USE OTRS; DELIMITER $$ DROP TRIGGER IF EXISTS `otrs`.`copiar_tiempos_SLA`$$ CREATE TRIGGER `copiar_tiempos_SLA` AFTER UPDATE ON `ticket` FOR EACH ROW BEGIN 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 and NEW.escalation_response_time!=0 and NEW.escalation_update_time!=0 and NEW.escalation_solution_time!=0; END $$ DELIMITER ; Leonardo Certuche 2010/1/13 Leonardo Certuche

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.idand 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