Script da consulta básica da view, não considerando condições adicionais.
SELECT
T.id AS 'ID',
CASE
WHEN C.completename IS NOT NULL
THEN C.completename
ELSE 'N/A'
END AS 'Categoria',
CASE
WHEN L.name IS NOT NULL
THEN L.name
ELSE 'N/A'
END AS 'Unidade solicitante',
CASE
WHEN G.name IS NOT NULL
THEN G.name
ELSE 'N/A'
END AS 'Grupo',
CAST((CASE
WHEN T.type = 1
THEN 'Incidente'
ELSE 'Requisição'
END) AS CHAR (10)) AS 'Tipo',
CAST((CASE
WHEN T.status = 1
THEN 'Novo'
WHEN T.status = 4
THEN 'Pendente'
WHEN T.status = 5
THEN 'Solucionado'
WHEN T.status = 6
THEN 'Fechado'
ELSE 'Processando'
END) AS CHAR (15)) AS 'Status',
T.date AS 'Abertura',
T.solvedate AS 'Solucionado em',
T.closedate AS 'Fechamento',
CASE
WHEN T.time_to_resolve IS NOT NULL
THEN T.time_to_resolve
ELSE 'Sem SLA atribuído'
END AS 'SLA',
CAST((CASE
WHEN
T.time_to_resolve IS NOT NULL
AND T.time_to_resolve < T.solvedate
AND T.status > 4
OR T.time_to_resolve < CURRENT_TIMESTAMP()
AND T.status < 4
THEN 'Atrasado'
ELSE 'No prazo'
END) AS CHAR (10)) AS 'Status SLA'
FROM
glpi.glpi_tickets T LEFT JOIN
glpi.glpi_itilcategories C ON T.itilcategories_id = C.id LEFT JOIN
glpi.glpi_locations L ON T.locations_id = L.id LEFT JOIN
glpi.glpi_groups_tickets GT ON T.id = GT.tickets_id LEFT JOIN
glpi.glpi_groups G ON GT.groups_id = G.id
WHERE
T.is_deleted = 0
AND G.id NOT IN (22,132,141,142,155,156,157)
AND C.completename NOT LIKE 'Plano de Home Office%'
AND C.completename NOT LIKE 'Rotinas Internas%'
AND C.completename NOT LIKE 'Projetos%';
SELECT
T.id AS 'ID',
CASE
WHEN C.completename IS NOT NULL
THEN C.completename
ELSE 'N/A'
END AS 'Categoria',
CASE
WHEN L.name IS NOT NULL
THEN L.name
ELSE 'N/A'
END AS 'Unidade solicitante',
CASE
WHEN G.name IS NOT NULL
THEN G.name
ELSE 'N/A'
END AS 'Grupo',
CASE
WHEN U.firstname <> '' OR U.realname <> ''
THEN UPPER(CONCAT(CONCAT(U.firstname, ' '), U.realname))
ELSE U.name
END AS 'Técnico',
CAST((CASE
WHEN T.type = 1
THEN 'Incidente'
ELSE 'Requisição'
END) AS CHAR (10)) AS 'Tipo',
CAST((CASE
WHEN T.status = 1
THEN 'Novo'
WHEN T.status = 4
THEN 'Pendente'
WHEN T.status = 5
THEN 'Solucionado'
WHEN T.status = 6
THEN 'Fechado'
ELSE 'Processando'
END) AS CHAR (15)) AS 'Status',
T.date AS 'Abertura',
T.solvedate AS 'Solucionado em',
T.closedate AS 'Fechamento',
CASE
WHEN T.time_to_resolve IS NOT NULL
THEN T.time_to_resolve
ELSE 'Sem SLA atribuído'
END AS 'SLA',
CAST((CASE
WHEN
T.time_to_resolve IS NOT NULL
AND T.time_to_resolve < T.solvedate
AND T.status > 4
OR T.time_to_resolve < CURRENT_TIMESTAMP()
AND T.status < 4
THEN 'Atrasado'
ELSE 'No prazo'
END) AS CHAR (10)) AS 'Status SLA'
FROM
glpi.glpi_tickets T LEFT JOIN
glpi.glpi_itilcategories C ON T.itilcategories_id = C.id LEFT JOIN
glpi.glpi_locations L ON T.locations_id = L.id LEFT JOIN
glpi.glpi_groups_tickets GT ON T.id = GT.tickets_id LEFT JOIN
glpi.glpi_groups G ON GT.groups_id = G.id LEFT JOIN
glpi.glpi_tickets_users TU ON T.id = TU.tickets_id LEFT JOIN
glpi.glpi_users U ON U.id = TU.users_id
WHERE
T.is_deleted = 0
AND TU.type = 2
AND G.id NOT IN (22,132,141,142,155,156,157)
AND C.completename NOT LIKE 'Plano de Home Office%'
AND C.completename NOT LIKE 'Rotinas Internas%'
AND C.completename NOT LIKE 'Projetos%';
SELECT
T.id AS 'ID',
CASE
WHEN C.completename IS NOT NULL
THEN C.completename
ELSE 'N/A'
END AS 'Categoria',
CASE
WHEN L.name IS NOT NULL
THEN SUBSTRING_INDEX(L.completename,' > ',1)
ELSE 'N/A'
END AS 'Unidade solicitante',
CASE
WHEN G.name IS NOT NULL
THEN G.name
ELSE 'N/A'
END AS 'Grupo',
CAST((CASE
WHEN T.type = 1
THEN 'Incidente'
ELSE 'Requisição'
END) AS CHAR (10)) AS 'Tipo',
CAST((CASE
WHEN T.status = 1
THEN 'Novo'
WHEN T.status = 4
THEN 'Pendente'
WHEN T.status = 5
THEN 'Solucionado'
WHEN T.status = 6
THEN 'Fechado'
ELSE 'Processando'
END) AS CHAR (15)) AS 'Status',
T.date AS 'Abertura',
T.solvedate AS 'Solucionado em',
T.closedate AS 'Fechamento',
CASE
WHEN T.time_to_resolve IS NOT NULL
THEN T.time_to_resolve
ELSE 'Sem SLA atribuído'
END AS 'SLA',
CAST((CASE
WHEN
T.time_to_resolve IS NOT NULL
AND T.time_to_resolve < T.solvedate
AND T.status > 4
OR T.time_to_resolve < CURRENT_TIMESTAMP()
AND T.status < 4
THEN 'Atrasado'
ELSE 'No prazo'
END) AS CHAR (10)) AS 'Status SLA',
S.satisfaction as 'Nota de avaliação',
S.date_answered as 'Avaliado em',
S.comment as 'Comentário'
FROM
glpi.glpi_tickets T LEFT JOIN
glpi.glpi_itilcategories C ON T.itilcategories_id = C.id LEFT JOIN
glpi.glpi_locations L ON T.locations_id = L.id LEFT JOIN
glpi.glpi_groups_tickets GT ON T.id = GT.tickets_id LEFT JOIN
glpi.glpi_groups G ON GT.groups_id = G.id LEFT JOIN
glpi.glpi_ticketsatisfactions S ON T.id = S.tickets_id
WHERE
T.is_deleted = 0;
Inclui identificação anonimizada dos solicitantes.
SELECT
T.id AS 'ID',
CASE
WHEN C.completename IS NOT NULL
THEN C.completename
ELSE 'N/A'
END AS 'Categoria',
INSERT(INSERT(U.name,1,3,'***'),10,2,'**') as 'Solicitante',
CASE
WHEN L.name IS NOT NULL
THEN SUBSTRING_INDEX(L.completename,' > ',1)
ELSE 'N/A'
END AS 'Unidade solicitante',
CASE
WHEN G.name IS NOT NULL
THEN G.name
ELSE 'N/A'
END AS 'Grupo',
CAST((CASE
WHEN T.type = 1
THEN 'Incidente'
ELSE 'Requisição'
END) AS CHAR (10)) AS 'Tipo',
CAST((CASE
WHEN T.status = 1
THEN 'Novo'
WHEN T.status = 4
THEN 'Pendente'
WHEN T.status = 5
THEN 'Solucionado'
WHEN T.status = 6
THEN 'Fechado'
ELSE 'Processando'
END) AS CHAR (15)) AS 'Status',
T.date AS 'Abertura',
T.solvedate AS 'Solucionado em',
T.closedate AS 'Fechamento',
CASE
WHEN T.time_to_resolve IS NOT NULL
THEN T.time_to_resolve
ELSE 'Sem SLA atribuído'
END AS 'SLA',
CAST((CASE
WHEN
T.time_to_resolve IS NOT NULL
AND T.time_to_resolve < T.solvedate
AND T.status > 4
OR T.time_to_resolve < CURRENT_TIMESTAMP()
AND T.status < 4
THEN 'Atrasado'
ELSE 'No prazo'
END) AS CHAR (10)) AS 'Status SLA',
S.satisfaction as 'Nota de avaliação',
S.date_answered as 'Avaliado em',
S.comment as 'Comentário',
unix_timestamp(`T`.`solvedate`) - unix_timestamp(`T`.`date`) AS 'Tempo para solução',
-- Necessário apenas para apresentar registros individuais formatados
CONCAT(
FLOOR((unix_timestamp(`T`.`solvedate`) - unix_timestamp(`T`.`date`))/3600/24)," dias, ",
FLOOR(MOD((unix_timestamp(`T`.`solvedate`) - unix_timestamp(`T`.`date`))/3600,24)),"h:",
FLOOR(MOD((unix_timestamp(`T`.`solvedate`) - unix_timestamp(`T`.`date`)),3600)/60),"m:",
MOD((unix_timestamp(`T`.`solvedate`) - unix_timestamp(`T`.`date`)),60),"s"
) AS `Tempo para solução (formatado)`,
-- Referenciando a tabela auxiliar com os custos por categoria de serviço
V.valor as 'Custo'
FROM
glpi.glpi_tickets T LEFT JOIN
glpi.glpi_itilcategories C ON T.itilcategories_id = C.id LEFT JOIN
glpi.glpi_locations L ON T.locations_id = L.id LEFT JOIN
glpi.glpi_groups_tickets GT ON T.id = GT.tickets_id LEFT JOIN
glpi.glpi_groups G ON GT.groups_id = G.id LEFT JOIN
test.valores_servicos V ON C.id = V.id_categoria LEFT JOIN
glpi.glpi_ticketsatisfactions S ON T.id = S.tickets_id LEFT JOIN
glpi.glpi_tickets_users TU ON T.id = TU.tickets_id LEFT JOIN
glpi.glpi_users U ON U.id = TU.users_id
WHERE
T.is_deleted = 0
AND C.id IN (549) -- Categorias adicionais deverão ser adicionadas aqui
AND TU.type = 1;