Kibana et GLPI : Dashboard v2!

Petit retour sur le précédent article GLPI-Dashboard :

EDIT : Nouvel  article, automatiser le paramétrage et la communication entre ES et GLPI : Kibana pour GLPI en 15 minutes!

Après avoir pas mal joué avec Kibana et les données en provenance de glpi je me suis rendu compte qu’il y avait pas mal de truc qui manquaient pour avoir des infos fiables et utilisables.

On va donc voir ici comment améliorer tout ça, et avoir encore plus de matière pour jouer!

1/ Les Demandeurs et Techniciens :

Dans la vue MySQL je remonte les champs users_id_recipient et users_id_lastupdater, concrètement ça récupère le nom de la personne qui ouvre le ticket et le nom de la dernière personne qui l’a modifié, les champs « Par » et « Dernière modification ».

glpilastupdater

Au début il m’a semblé pouvoir fonctionner comme ça, par souci de simplicité (voir de flemme 🙂 ), étant donné qu’une bonne partie de mes utilisateurs ouvrent leurs tickets  par mail, via un collecteur IMAP, ils sont donc identifiés comme « recipient », même combat pour les techniciens, ils sont généralement les derniers à modifier le ticket et donc considérés comme « lastupdater ».

J’ai vite déchanté!

Je me suis donc attelé à modifier la requête SQL, pour aller récupérer le premier utilisateur et le premier technicien enregistré pour un ticket de la façon suivante : (je met le code des vues complètes en fin d’article), je sais pas si c’est très propre niveau MySQL mais ça à le mérite de marcher…

Si vous avez des conseils ou des remarques, n’hésitez pas à vous lâcher dans les commentaires !

-- On récupère l'Id utilisateur et on le stocke dans une colonne DID (G = Glpi_tickets)
(select Ur.users_id from glpi_tickets_users Ur where Ur.type = 1 and Ur.tickets_id = G.id limit 1) as "DID"

-- On fait le lien entre le DID et le nom d'utilisateur pour l'afficher en "NOM Prénom"
(select concat(concat(Ur2.realname,' '),Ur2.firstname) from glpi_users Ur2 where DID = Ur2.id) AS "Demandeur"

Exactement le même procédé pour récupérer les techniciens, plus de soucis de ce côté la!

2/ Les tickets supprimés :

Je me suis aperçu que j’avais un nombre de tickets incohérent, comparé au statistiques sorties directement via glpi… Après quelques recherches je me suis aperçu que cela venait des tickets supprimés. J’ai donc rajouté une clause « where » qui ne sélectionne que les tickets avec le champ « is_deleted = 0 « .

3/ Les temps de traitements des tickets :

Très important en fait, j’ai été con de ne pas les prendre dès le début, ça se récupère très simplement et ça en dit beaucoup!

Par exemple afficher le délai moyen de prise en compte d’une demande par catégorie, par entité, voir par technicien (l’objectif n’est pas de les fliquer non plus hein! 🙂 )

J’ai ajouté ces champs à la vue :

G.waiting_duration/3600 as "temps attente", 
G.close_delay_stat/3600 as "temps cloture",
G.solve_delay_stat/3600 as "temps resolution", 
G.takeintoaccount_delay_stat/3600 as "temps prise en compte"

4/ Les tâches ! 

Le gros du boulot!

En faisant quelques mise en forme de données, notamment en jouant avec le temps des tickets (actiontime), je me suis rendu compte que ça « merdait », les temps ne collaient pas avec les temps réellement logués,  c’est ok sur des tickets qui ont le statut « clos » et qui ont étés ouverts dans une intervalle de temps donnée, mais pour le reste…

On peut en tirer une tendance globale sur de longues plages de dates, mais sur des périodes courtes, ça ne fonctionne pas terrible.

J’ai donc créé une nouvelle vue, basée ce coup-ci sur la table des tâches!

Quitte à récupérer le technicien de la tâche, la date de la tache et le temps passé, autant remonter jusqu’à la table tickets et récupérer la totale, à savoir le demandeur, la catégorie, l’entité, l’objet du ticket, etc…

Avec ça, on devrait enfin pouvoir jouer avec des données fiables!

En trois clics, et c’est la toute la force de kibana, on peut afficher en  pourcentage la somme du temps passé par catégorie par technicien, le tout graphiquement avec la possibilité de filtrer dynamiquement dessus!

Comme on dirait quelqu’un de chez moi « C’est ça qu’on veut! »

time_cat_tech

Tout les temps sont en secondes, j’ai donc tout divisé par 3600 directement dans les vues, pour les avoir en heures, mais malheureusement pas en base 60. Si quelqu’un sait comment gérer ça dans Elasticsearch, qu’il se manifeste svp! J’ai galéré à souhait avec les formats dans les mappings, je les ai pratiquement tous essayés mais je n’ai pas réussi à récupérer du temps. A chaque fois je récupère une date, mais du temps sous le format HH:MM, je cherche encore!

Autre exemple de qu’on peut faire, je l’aime bien celui la… Le top des demandeurs par technicien (somme du temps des tâches) :

time_demand_tech

Je l’ai surnommé « A Chacun à son Emmerdeur »  🙂 !

Après, « y’a plus qu’à » savoir ce qu’on veut remonter, quelles sont les infos pertinentes!…

Ca deviens vite le bazar, mon dashboard c’est un peu la nasa, ou plutôt le marché aux puces… Surtout sur le 13″ ou je suis en train d’écrire…  je vous laisse juger :

dashboard
 

Pour mettre ça en place la procédure est la même que décrite dans le précédent article sur Glpi + ELK :

On commence par créer les vues :

Vue Tickets :

CREATE VIEW nom_vue_ticket as
SELECT
    G.id,
    E.name as "Entité",
    (select Ur.users_id from glpi_tickets_users Ur where Ur.type = 1 and Ur.tickets_id = G.id limit 1) as "DID",
	(select Ur.users_id from glpi_tickets_users Ur where Ur.type = 2 and Ur.tickets_id = G.id limit 1) as "TID",
	(select concat(concat(Ur2.realname,' '),Ur2.firstname) from glpi_users Ur2 where DID = Ur2.id) AS "Demandeur",
    (select concat(concat(Ur2.realname,' '),Ur2.firstname) from glpi_users Ur2 where TID = Ur2.id) AS "Technicien",
    R.name as "Source",
    CASE g.type 
		WHEN 1 THEN 'Incident' 
        ELSE 'Demande' 
        END as "Type",
    CASE g.status 
		WHEN 1 THEN 'Nouveau' 
        WHEN 2 THEN 'En Cours'
        WHEN 3 THEN 'En Cours (Attribué)'
        WHEN 4 THEN 'En Attente'
        WHEN 5 THEN 'Resolu'
        WHEN 6 THEN 'Clos'
        ELSE NULL 
        END as "Status",
    C.name as "Catégorie",
    G.name as "Objet",
    G.date,
    G.closedate,
    G.actiontime/3600 as "actiontime",
    G.waiting_duration/3600 as "temps attente", 
    G.close_delay_stat/3600 as "temps cloture",
    solve_delay_stat/3600 as "temps resolution", 
    takeintoaccount_delay_stat/3600 as "temp prise en compte"
FROM 
    glpi_tickets G left outer join
    glpi_itilcategories C on G.itilcategories_id = C.id left outer join
    glpi_entities E on G.entities_id = E.id left outer join 
    glpi_users U1 on G.users_id_recipient = U1.id left outer join
    glpi_users U2 on G.users_id_lastupdater = U2.id left outer join
    glpi_requesttypes R on G.requesttypes_id = R.id
where 
	G.is_deleted = 0

order by g.date desc

Vue Tâches :

CREATE VIEW nom_vue_tasks AS

select 
	Gt.id,
    Gt.date,
    E.name as "Entité",
    (select Ur.users_id from glpi_tickets_users Ur where Ur.type = 1 and Ur.tickets_id = G.id limit 1) as "DID",
    (select concat(concat(Ur2.realname,' '),Ur2.firstname) from glpi_users Ur2 where DID = Ur2.id) AS "Demandeur",
	concat(concat(Gu.realname,' '),Gu.firstname) as "Technicien",
    Gc.name as "Categorie",
    G.name as "Objet",
	Gt.content as "Tache", 
	Gt.actiontime/3600 as "actiontime"
    
from 
	glpi_tickettasks Gt left outer join
	glpi_users Gu on Gt.users_id = Gu.id left outer join
	glpi_tickets G on G.id = Gt.tickets_id left outer join
    glpi_entities E on G.entities_id = E.id left outer join 
    glpi_itilcategories Gc on Gc.id = G.itilcategories_id

order by Gt.date desc

Ensuite on va créer les deux index Elasticsearch (toujours avec le plugin Sense)

put /glpi_tickets
put /glpi_tasks

On créé les mappings pour les deux vues :

Mapping glpi_tickets

put /glpi_tickets/_mapping/jdbc
{
            "properties": {
               "Catégorie": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "DID": {
                  "type": "long"
               },
               "Demandeur": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Entité": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Objet": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Source": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Status": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "TID": {
                  "type": "long"
               },
               "Technicien": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Type": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "actiontime": {
                  "type": "double"
               },
               "closedate": {
                  "type": "date",
                  "format": "dateOptionalTime"
               },
               "date": {
                  "type": "date",
                  "format": "dateOptionalTime"
               },
               "id": {
                  "type": "long"
               },
               "temps prise en compte": {
                  "type": "double"
               },
               "temps attente": {
                  "type": "double"
               },
               "temps cloture": {
                  "type": "double"
               },
               "temps resolution": {
                  "type": "double"
               }
            }
         }

Mapping glpi_tasks

put /glpi_tasks/_mapping/jdbc
{
            "properties": {
               "Categorie": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "DID": {
                  "type": "long"
               },
               "Demandeur": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Entité": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Objet": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Tache": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Technicien": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "actiontime": {
                  "type": "double"
               },
               "date": {
                  "type": "date",
                  "format": "dateOptionalTime"
               },
               "id": {
                  "type": "long"
               }
            }
         }

Maintenant on envoie, ou plutôt on va aller chercher la purée avec les deux rivers jdbc !

River Glpi tickets

PUT /_river/glpi_tickets/_meta
{    "type" : "jdbc",
    "jdbc" : {
        "strategy" : "simple",
        "url" : "jdbc:mysql://xxx.xxx.xxx.xxx:3306/glpi",
        "user" : "xxxx",
        "password" : "xxxx",
        "sql" : "select *, id as _id from nom_vue_tickets",
        "index" : "glpi_tickets",
        "schedule" : "0 0/5 * 1/1 * ? *",
        "max_retries": 3,
        "max_retries_wait" : "10s"
   }
}

River Glpi tasks

PUT /_river/glpi_tasks/_meta
{    "type" : "jdbc",
    "jdbc" : {
        "strategy" : "simple",
        "url" : "jdbc:mysql://xxx.xxx.xxx.xxx:3306/glpi",
        "user" : "xxxx",
        "password" : "xxxx",
        "sql" : "select *, id as _id from nom_vue_tickets",
        "index" : "glpi_tasks",
        "schedule" : "0 0/5 * 1/1 * ? *",
        "max_retries": 3,
        "max_retries_wait" : "10s"
   }
}

Amusez vous bien!

Encore une fois, Hésitez pas à vous exprimer dans les commentaires.

11 commentaires

  1. J’ai tout implémentée correctement cependant rien ne remonte dans mon kibana.
    Dans mes logs elasticsearch, je vois bien que mes rivers fonctionnent :

    [2015-06-19 16:55:06,998][INFO ][river.jdbc.RiverMetrics ] pipeline org.xbib.elasticsearch.plugin.jdbc.RiverPipeline@56e63e9 complete: river jdbc/glpi_tasks metrics: 6 rows, 0.035992565260039786 mean, (0.0 0.0 0.0), ingest metrics: elapsed 6 seconds, 2.75 KB bytes, 402.0 bytes avg, 0 MB/s

    [2015-06-19 16:55:14,085][INFO ][river.jdbc.RiverMetrics ] pipeline org.xbib.elasticsearch.plugin.jdbc.RiverPipeline@50acc3a1 complete: river jdbc/glpi_tickets metrics: 3140 rows, 18.052640672562358 mean, (15.959134839987069 3.299097817205148 1.1058143382226129), ingest metrics: elapsed 13 seconds, 1.43 MB bytes, 477.0 bytes avg, 0.103 MB/s

    Qu’est ce que j’aurais pu oublier?

  2. Salut,

    j’essaye d’éxécuter la création de vue directement depuis phpmyadmin mais j’obtiens l’erreur suivante : #1054 – Unknown column ‘g.type’ in ‘field list’.

    Y’a t’il une procédure pour faire cela?

      1. Pour info il y a 2 erreurs dans la déclaration de tes rivers :
        River Glpi tickets :
        « sql » : « select *, id as _id from nom_vue_tickets », — nom_vue_ticket (sans le s)

        River Glpi tasks:
        « sql » : « select *, id as _id from nom_vue_tickets », — nom_vue_tasks

  3. Bonjour,
    J’ai pas de commentaire a ces sujets mais j’ai un problème avec la configuration des Entités.
    Dans mon cas, c’est un siège avec des agences d’ou la necessité des entités sauf que les Techniciens sont seulement dans l’entité principal. Du cous les Tickets et notifications des autres entités doivent remontés à l’entité principal.
    Mon soucis est que les tickets ne remontent pas et lors de l’attribution des tickets les techniciens sont pas visibles dans la liste.
    AU SECOURS!!!!!!

    1. Salut,

      Je pense qu’il faut que tes techniciens appartiennent à l’entité racine mais en mode R (Récursif), afin qu’il puissent voir toutes les sous-entités.

      Bye

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.