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.certu...@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.certu...@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 >> > >
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW! http://www.otrs.com/en/support/enterprise-subscription/