GLPI/Script SQL

Voici quelques exemples de script SQL pour traiter certains aspects.

Exemple de script SQL

Affichage de certains tickets d'un technicien

Affichage des tickets d'un technicien, dont le statut n'est ni résolu ni clos :

• avec affichage du numéro du ticket et du numéro du technicien ;
• dont le statut n'est ni résolu ni clos ;
• dont la catégorie a pour numéro 33 ;
• ...

SELECT DISTINCT glpi_tickets_users.tickets_id, glpi_tickets_users.users_id FROM glpi_tickets_users
WHERE glpi_tickets_users.tickets_id in
(SELECT DISTINCT glpi_tickets_users.tickets_id 
FROM glpi_tickets, glpi_tickettasks, glpi_tickets_users, glpi_itilcategories
WHERE ( 
		(
				glpi_tickets.status = 1 
			OR glpi_tickets.status = 2
			OR glpi_tickets.status = 3
			OR glpi_tickets.status = 4
		)
		AND glpi_tickets.id = glpi_tickets_users.tickets_id
		AND glpi_tickets.itilcategories_id = glpi_itilcategories.id
		AND glpi_tickets.itilcategories_id = 33
		AND glpi_tickets_users.users_id = 762
      )
)
AND glpi_tickets_users.users_id = 762
;

Modification d'affectation de tickets d'un technicien

Remplacement du technicien (762) des tickets d'un autre technicien (560) :

• dont le statut n'est ni résolu ni clos ;
• dont la catégorie a pour numéro 33 ;
• ...

UPDATE glpi_tickets_users SET users_id = 762
WHERE glpi_tickets_users.tickets_id in
(SELECT DISTINCT glpi_tickets_users.tickets_id 
FROM glpi_tickets, glpi_tickettasks, glpi_itilcategories
WHERE ( 
		(
				glpi_tickets.status = 1 
			OR glpi_tickets.status = 2
			OR glpi_tickets.status = 3
			OR glpi_tickets.status = 4
		)
		AND glpi_tickets.id = glpi_tickets_users.tickets_id
		AND glpi_tickets.itilcategories_id = glpi_itilcategories.id
		AND glpi_tickets.itilcategories_id = 33
		AND glpi_tickets_users.users_id = 560
		)
)
AND glpi_tickets_users.users_id = 560
;

Requête

Référence :

• https://forum.glpi-project.org/viewtopic.php?id=280215

UPDATE glpi_tickets SET status= "1",takeintoaccount_delay_stat="0" 
WHERE id IN (
	SELECT glpi_tickets.id 
	FROM glpi_tickets LEFT JOIN glpi_tickets_users ON glpi_tickets.id = glpi_tickets_users.tickets_id AND glpi_tickets_users.type = "2" 
	WHERE glpi_tickets.id IN (
		SELECT glpi_tickets.id 
		FROM glpi_tickets LEFT JOIN glpi_groups_tickets ON glpi_tickets.id = glpi_groups_tickets.tickets_id 
		WHERE glpi_tickets.status = "2" AND glpi_groups_tickets.groups_id = "1" AND glpi_tickets.is_deleted = "0"
	)
	AND glpi_tickets_users.type IS NULL
)

•