You are not logged in.
Good morning community, I would like to know if someone can help me with some views that I must do in mysql (navicat). (select .....)
Thank you for your help, I am in the process of studying mysql views.
* List the number of open and closed requests by a technician.
Example:
open closed
Technician 1 1 10
Technician 2 2 11
Technical 3 1 20
* List Number of requests per applicant
Example:
Department A = 10
Department B = 20
Department C = 0
* List quantity requests by category
Example:
Change of pc = 10
Formatting = 5
Change of account = 6
* List by request of origin
Example
GLPI: 20
Phone: 5
Mail: 2
Offline
help me please!
Offline
for ticket source :
SELECT COUNT(glpi_tickets.id) AS nbOuverture ,glpi_requesttypes.name AS origine
FROM `glpi_tickets` JOIN glpi_requesttypes ON glpi_requesttypes.id = `glpi_tickets`.`requesttypes_id`
WHERE glpi_tickets.`date`> '2018-03-01' GROUP BY origine
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
for category stats :
select glpi_itilcategories.name as category, count(glpi_tickets.id) as nombre
from glpi_tickets
join glpi_itilcategories on glpi_itilcategories.id=glpi_tickets.itilcategories_id
where glpi_tickets.date >='2018-01-01'
group by category
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
i don't understand "per applicant"...
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
thx you very much friend!
my english is bad. excuse me please.
* List Number requester???
Example:
Department A = 10
Department B = 20
Department C = 0
Offline
are departments "requester groups " ?
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
********Hi LaDenrée, the departments are not groups, they are assigned as users "SELF-SERVICE"
* List the number of open and closed requests by a technician.
Example:
open closed
Technician 1 1 10
Technician 2 2 11
Technical 3 1 20
I'm doing this query but I do not have good results
SELECT `glpi_tickets`.`id`,`glpi_users`.`name`,glpi_tickets.status
FROM `glpi_tickets`
LEFT JOIN `glpi_tickets_users` ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` AND `glpi_tickets_users`.`type` = 2 )
LEFT JOIN `glpi_users` ON (`glpi_tickets_users`.`users_id` = `glpi_users`.`id` )
where `glpi_tickets`.`is_deleted` = '0'
Offline
try this :
SELECT `glpi_users`.`name`,glpi_tickets.status,count(glpi_tickets.id) as Nombre
FROM `glpi_tickets`
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id`
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 2 AND glpi_tickets.date>'2018-01-01'
GROUP BY `glpi_users`.`name`,glpi_tickets.status
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
i'm not sure I understand " List Number of requests per applicant" but try this
SELECT `glpi_users`.`name` AS Dept ,count(glpi_tickets.id) as Nombre
FROM `glpi_tickets`
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id`
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 1 AND glpi_tickets.date>'2018-01-01'
GROUP BY `glpi_users`.`name`
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
Thank you very much, you are an expert. It is what I needed.
How could you give the status a name (close, open, etc.) , for example in the second query.
name status status type tickets
mcano 2 ? 2
mcano 3 ? 1
mcano 6 close 131
Offline
SELECT `glpi_users`.`name`,
replace(replace(replace(replace(replace(replace(glpi_tickets.status,"1","new"),"2","Assigned"),"3","planed"),"4","waiting"),"5","solved"),"6","closed") AS statut
,count(glpi_tickets.id) as Nombre
FROM `glpi_tickets`
LEFT JOIN `glpi_tickets_users` ON `glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`
LEFT JOIN `glpi_users` ON `glpi_tickets_users`.`users_id` = `glpi_users`.`id`
WHERE `glpi_tickets`.`is_deleted` = '0' AND `glpi_tickets_users`.`type` = 2 AND glpi_tickets.date>'2018-01-01'
GROUP BY `glpi_users`.`name`,statut
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
thank you very much, you are a genius
Offline
For information, @LaDenrée, you can use CASE statement to simplify the query, ex:
CASE
WHEN glpi_tickets.status = 1 THEN "New"
WHEN glpi_tickets.status = 2 THEN "Assigned"
WHEN glpi_tickets.status = 3 THEN "Planed"
WHEN glpi_tickets.status = 4 THEN "Waiting"
WHEN glpi_tickets.status = 5 THEN "Solved"
WHEN glpi_tickets.status = 6 THEN "Closed"
END as status
Offline
@orthagh : +1
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
Hello everyone, thanks for all the support provided. Especially to @ LaDenrée.
Is there the possibility of making a large table in mysql view?
For example
id_ticket title Category Technician Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request
THX.
Offline
It will be very difficult?
Offline
this query should work unless you only have 1 requester and 1 technician and 1 group by ticket
SELECT ti.id AS id_ticket,ti.name AS title,cat.name AS category,tech.name AS Technician,ti.priority AS priority,req.name AS requester,
CASE
WHEN ti.status = 1 THEN "New"
WHEN ti.status = 2 THEN "Assigned"
WHEN ti.status = 3 THEN "Planed"
WHEN ti.status = 4 THEN "Waiting"
WHEN ti.status = 5 THEN "Solved"
WHEN ti.status = 6 THEN "Closed"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
WHEN ti.type = 1 then "Incident"
when ti.type = 2 then "request"
END AS ticket_type
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2018-01-01'
GROUP BY id_ticket,title,category,Technician,priority,requester,status,technician_group,open_date,ticket_source,ticket_type
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
thank you very much friend!!
Offline
Hello everyone, I have a doubt regarding this row:
id_ticket title Category Technician Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates ** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request
100 Pc repair repair Juan Perez ** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request
Could it be done in this way, so as not to repeat the id_ticket?
id_ticket title Category Technician1 Technician2 Priority Requester Status Task duration technitian group opening date request source type
100 Pc repair repair Bill gates** Juan Perez** Low Departament A Closed 1 hours 0 minutes informatic 05-09-2018 11:46 GLPI Request
Thank you very much for your help @La Denrée.
Last edited by sebastian.123456 (2018-09-07 18:54:13)
Offline
:c
Offline
try with
group_concat(tech.name SEPARATOR ' ') AS Technician
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline
SELECT ti.id AS id_ticket, ti.name AS title,cat.name AS category,group_concat(tech.name SEPARATOR ' ') AS Technician,ti.priority AS priority,req.name AS requester,
CASE
WHEN ti.status = 1 THEN "Nuevo"
WHEN ti.status = 2 THEN "Asignado"
WHEN ti.status = 3 THEN "Planificado"
WHEN ti.status = 4 THEN "En espera"
WHEN ti.status = 5 THEN "Resuelto"
WHEN ti.status = 6 THEN "Cerrado"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
WHEN ti.type = 1 then "Incidente"
when ti.type = 2 then "Solicitud"
END AS ticket_type
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN glpi_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2016-01-01'
GROUP BY id_ticket,title,category,Technician,priority,requester,status,technician_group,open_date,ticket_source,ticket_type
Last edited by sebastian.123456 (2018-09-11 17:49:18)
Offline
SELECT ti.id AS id_ticket, ti.name AS title,cat.name AS category,group_concat(tech.name SEPARATOR ' ') AS Technician,req.name AS requester,
CASE
WHEN ti.status = 1 THEN "Nuevo"
WHEN ti.status = 2 THEN "Asignado"
WHEN ti.status = 3 THEN "Planificado"
WHEN ti.status = 4 THEN "En espera"
WHEN ti.status = 5 THEN "Resuelto"
WHEN ti.status = 6 THEN "Cerrado"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
WHEN ti.type = 1 then "Incidente"
when ti.type = 2 then "Solicitud"
END AS ticket_type,
CASE
WHEN ti.priority = 1 THEN "Muy Baja"
WHEN ti.priority = 2 THEN "Baja"
WHEN ti.priority = 3 THEN "Media"
WHEN ti.priority = 4 THEN "Alta"
WHEN ti.priority = 5 THEN "Muy Alta"
WHEN ti.priority = 6 THEN "Cerrado"
END AS prioridad
Could it be divided into two or more tables depending on the number of technicians?
Last edited by sebastian.123456 (2018-09-11 17:37:14)
Offline
right, you should remove Technician in group by clause.
SELECT ti.id AS id_ticket,ti.name AS title,cat.name AS category,GROUP_CONCAT(tech.name) AS Technician,ti.priority AS priority,req.name AS requester,
CASE
WHEN ti.`status` = 1 THEN 'New'
WHEN ti.status = 2 THEN 'Assigned'
WHEN ti.status = 3 THEN 'Planed'
WHEN ti.status = 4 THEN 'Waiting'
WHEN ti.status = 5 THEN 'Solved'
WHEN ti.status = 6 THEN 'Closed'
END AS status,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,
CASE
WHEN ti.type = 1 then 'Incident'
WHEN ti.type = 2 then 'request'
END AS ticket_type
FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN glpi_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2018-01-01'
GROUP BY id_ticket,title,category,priority,requester,status,technician_group,open_date,ticket_source,ticket_type
and you also should use single quotes in CASE statement
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
Offline