[otrs] STATS - Ticket closed with SLA Violation

Discussion:

Ruslan

2010-08-25 12:16:36 UTC

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?

Leonardo Certuche

2010-08-25 19:30:50 UTC

Permalink

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

Post by Ruslan
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

2010-08-27 12:04:59 UTC

Permalink

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

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

Post by Leonardo Certuche
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

Post by Ruslan
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

2010-08-27 14:45:11 UTC

Permalink

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

Post by Ruslan
Praise you Leonardo!!! )))))))
And nice trick BTW )
I was trying to - rewrite module )
On 25 Ð°Ð²Ð³, 23:30, Leonardo Certuche

Post by Leonardo Certuche
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

Post by Ruslan
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

Ruslan

2010-08-30 09:16:35 UTC

Permalink

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

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

Post by Leonardo Certuche
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

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

Post by Leonardo Certuche
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

Post by Ruslan
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

Leonardo Certuche

2010-08-30 14:24:10 UTC

Permalink

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

Post by Ruslan
One more question - How did u getting stats using this new table
"sla_times" ?
On 27 Ð°Ð²Ð³, 18:45, Leonardo Certuche

with

Post by Leonardo Certuche
us?
Thanks!
Leonardo Certuchewww.itconsultores.com.co
MedellÃn, Colombia

Post by Ruslan
Praise you Leonardo!!! )))))))
And nice trick BTW )
I was trying to - rewrite module )
On 25 Ð°Ð²Ð³, 23:30, Leonardo Certuche

Post by Leonardo Certuche
Hello,
I'm also sad of OTRS behavior regarding SLAs. In order to save

somewhere