ELK et Base de donnée = GLPI Dashboard!

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

Alors,

Dans la continuité des tests que je suis en train de mener autour de la stack ELK, j’ai pu tester la centralisation des logs (logstash/nxlog) la création de dashboards avec Kibana3, puis maintenant avec Kibana4 pour visualiser tout ça. C’est que du bonheur, je me suis bien amusé à jouer avec ça.

Il est temps de passer à la vitesse supérieure, l’idée maintenant et de pouvoir ajouter des données depuis des bases de données extérieures mySQL et SQL.

On va chercher à récupérer les infos de l’outil de ticketting (GLPI) utilisé quotidiennement dans ma boite, pour les injecter dans Elasticsearch à intervalles régulier et générer un ou des Dashboard Kibana4 dans ce genre là… Et surement bien mieux une fois l’outil maîtrisé! ci dessous, quelques essais fraîchement générés.

Kibana4_glpi

Kibana4_glpi2

On va partir du principe qu’on à un serveur ELK de dispo, même si logstash ne va nous être utile dans ce cas précis, ElasticSearch et Kibana dans leur dernières versions bien sur!

On va procéder en plusieurs étapes !

1/ Création requête d’une requête MySQL pour sélectionner les données voulues et les mettre en forme, puis création d’une vue simple à interroger.
2/ Elasticsearch, installation du plugin JDBC-River
3/ Création du dit River pour aller chercher les données
4/ Paramétrage Kibana et Création Dashboard.

1/ MySQL :

Ci dessous la vue que j’ai créé pour récupérer les infos dont j’estime avoir besoin, on y voit quelques bidouilles car certaines infos sont introuvables dans la base de GLPI, Demandes/Incident, Status du ticket etc…

CREATE VIEW nom_vue AS

SELECT 
	g.id,
    E.name as "Entité",
    concat(concat(U1.realname,' '),U1.firstname) as "Demandeur",
    concat(concat(U2.realname,' '),U2.firstname) 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 'Résolu'
        WHEN 6 THEN 'Clos'
        ELSE NULL 
        END as "Status",
    C.name as "Catégorie",
    G.name as "Objet",
    G.date,
    G.closedate,
    G.date_mod,
    G.actiontime,
    G.waiting_duration,
    G.close_delay_stat
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

Une fois la vue en place, j’ai créé un utilisateur avec des droits d’accès ReadOnly sur la base afin de pouvoir l’interroger depuis mon river.

2/ ElasticSearch !

Après avoir cherché de droite de gauche je suis assez rapidement tombé sur le concept des « Rivers » ElasticSearch, en gros ce sont des services qui sont chargés d’aller récupérer de la donnée depuis différentes sources : MySQL, Twitter, MongoDB, Wikipedia etc… Voir la doc ici

On va donc chercher à mettre en place un de ces fameux river pour aller chercher notre bonheur dans MySQL. Pour cela il faut installer un plugin Elasticsearch ainsi qu’un driver JDBC pour MySQL.

J’ai suivi la procédure dispo sur le GitHub du projet :

On installe le plugin jdbc-river :

cd /usr/share/elasticsearch
./bin/plugin --install jdbc --url http://xbib.org/repository/org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.4.0.10/elasticsearch-river-jdbc-1.4.0.10.zip

On télécharge le driver JDBC-MySQL et on le copie dans le répertoire des plugins et on redémarre ES :

curl -o mysql-connector-java-5.1.33.zip -L 'http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.33.zip/from/http://cdn.mysql.com/'
unzip mysql-connector-java-5.1.33.zip
cp mysql-connector-java-5.1.33-bin.jar /usr/share/elasticsearch/plugins/jdbc/
service elasticsearch restart

3/ Elasticsearch, et le fameux JDBC-River!

On va maintenant pouvoir se lancer dans la création d’un nouveau river, pour éviter d’avoir à jouer du cURL dans un terminal en permanence, j’ai utilisé le plugin chrome Sense, c’est une console de développement JSON pour ElasticSearch et c’est assez pratique.

On passe de ça :

curl -XPUT "http://xxx.xxx.xxx.xxx:9200/_river/jdbc/_meta" -d'
{
    "type" :  "jdbc",
    "jdbc" : {
        "url" : "jdbc:mysql://xxx.xxx.xxx.xxx:3306/glpi",
        "user" : "xxx",
        "password" : "xxx",
        "sql" : "select * from nom_view"
    }
}'

A ça, et c’est quand même vachement plus pratique pour bidouiller, ctrl-enter exécute la requête!

sense

 

On aurait pu se lancer directement dans la création d’un river qui aurait généré l’index ainsi que le mapping dans Elasticsearch, le seul problème c’est que par défaut les champs ont le paramètre « index »: « analyzed » . Cela pose problème au moindre espace ou point, Kibana sépare le champ en plusieurs valeurs et dans le cas de GLPI ça merde instantanément… (Nom Prénom, Entité Racine, etc…) et surtout ça aurait été beaucoup trop simple…!

J’ai donc créé un river, qui à créé automatiquement index et mapping dans ES, j’ai récupéré le contenu de ce dernier avec GET _mapping/jdbc dans la console Sense. Puis ensuite j’ai simplement ajouté le paramètre « index »: « not_analyzed »  a tout les champs string…

Je me suis bien galéré avec ça, mais ça m’as permis d’en apprendre un peu plus sur Elasticsearch! (je découvre…)

On va se simplifier la vie, et créer directement l’index et le mapping qui vont bien :

Dans la console Sense on va éxecuter les commandes suivantes :

Création de l’index :

PUT /jdbc

Creation du Mapping :

put /jdbc/_mapping/jdbc
{
            "properties": {
               "Catégorie": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "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"
               },
               "Technicien": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "Type": {
                  "type": "string",
                  "index": "not_analyzed"
               },
               "actiontime": {
                  "type": "long"
               },
               "date_mod" : {
                "format" : "dateOptionalTime",
                "type" : "date"
                },
               "close_delay_stat": {
                  "type": "long"
               },
               "closedate": {
                  "type": "date",
                  "format": "dateOptionalTime"
               },
               "date": {
                  "type": "date",
                  "format": "dateOptionalTime"
               },
               "id": {
                  "type": "long"
               },
               "waiting_duration": {
                  "type": "long"
               }
            }
         }

Jusque la pas de soucis, je créé donc mon river (voir ci dessous) à la différence près qu’il est en mode « One-Shot » (pas de synchro-active), Je fait mumuse un peu avec Kibana tout fonctionne c’est super!

Je paramètre donc mon river avec le paramètre Schedule pour aller récupérer les données selon un intervalle régulier. Et là…

Putain, quelle misère, le nombre de docs dans Elasticsearch double à chaque synchro, je me retrouve avec un truc totalement inexploitable rempli de doublons. Je me suis bien fait chier mais j’ai réussi à comprendre!

Concrètement,  j’ai ajouté dans la vue MySQL le champ ID de la table glpi_tickets, puis lors de la création du River j’ai fait correspondre le champ id de glpi avec le champ _id d’ElasticSearch, et là plus aucun problème!

Donc !

Creation du River avec une synchro toutes les 5 minutes:

PUT /_river/jdbc/_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",
        "schedule" : "0 0/5 * 1/1 * ? *",
        "max_retries": 3,
        "max_retries_wait" : "10s"
   }
}

Et voilà!  Si tout c’est bien passé vous devriez maintenant voir les synchro s’effectuer dans les logs Elasticsearch!

4/ Kibana !

On va maintenant aller créer un nouvel index dans Kibana4, ça se passe dans l’onglet settings! On tape le nom de notre index ainsi que le champ que l’on va prendre comme index temporel, ici la date d’ouverture.

 

index

Une fois l’index créé on va dans l’onglet discover, puis on va créer une nouvelle recherche avec l’index jdbc! Pour cela il faut cliquer sur l’engrenage en haut a droite :

index_jdbc

 

Et voilà!

On à maintenant notre GLPI en synchro incrémentielle avec ElasticSearch, y’a plus qu’a jouer du Kibana et se faire plaisir!

Prochaine étape, la même chose, mais sur un applicatif métier via SQL!

EDIT 2015/03/22La suite ici, GLPI Dashboard V2 ! 

 Desaille.fr | Le réseau aura raison de vous

4 commentaires

  1. Bonjour,

    Super tot, j’ai tout suivi mais je n’ai absolu rien qui dans kibana
    No results found
    Unfortunately I could not find any results matching your search. I tried really hard. I looked all over the place and frankly, I just couldn’t find anything good. Help me, help you. Here’s some ideas:

    peux tu m’aider stp ?

  2. Salut Vincent,
    Merci pour ce tuto super intéressant 😉 Je suis en train de travailler sur un projet et je découvre à peine ELK… il s’agit vraiment d’un outils puissant !
    Si tu pourrais m’éclaircir sur deux points, ce serait parfait :
    – Ton river est programmé toutes les 5 minutes. Cela veut-il dire que tu importes toutes les données à chaques fois, nouvelles comme anciennes ?
    – si ton river n’arrive pas à avoir accès à la base de données (pour une raison quelconque), peux-tu en être averti ? Je vois qu’il y a un paramètre « max_retries ».
    Merci beaucoups !
    Bye

    1. Salut,

      Y’a pas vraiment de recette miracle, faut jouer avec Kibana, je t’invite à consulter les tutos suivants :

      https://www.elastic.co/blog/kibana-4-video-tutorials-part-1
      https://www.elastic.co/blog/kibana-4-video-tutorials-part-2
      https://www.elastic.co/blog/kibana-4-video-tutorials-part-3
      https://www.elastic.co/blog/kibana-4-video-tutorials-part-4
      https://www.timroes.de/2015/02/07/kibana-4-tutorial-part-3-visualize/
      https://www.timroes.de/2015/02/07/kibana-4-tutorial-part-4-dashboard/

      Une fois que tu as pigé la logique du truc, c’est vraiment assez simple, le plus dur c’est de savoir ce que l’on veut afficher. Injecte des data et teste! En espérant que ça t’aide

      Bye

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.