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