I have not solved a problem for some time, I can not show through mysql the tickets that have been scaled.
I need it to show it in Grafana.
The query that I created is the following:
Code: Select all
SELECT
T.title AS Ticket,
T.customer_id AS Cliente,
E.name AS Servicio,
DATE_FORMAT((T.create_time + INTERVAL S.first_response_time MINUTE),'%Y-%m-%d %H:%I:%S') as "Escalado",
S.solution_time AS SLA_SOLUCION,
(case when (S.first_response_time = 0) then 0 else dif_sla_respuesta(T.id,Z.escalation_response_time,T.ticket_state_id) end/60) AS SLA_Respuesta,
(case when (S.solution_time = 0) then 0 else dif_sla_solucion(T.id,Z.escalation_solution_time,T.ticket_state_id) end/60) AS Tiempo_Solucion,
((S.solution_time) - (case when (S.solution_time = 0) then 0 else dif_sla_solucion(T.id,Z.escalation_solution_time,T.ticket_state_id) end/60)) AS Tiempo_Trabajado
FROM ticket T
INNER JOIN service E on (E.id = T.service_id)
INNER JOIN sla S on (S.id = T.sla_id)
INNER JOIN queue B on (B.id = T.queue_id)
INNER JOIN ticket_state X on (X.id = T.ticket_state_id)
INNER JOIN ticket_state_type Y on (Y.id = X.type_id)
INNER JOIN zz_reg_escalacion_ticket Z on (Z.ticket_id = T.id)
where Y.id = 3 and customer_id = 'Prueba'
and
S.first_response_time != 0
It does not show the results well, in "Time Solution" there are tickets in negative but the ticket is not scaled.
I would appreciate your help.
Thanks greetings.