Forum GLPI-Project

sebastianxxx

Member

Registered: 2018-06-01

Posts: 9

Query in mysql GLPI

Hello, I would like to know if you can help me with the following query:

id_ticket     name ticket       assigned technician            User           location       Priority
2258         fail in server    Sebastián                          pedro       first floor       low

The idea is that it shows me all the open tickets, even if no technicians have been assigned.

sebastianxxx

Member

Registered: 2018-06-01

Posts: 9

Re: Query in mysql GLPI

Hello friends. Is it very difficult to solve?

cconard96

Moderator

Registered: 2018-07-31

Posts: 2,813

Website

Re: Query in mysql GLPI

Could you explain what you are trying to do a bit more?

---

GLPI Collaborator and Plugin Developer.
My non-English comments are automated translations. Sorry for any confusion that causes.
Mes commentaires non anglais sont des traductions automatiques. Désolé pour toute confusion qui cause.
Mis comentarios que no están en inglés son traducciones automáticas. Perdón por cualquier confusión que cause.

sebastianxxx

Member

Registered: 2018-06-01

Posts: 9

Re: Query in mysql GLPI

Yes, of course, good morning, I want to perform a mysql query to deliver the results indicated above. such as, for example, the ticket id, the name of the ticket, its location, requesting user, etc.

LaDenrée

HELPER

Registered: 2012-11-19

Posts: 6,287

Re: Query in mysql GLPI

assuming you only have 1 technician assigned per ticket and one requester per ticket this should work

SELECT ti.id as 'id_ticket', ti.name as 'name', u2.name as 'assigned technician',u1.name as 'user (requester)',loc.name as 'location',ti.priority as 'priority' 
FROM glpi_tickets AS ti 
    LEFT OUTER JOIN glpi_tickets_users AS tu1 ON tu1.tickets_id=ti.id AND tu1.type=1 
        LEFT OUTER JOIN glpi_users AS u1 on u1.id=tu1.users_id 
    LEFT OUTER JOIN glpi_tickets_users AS tu2 ON tu2.tickets_id=ti.id AND tu2.type=2 
        LEFT OUTER JOIN glpi_users AS u2 on u2.id=tu2.users_id 
   LEFT OUTER JOIN glpi_locations AS loc on loc.id=ti.locations_id
 WHERE 1=1

---

Trouver la panne avant de réparer...
GLPI10.0.16 (ubuntu 22.04 PHP8.1  Mariadb10.6 ) plugins : comportements 2.7.3 reports 1.16.0 formcreator 2.13.9, datainjection 2.13.5 fields 1.21.9

sebastianxxx

Member

Registered: 2018-06-01

Posts: 9

Re: Query in mysql GLPI

Thank you very much, the query is correct, it just needs to show the tickets with an open status

cconard96

Moderator

Registered: 2018-07-31

Posts: 2,813

Website

Re: Query in mysql GLPI

Change "WHERE 1=1" to "WHERE ti.status != 6" for tickets not closed. For tickets not solved or closed, "WHERE ti.status NOT IN (5,6)".

---

GLPI Collaborator and Plugin Developer.
My non-English comments are automated translations. Sorry for any confusion that causes.
Mes commentaires non anglais sont des traductions automatiques. Désolé pour toute confusion qui cause.
Mis comentarios que no están en inglés son traducciones automáticas. Perdón por cualquier confusión que cause.

sebastianxxx

Member

Registered: 2018-06-01

Posts: 9

Re: Query in mysql GLPI

Thanks you.