Requête SQL sur BDD GLPI

**ScaIN** · 28/08/2018, 10h23

Bonjour à tous,

Travaillant actuellement pour un support helpdesk, nous utilisons la solution GLPI pour la création des tickets.
Nous cherchons actuellement à extraire nos KPI, nous en avons déjà une bonne partie.
Pour l'extraction nous utilisons un liens ODBC vers la base GLPI et lançons donc nos requêtes SQL.

Par contre nous n'arrivons pas à construire une des requête (par manque d'expérience ^^).

Le contexte :
GLPI est installé sur un CENTOS 7
mysql version 14.14 Distribution 5.1.71 (mariadb)

Nous cherchons à extraire l'autonomie de notre équipe desk, pour ce faire notre requête attaque la base GLPI sur la table glpi_logs (historique).
Dans cette table nous retrouvons donc les colonnes suivantes :
id, itemtype, items_id, itemtype_link, linked_action, user_name, date_mod, id_search_option,old_value, new_value

Donc je veux savoir quels tickets ont été clôturés par mon équipe sans escalade vers une autre équipe.
À chaque fois que le ticket est escaladé il ajoute une ligne supplémentaire sur le même ticket en précisant pour old_value (l'ancienne équipe affectée) et new_value (la nouvelle équipe affectée).

Ce que je n'arrive pas à faire dans ma requête c'est lui dire de ne sortir que les tickets qui ont appartenu à mon équipe, si il voit d'autre ligne avec d'autres équipe il ne doit pas tenir compte du ticket.

Je vous mets en pièce jointe l'exemple d'un ticket qui a été escaladé dans plusieurs équipes pour que vous compreniez.
En espérant avoir été assez clair sinon n'hésitez pas

Est ce que quelqu'un saurait me conseiller sur la construction de la requête svp ?

**CinePhil** · 28/08/2018, 11h06

Donc je veux savoir quels tickets ont été clôturés par mon équipe sans escalade vers une autre équipe.
À chaque fois que le ticket est escaladé il ajoute une ligne supplémentaire sur le même ticket en précisant pour old_value (l'ancienne équipe affectée) et new_value (la nouvelle équipe affectée).

Le ticket est identifié par la colonne items_id ?

Donc vous cherchez les tickets où la new_value est "Service Desk (5)" et pour lesquels il n'existe pas de ligne avec old_value = "Service Desk (5)". C'est ça ?

Est ce que quelqu'un saurait me conseiller sur la construction de la requête svp ?

Commencez par faire une requête qui cherche les tickets de votre service puis ajoutez une condition de restriction (WHERE) qui va vérifier la non-existence (NOT EXISTS) de lignes avec votre service dans la colonne old_value.

Je vous laisse chercher ; revenez nous voir si vous n'y arrivez pas.

**ScaIN** · 28/08/2018, 11h49

Le ticket est identifié par la colonne items_id ?

Donc vous cherchez les tickets où la new_value est "Service Desk (5)" et pour lesquels il n'existe pas de ligne avec old_value = "Service Desk (5)". C'est ça ?

Oui c'est ça, items_id = Numéro du ticket
Pour la recherche oui c'est l'idée puisque quand un ticket n'est pas escaladé il n'y a qu'une ligne avec la colonne new_value = "Service Desk (5)"

Commencez par faire une requête qui cherche les tickets de votre service puis ajoutez une condition de restriction (WHERE) qui va vérifier la non-existence (NOT EXISTS) de lignes avec votre service dans la colonne old_value.

J'ai testé une requête mais sans grand succès, ce que je n'ai pas précisé c'est que j'utilise des jointures de tables également, voici ma requête d'origine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
T1.id as Tickets,
T1.date as "Date d'ouverture",
T1.type,
T2.completename as Categories,
T3.groups_id as Groupe,
T4.completename as Lieu,
T1.closedate as "Date de cloture",
T1.solvedate as "Date de resolution",
T1.urgency,
T1.impact,
T1.priority
FROM glpi_tickets AS T1
LEFT JOIN glpi_itilcategories T2
ON T1.itilcategories_id = T2.id
LEFT JOIN glpi_groups_tickets T3
ON T1.id = T3.tickets_id
LEFT JOIN glpi_locations T4
ON T1.locations_id = T4.id
LEFT JOIN glpi_logs T5
ON T5.items_id = T1.id
WHERE MONTH(T1.date) = MONTH(CURRENT_DATE)
AND YEAR(T1.date) = YEAR(CURRENT_DATE)
AND T1.type = 1
AND NOT T2.completename in ("Service Desk > Téléphonie > Test d'appel","Service Desk > Téléphonie > Faux appels")
GROUP BY T1.id

J'ai regardé la structure de la fonction NOT EXISTS mais apparemment il faut intégrer une requête à la suite, ça donnerait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
T1.id as Tickets,
T1.date as "Date d'ouverture",
T1.type,
T2.completename as Categories,
T3.groups_id as Groupe,
T4.completename as Lieu,
T1.closedate as "Date de cloture",
T1.solvedate as "Date de resolution",
T1.urgency,
T1.impact,
T1.priority
FROM glpi_tickets AS T1
LEFT JOIN glpi_itilcategories T2
ON T1.itilcategories_id = T2.id
LEFT JOIN glpi_groups_tickets T3
ON T1.id = T3.tickets_id
LEFT JOIN glpi_locations T4
ON T1.locations_id = T4.id
LEFT JOIN glpi_logs T5
ON T5.items_id = T1.id
WHERE MONTH(T1.date) = MONTH(CURRENT_DATE)
AND YEAR(T1.date) = YEAR(CURRENT_DATE)
AND T1.type = 1
AND NOT T2.completename in ("Service Desk > Téléphonie > Test d'appel","Service Desk > Téléphonie > Faux appels")
AND NOT EXISTS (SELECT old_value FROM glpi_logs WHERE old_value = "Service Desk (5)")
GROUP BY T1.id

Mais la requête ne me retourne aucun résultat

**CinePhil** · 28/08/2018, 13h11

Dans votre sous-requête NOT EXISTS, il manque la condition "de jointure" qui permet d'examiner la restriction avec la ligne examinée lors du processus de déroulement de la requête.
En l'état, votre requête dit : "Sélectionner des colonnes de plusieurs tables quand il n'existe pas de ligne de la table glpi_logs où old_value = "Service Desk (5)".
Comme il existe au moins une ligne de cette table où old_value = "Service Desk (5), le NOT EXISTS renvoie faux et la requête ne sélectionne rien.

Ceci devrait mieux marcher :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
	T1.id as Tickets,
	T1.date as "Date d'ouverture",
	T1.type,
	T2.completename as Categories,
	T3.groups_id as Groupe,
	T4.completename as Lieu,
	T1.closedate as "Date de cloture",
	T1.solvedate as "Date de resolution",
	T1.urgency,
	T1.impact,
	T1.priority
FROM glpi_tickets AS T1
LEFT JOIN glpi_itilcategories T2 ON T1.itilcategories_id = T2.id
LEFT JOIN glpi_groups_tickets T3 ON T1.id = T3.tickets_id
LEFT JOIN glpi_locations T4 ON T1.locations_id = T4.id
LEFT JOIN glpi_logs T5 ON T5.items_id = T1.id
WHERE MONTH(T1.date) = MONTH(CURRENT_DATE)
	AND YEAR(T1.date) = YEAR(CURRENT_DATE)
	AND T1.type = 1
	AND T2.completename NOT IN ("Service Desk > Téléphonie > Test d'appel", "Service Desk > Téléphonie > Faux appels")
	AND NOT EXISTS 
	(
		SELECT * 
		FROM glpi_logs gl
		WHERE gl.old_value = "Service Desk (5)"
			AND gl.items_id = T1.id
	)
GROUP BY T1.id

=> La condition NOT EXISTS est examinée pour chaque ligne sélectionnée qui répond aux autres conditions. On cherche donc bien les tickets pour lesquels il n'y a pas de ligne où old_value = "Service Desk (5) dans la table glpi_logs.

**ScaIN** · 03/09/2018, 09h13

Bonjour, juste pour vous faire un retour. J'ai testé la requête qui a fait planter la PROD (oui je sais pas de test en PROD...

)

La table est trop lourde et la requête n'a pas pu abouti, comme je suis sur une version GLPI qui ne purge pas les logs ça pose problème. Donc je suis en train de faire bouger les choses on va migrer sur une version plus récente de GLPI qui elle permet de purger les logs et je ne devrais plus être embêté pour lancer ma requête.

Je vous fais un retour dés que possible

**CinePhil** · 03/09/2018, 09h27

Je ne sais pas comment GLPI indexe sa BDD mais ce problème de "plantage" (à cause d'un trop long temps de réponse ?) peut être dû à un manque d'index quelque part.

**escartefigue** · 03/09/2018, 11h27

Les fonctions de colonne (MONTH, YEAR) sur les critères de filtrage rendent le prédicat non SArgAble, l'ajout d'index n'y changerait rien

Par ailleurs, les colonnes de la clause SELECT sont incohérentes avec celles du GROUP BY
Seul MySQL autorise cette syntaxe aberrante, les colonnes du SELECT hors GROUP BY prendront une valeur aléatoire dans le résultat.
Tout autre SGBD provoquerait une erreur de syntaxe que la norme SQL n'autorise pas.

EDIT : et pourquoi faire un GROUP BY puisqu'il n'y a aucun agrégat ?

**CinePhil** · 03/09/2018, 11h54

et pourquoi faire un GROUP BY puisqu'il n'y a aucun agrégat ?

Oups ! Je n'avais pas vu cette énormité lors de ma réponse !

**ScaIN** · 07/01/2019, 15h17

Bonjour,

Une bonne et heureuse année à vous

Ca fait longtemps que je n'ai pas UP ce post. Nous sommes sur le point de faire la MAJ de GLPI. Je n'ai donc toujours pas pu tester la requête.

Je suis également en train de chercher comment récupérer le groupe vers lequel on escalade quand il y a une escalade. Lors d'escalades dans GLPI il faut d'abord ajouter un groupe (vers lequel on escalade) et ensuite on supprime l'autre groupe.
Donc dans la table "glpi_logs" ça se traduit en 2 lignes, une pour l'ajout, une pour la suppression (voir ci-dessous)

Nom : Capture.PNG
Affichages : 2870
Taille : 5,3 Ko

Nom : Capture.PNG
Affichages : 2870
Taille : 5,3 Ko

La colonne numéro 4 est l'ID de l'action, 15 pour ajout et 16 pour suppression.

Le but c'est de pouvoir afficher sur une même ligne le groupe qui a été ajouté et le groupe qui a été supprimé juste après. Je pense qu'il faut se baser sur la date mais je ne vois pas comment lui dire d'afficher l'action de suppression de groupe juste après celle de l'ajout.

J'espère avoir été assez claire =/

**CinePhil** · 08/01/2019, 09h22

Vite fait...
Il faut, pour chaque ligne de groupe ajouté, chercher le même groupe supprimé à la date la plus petite et supérieure.
Je vous laisse réfléchir à la requête.

**ScaIN** · 08/01/2019, 11h50

Oui c'est bien ça

Je pensais faire une requête imbriquée dans mon SELECT.

Je vais y réfléchir..

**ScaIN** · 08/01/2019, 13h38

J'ai pensé utiliser ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT
*,
(
     SELECT
     new_value
     FROM glpi_logs
     WHERE MONTH(date_mod) = MONTH(CURRENT_DATE)
     AND YEAR(date_mod) = YEAR(CURRENT_DATE)
     AND linked_action = 15
     AND itemtype_link = "Group"
     LIMIT 1
)
FROM glpi_logs
WHERE MONTH(date_mod) = MONTH(CURRENT_DATE)
AND YEAR(date_mod) = YEAR(CURRENT_DATE)
AND linked_action = 16
AND itemtype_link = "Group"
AND old_value in ("liste groupes")

Seulement le problème c'est que je veux lui dire de prendre la valeur suivante en utilisant ">" mais je ne sais pas récupérer ma date qui va résulter de ma requête...

**ScaIN** · 09/01/2019, 09h40

J'ai pensé également à utiliser un JOIN comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
SELECT
T1.items_id,
T1.user_name,
T1.old_value,
T2.date_mod,
T2.new_value
FROM glpi_logs AS T1
JOIN
(
SELECT
items_id,
new_value, 
date_mod
FROM glpi_logs
WHERE linked_action = 15
AND itemtype_link = "Group"
GROUP BY items_id
) AS T2
ON T1.items_id = T2.items_id
WHERE MONTH(T1.date_mod) = MONTH(CURRENT_DATE)
AND YEAR(T1.date_mod) = YEAR(CURRENT_DATE)
AND T1.linked_action = 16
AND T1.itemtype_link = "Group"
AND T1.old_value in ("Exploitation (2)", "Analyste exploitation (34)", "Intégration (100)")
AND T2.date_mod > T1.date_mod

Je ne sais pas si je suis sur la bonne piste...

Qu'en pensez vous ?

**Artemus24** · 10/01/2019, 13h28

Salut à tous.

Envoyé par ScaIN

mysql version 14.14 Distribution 5.1.71 (mariadb)

C'est quoi ça ?

Utilisez-vous un serveur mysql ou mariadb ?

La dernière version mariadb est la 10.3.12.
La dernière version mysql est la 8.0.13.
Il n'existe pas de version mysql ni de version mariadb 14.14.

La version 5.1.71 correspond à une très ancienne version de mysql et non à mariadb.
Le terme de distribution est relatif à des systèmes d'exploitations et non à des serveurs.

Soyez plus précis. C'est MySql ou Mariadb ? Et quelle version ?

Envoyé par ScaIN

Nous cherchons à extraire l'autonomie de notre équipe desk,

Je suppose que par équipe "desk", je dois comprendre "Service Desk(5)". Est-ce bien cela ?

Envoyé par ScaIN

je veux savoir quels tickets ont été clôturés par mon équipe sans escalade vers une autre équipe.

Par clotûré, que dois-je comprendre ? Est-ce la dernière ligne pour un ticket donné ?
Le fait d'être la dernière ligne ne signifie pas pour autant que le ticket a été clotûré.

Envoyé par ScaIN

À chaque fois que le ticket est escaladé il ajoute une ligne supplémentaire sur le même ticket en précisant pour old_value (l'ancienne équipe affectée) et new_value (la nouvelle équipe affectée).

Par très clair, non plus, sur l'affectation de l'ancienne équipe et de la nouvelle équipe à vos lignes.
Pourquoi avoir créé deux colonnes l'une pour l'ancienne équipe et l'autre pour la nouvelle équipe ?

A chaque affectation d'une nouvelle équipe, vous créez une nouvelle ligne.
Il suffisait d'indiquer dans une seule colonne le nom de la nouvelle équipe.
D'ailleurs, vous le constatez par vous même, 50% des colonnes "old_value" et "new_value" sont renseignées.

Envoyé par ScaIN

Ce que je n'arrive pas à faire dans ma requête c'est lui dire de ne sortir que les tickets qui ont appartenu à mon équipe, si il voit d'autre ligne avec d'autres équipe il ne doit pas tenir compte du ticket.

Il suffit de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
where t1.new_value = 'Service Desk(5)'
  and t1.date_mod  = (  select   max(date_mod)
                           from  matable as t2
                          where  t1.item_id = 84689
                     );

Autrement dit, vous recherchez sur la dernière ligne de votre ticket si celui-ci correspond bien à votre équipe.

Envoyé par ScaIN

Je vous mets en pièce jointe l'exemple d'un ticket qui a été escaladé dans plusieurs équipes pour que vous compreniez.

Il y a quand même un problème avec votre exemple.
Pour les lignes 92998026 et 92998145, seulement la colonne "new_value" est renseignée. Je ne vois pas d'alternance !

Et pour les lignes 93110822 & 93115832 la même équipe "windows (14)". Je n'appelle pas cela une alternance.

Et l'alternance ce fait dans quel sens ?
De la colonne old_value de la ligne précédente vers la colonne new_value de la ligne suivante ?
Ou bien de la colonne new_value de la ligne précédente vers la colonne old_value de la ligne suivante ?
Votre exemple ne reflète aucun de ces deux cas.

Il faudrait un peu plus d'explication sur ce que vous désirez obtenir à partir de l'exemple que vous donnez.

@+

Requête SQL sur BDD GLPI

Requêtes MySQL

Discussions similaires

Partager

Partager