STATS - Ticket closed with SLA Violation - otrs

STATS - Ticket closed with SLA Violation

Ruslan

25 Aug 2010 25 Aug '10

12:16 p.m.

hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion?

Show replies by date

Leonardo Certuche

25 Aug 25 Aug

7:30 p.m.

Hello, I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here. 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`) ) USE OTRS; 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 ; Leonardo Certuche www.itconsultores.com.co On 25 August 2010 07:16, Ruslan wrote:

...

hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion? --------------------------------------------------------------------- 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

Ruslan

27 Aug 27 Aug

12:04 p.m.

Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module ) On 25 авг, 23:30, Leonardo Certuche wrote:

...

Hello,

I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

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`) )

USE OTRS; 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 ;

Leonardo Certuchewww.itconsultores.com.co

On 25 August 2010 07:16, Ruslan wrote:

...
hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion? --------------------------------------------------------------------- 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

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

Leonardo Certuche

2:45 p.m.

Hello Ruslan, I'm glad you like it :D As you can see it's a workaround tropical-style. If you get the chance to improve it or to come with a better approach, could you please share it with us? Thanks! Leonardo Certuche www.itconsultores.com.co Medellín, Colombia On 27 August 2010 07:04, Ruslan wrote:

...

Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module )

On 25 авг, 23:30, Leonardo Certuche wrote:

...
Hello,

I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

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`) )

USE OTRS; 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 ;

Leonardo Certuchewww.itconsultores.com.co

On 25 August 2010 07:16, Ruslan wrote:

...
hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion? --------------------------------------------------------------------- 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

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

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

Ruslan

30 Aug 30 Aug

9:16 a.m.

One more question - How did u getting stats using this new table "sla_times" ? On 27 авг, 18:45, Leonardo Certuche wrote:

...

Hello Ruslan,

I'm glad you like it :D

As you can see it's a workaround tropical-style. If you get the chance to improve it or to come with a better approach, could you please share it with us?

Thanks!

Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

On 27 August 2010 07:04, Ruslan wrote:

...
Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module )

...
On 25 авг, 23:30, Leonardo Certuche wrote:

...
Hello,

...
...
I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

...
...
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`) )

...
...
USE OTRS; 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 ;

...
...
Leonardo Certuchewww.itconsultores.com.co

...
...
On 25 August 2010 07:16, Ruslan wrote:

...
...
...
hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion? --------------------------------------------------------------------- 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

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

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

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

Leonardo Certuche

2:24 p.m.

Hello there, I'd love to know how but true to be told, we're deploying our own reporter web-based and with OTRS integrated authentication in which a full set of reports based on ITIL PKIs are shown Leonardo Certuche www.itconsultores.com.co Medellín, Colombia On 30 August 2010 03:16, Ruslan wrote:

...

One more question - How did u getting stats using this new table "sla_times" ?

On 27 авг, 18:45, Leonardo Certuche wrote:

...
Hello Ruslan,

I'm glad you like it :D

As you can see it's a workaround tropical-style. If you get the chance to improve it or to come with a better approach, could you please share it with us?

Thanks!

Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

On 27 August 2010 07:04, Ruslan wrote:

...
Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module )

...
On 25 авг, 23:30, Leonardo Certuche wrote:

...
Hello,

...
...
I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

...
...
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`) )

...
...
USE OTRS; 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 ;

...
...
Leonardo Certuchewww.itconsultores.com.co

...
...
On 25 August 2010 07:16, Ruslan wrote:

...
...
...
hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion?

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

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

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

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

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

Ruslan

3:59 p.m.

You develop it for own purposes or we can check it and m.b. buy rgis sytem? On 30 авг, 18:24, Leonardo Certuche wrote:

...

Hello there,

I'd love to know how but true to be told, we're deploying our own reporter web-based and with OTRS integrated authentication in which a full set of reports based on ITIL PKIs are shown

Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

On 30 August 2010 03:16, Ruslan wrote:

...
One more question - How did u getting stats using this new table "sla_times" ?

...
On 27 авг, 18:45, Leonardo Certuche wrote:

...
Hello Ruslan,

...
...
I'm glad you like it :D

...
...
As you can see it's a workaround tropical-style. If you get the chance to improve it or to come with a better approach, could you please share it with us?

...
...
Thanks!

...
...
Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

...
...
On 27 August 2010 07:04, Ruslan wrote:

...
...
...
Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module )

...
...
...
On 25 авг, 23:30, Leonardo Certuche wrote:

...
Hello,

...
...
...
...
I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

...
...
...
...
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`) )

...
...
...
...
USE OTRS; 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 ;

...
...
...
...
Leonardo Certuchewww.itconsultores.com.co

...
...
...
...
On 25 August 2010 07:16, Ruslan wrote:

...
...
...
...
...
hi every one. I'm trying to create Stat-report with list of tickets closed with SLA violation. But when ticket is closed, it seems that sustem reset "escalation time" - ... I think it's wrong action. Any way - i need to analyze SLA violation in any time period. Anyone have any suggestion?

...
---------------------------------------------------------------------

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

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

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

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

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

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

Leonardo Certuche

5:04 p.m.

We developed it as part of our IT&Libre http://www.itilibre.com solution, available in spanish and english as added value to the customers that hire our ITSM consultant services. If you're interested in taking a look at it, please let us know. Leonardo Certuche www.itconsultores.com.co Medellín, Colombia On 30 August 2010 09:59, Ruslan wrote:

...

You develop it for own purposes or we can check it and m.b. buy rgis sytem?

On 30 авг, 18:24, Leonardo Certuche wrote:

...
Hello there,

I'd love to know how but true to be told, we're deploying our own reporter web-based and with OTRS integrated authentication in which a full set of reports based on ITIL PKIs are shown

Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

On 30 August 2010 03:16, Ruslan wrote:

...
One more question - How did u getting stats using this new table "sla_times" ?

...
On 27 авг, 18:45, Leonardo Certuche wrote:

...
Hello Ruslan,

...
...
I'm glad you like it :D

...
...
As you can see it's a workaround tropical-style. If you get the chance to improve it or to come with a better approach, could you please share it with us?

...
...
Thanks!

...
...
Leonardo Certuchewww.itconsultores.com.co Medellín, Colombia

...
...
On 27 August 2010 07:04, Ruslan wrote:

...
...
...
Praise you Leonardo!!! ))))))) And nice trick BTW ) I was trying to - rewrite module )

...
...
...
On 25 авг, 23:30, Leonardo Certuche wrote:

...
Hello,

...
...
...
...
I'm also sad of OTRS behavior regarding SLAs. In order to save somewhere else SLA times before they get reset, we're using the following SQL table and trigger. I hope it helps and if anyone has as better approach (or if newer version won't reset those times) please let us know here.

...
...
...
...
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`) )

...
...
...
...
USE OTRS; 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 ;

...
...
...
...
Leonardo Certuchewww.itconsultores.com.co

...
...
...
...
On 25 August 2010 07:16, Ruslan wrote:

...
...
...
...
> hi every one. I'm trying to create Stat-report with list of tickets > closed with SLA violation. But when ticket is closed, it seems that > sustem reset "escalation time" - ... > I think it's wrong action. > Any way - i need to analyze SLA violation in any time period. > Anyone have any suggestion?

...
---------------------------------------------------------------------

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

...
...
...
...

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

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

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

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

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

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

5436

Age (days ago)

5441

Last active (days ago)

List overview

Download

7 comments

2 participants

participants (2)

Leonardo Certuche
Ruslan