Avant-propos

Il est important de comprendre que la database centreon_storage est la table du temps réel.

Toutes les données affichées dans la console en dehors des vues configuration et administration proviennent de cette database.

Il y a un lien entre les databases centreon et centeon_storage ce sont les ID pour :

• Les hôtes
• Les différents regroupements
• Services
• Hosts
• Services

Donc tous les vues temps réel s’appuient sur cette database en utilisant la durée de rétention pour afficher l’historique dans les différentes vues.

La structure de la database est sensiblement identique à celle de centreon.

Pour faire un raccourci dans la database centreon les tables sont au singulier et dans centreon-storage au pluriel.

Ainsi par exemple les tables on peut faire les relations suivantes :

Nous pouvons noter que certaines tables n’existent pas dans cetreon_storage. Donc la console aura besoin des 2 databases pour afficher les informations dans la console.

Démarche

Les requêtes s’appuieront sur les données valides dans la database centreon pour afficher les informations contenues dans centreon_storage.

Cela évitera d’afficher les hôtes et les services désactivés

Dû à la durée de rétention certains host, services, groupes, catégories sont présentes dans la database centreon_storage

Nous allons rechercher les hôtes ou les services actifs dans centreon et remonter leur état depuis centreon_storage.

J’ai fait un export de mon centreon et je l’ai importé sur un mysql en local pour titiller la bête. 😀

J’ai également modifié volontairement certains status pour valider l’affichage des ceux-ci.

Lister l’état des hosts

Schéma

Analyse

Ce sera certainement l’analyse la plus courte de cet article 😀

On va rechercher dans centreon les hôtes actifs ,qui ne sont pas des templates et dans centreon_storage leurs états

Requête

Requête depuis la database centreon

SELECT HST.host_name AS "HOST",<br>REPLACE(REPLACE(REPLACE(REPLACE(CHST.state,"0","OK"),"1","UNREACHABLE"),"2","DOWN"),"3","PENDING") AS STATUS<br>FROM centreon.host AS HST<br>LEFT JOIN centreon_storage.hosts AS CHST ON CHST.host_id=HST.host_id<br>WHERE HST.host_register="1"<br>AND HST.host_activate="1"

SELECT HST.host_name AS "HOST",<br>REPLACE(REPLACE(REPLACE(REPLACE(CHST.state,"0","OK"),"1","UNREACHABLE"),"2","DOWN"),"3","PENDING") AS STATUS<br>FROM centreon.host AS HST<br>LEFT JOIN centreon_storage.hosts AS CHST ON CHST.host_id=HST.host_id<br>WHERE HST.host_register="1"<br>AND HST.host_activate="1"

Requête depuis la database centreon_storage

SELECT HST.host_name AS "HOST",
REPLACE(REPLACE(REPLACE(REPLACE(CHST.state,"0","OK"),"1","UNREACHABLE"),"2","DOWN"),"3","PENDING") AS STATUS
FROM centreon_storage.hosts AS CHST
LEFT JOIN centreon.host AS HST ON HST.host_id=CHST.host_id
WHERE HST.host_register="1"
AND HST.host_activate="1"

SELECT HST.host_name AS "HOST",
REPLACE(REPLACE(REPLACE(REPLACE(CHST.state,"0","OK"),"1","UNREACHABLE"),"2","DOWN"),"3","PENDING") AS STATUS
FROM centreon_storage.hosts AS CHST
LEFT JOIN centreon.host AS HST ON HST.host_id=CHST.host_id
WHERE HST.host_register="1"
AND HST.host_activate="1"

Résultat

Lister l’état hosts dans un hostgroup

Schéma

Analyse

Le schéma ci-dessus nous montre comment la table centreon_storage.hosts est alimenté par les tables de centreon host et nagios_server.

• La table host_id de centreon alimente le champ host_id de centreon_storage
• La table nagios_server de centreon alimente le champ instance_id dans centreon_storage.

Requête

SELECT
NS.name AS Poller,
HST.host_name AS HOST,
HST.host_alias AS ALIAS,
REPLACE(REPLACE(REPLACE(CHST.state, "0", "OK"), "1", "DOWN"), "2", " UNREACHABLE ") AS STATUS
FROM centreon.host AS HST
LEFT JOIN centreon_storage.hosts AS CHST on CHST.host_id=HST.host_id
LEFT JOIN centreon.nagios_server AS NS ON NS.id=CHST.instance_id
WHERE host_register="1"
AND host_activate='1'
ORDER BY NS.name,HST.host_name;

SELECT
NS.name AS Poller,
HST.host_name AS HOST,
HST.host_alias AS ALIAS,
REPLACE(REPLACE(REPLACE(CHST.state, "0", "OK"), "1", "DOWN"), "2", " UNREACHABLE ") AS STATUS
FROM centreon.host AS HST
LEFT JOIN centreon_storage.hosts AS CHST on CHST.host_id=HST.host_id
LEFT JOIN centreon.nagios_server AS NS ON NS.id=CHST.instance_id
WHERE host_register="1"
AND host_activate='1'
ORDER BY NS.name,HST.host_name;

Résultat

Lister l’état des services d’un hôte

Schéma

Analyse

Nous voyons bien l’héritage des tables de centreon_storage par celles de centreon

Contrairement à la database centreon le lien entre les hosts et les services sont directement dans la table des services.

Requête

SELECT
HST.host_name AS HOST,
SVC.service_description AS SERVICE,
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.host AS HST
LEFT JOIN centreon_storage.hosts AS CHST on CHST.host_id=HST.host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_register="1"
AND HST.host_activate='1'
AND SVC.service_activate="1"
AND SVC.service_register="1"
AND CHST.state=0
ORDER BY HST.host_name,SVC.service_description;

SELECT
HST.host_name AS HOST,
SVC.service_description AS SERVICE,
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.host AS HST
LEFT JOIN centreon_storage.hosts AS CHST on CHST.host_id=HST.host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_register="1"
AND HST.host_activate='1'
AND SVC.service_activate="1"
AND SVC.service_register="1"
AND CHST.state=0
ORDER BY HST.host_name,SVC.service_description;

Résultat exemple

Lister l’état services dans un hostgroup

Schéma

Analyse

Là ça commence de devenir super sweet 😀

Pour afficher 4 colonnes nous avons besoin de 6 tables dans 2 databases rien que cela 😛

La démarche est la suivante :

• On recherche les hostgroups actifs
• On recherche les hosts dans le hostgroup
• On vérifie que les hosts sont bien actifs
• On recherche les services rattachés aux hosts
• On vérifie que les services sont actifs
• On regarde enfin leur status en parsant la valeur par une indication parlante

Tout un programme 😀

Requête

SELECT HG.hg_name AS HOSTGROUP,
HST.host_name,
SVC.service_description AS SERVICE,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN"),"4","PENDING") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR on HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN  centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.hosts AS CHST ON CHST.host_id=HST.host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_activate="1"
AND HST.host_register="1"
AND SVC.service_activate="1"
AND SVC.service_register="1"
AND SVC.service_description like "%ping%"
ORDER BY HG.hg_name, HST.host_name

SELECT HG.hg_name AS HOSTGROUP,
HST.host_name,
SVC.service_description AS SERVICE,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN"),"4","PENDING") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR on HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN  centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.hosts AS CHST ON CHST.host_id=HST.host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_activate="1"
AND HST.host_register="1"
AND SVC.service_activate="1"
AND SVC.service_register="1"
AND SVC.service_description like "%ping%"
ORDER BY HG.hg_name, HST.host_name

Résultat

Lister l’état services dans un servicegroup

Schéma

Analyse

On continue 😀

Pour afficher 4 colonnes nous avons toujours besoin de 6 tables dans 2 databases . Et oui 😛

La démarche est la suivante :

• On recherche les servicesgroups actifs
• On recherche la relation entre les servicegroups et les services
• On vérifie que les services sont bien actifs
• On recherche les hosts rattachés aux services
• On vérifie que les hosts sont actifs
• On regarde enfin le status des services en parsant la valeur par une indication parlante

Encore tout un programme 😀

Requête

SELECT DISTINCT
SG.sg_name AS "SERVICEGROUP",
HST.host_name AS "HOST",
SVC.service_description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.servicegroup AS SG
LEFT JOIN centreon.servicegroup_relation AS SGR ON SGR.servicegroup_sg_id=SG.sg_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=SGR.service_service_id
LEFT JOIN centreon.host_service_relation AS HSR ON HSR.service_service_id=SVC.service_ID
LEFT JOIN centreon.host AS HST ON HST.host_id=HSR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC on CSVC.service_id=SVC.service_id
WHERE SVC.service_activate='1'
AND SVC.service_register="1"
AND SVC.service_description like "OS%process%snmp%"
ORDER BY SG.sg_name, HST.host_name,SVC.service_description;

SELECT DISTINCT
SG.sg_name AS "SERVICEGROUP",
HST.host_name AS "HOST",
SVC.service_description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.servicegroup AS SG
LEFT JOIN centreon.servicegroup_relation AS SGR ON SGR.servicegroup_sg_id=SG.sg_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=SGR.service_service_id
LEFT JOIN centreon.host_service_relation AS HSR ON HSR.service_service_id=SVC.service_ID
LEFT JOIN centreon.host AS HST ON HST.host_id=HSR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC on CSVC.service_id=SVC.service_id
WHERE SVC.service_activate='1'
AND SVC.service_register="1"
AND SVC.service_description like "OS%process%snmp%"
ORDER BY SG.sg_name, HST.host_name,SVC.service_description;

Résultat

Lister l’état services dans un hostgroup

Schéma

Analyse

La démarche reste la même :

• On recherche les hostgroups actifs
• On recherche la relation entre la table hostgroup et la table host
• On recherche les services rattachés aux hôtes
• On vérifie que les services sont actifs
• On regarde enfin le status des services en parsant la valeur par une indication parlante

Nous verrons dans un premier temps une requête raccourcie qui ne vérifie pas si les services sont bien actifs, du coup nous avons l’historique des services désactivés et enfin THE requête 😀

Requête

Version courte

SELECT DISTINCT
HG.hg_name AS "HOSTGROUP",
HST.host_name AS "HOST",
CSVC.description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR ON HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
WHERE HST.host_activate="1"
AND CSVC.description like "OS%process%snmp%"
ORDER BY HG.hg_name, HST.host_name,CSVC.description;

SELECT DISTINCT
HG.hg_name AS "HOSTGROUP",
HST.host_name AS "HOST",
CSVC.description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR ON HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
WHERE HST.host_activate="1"
AND CSVC.description like "OS%process%snmp%"
ORDER BY HG.hg_name, HST.host_name,CSVC.description;

Version complète

SELECT DISTINCT
HG.hg_name AS "HOSTGROUP",
HST.host_name AS "HOST",
CSVC.description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR ON HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_activate="1"
AND CSVC.description like "OS%process%snmp%"
AND SVC.service_activate="1"
ORDER BY HG.hg_name, HST.host_name,CSVC.description;

SELECT DISTINCT
HG.hg_name AS "HOSTGROUP",
HST.host_name AS "HOST",
CSVC.description AS "SERVICE",
REPLACE(REPLACE(REPLACE(REPLACE(CSVC.state,"0","OK"),"1","WARNING"),"2","CRITICAL"),"3","UNKNOWN") AS STATUS
FROM centreon.hostgroup AS HG
LEFT JOIN centreon.hostgroup_relation AS HGR ON HGR.hostgroup_hg_id=HG.hg_id
LEFT JOIN centreon.host AS HST ON HST.host_id=HGR.host_host_id
LEFT JOIN centreon_storage.services AS CSVC ON CSVC.host_id=HST.host_id
LEFT JOIN centreon.service AS SVC ON SVC.service_id=CSVC.service_id
WHERE HST.host_activate="1"
AND CSVC.description like "OS%process%snmp%"
AND SVC.service_activate="1"
ORDER BY HG.hg_name, HST.host_name,CSVC.description;

Résultat

Conclusion

Nous venons de voir plusieurs façons de requêter autour de centreon_storage.

Je n’ai pas abordé tous les cas de figures, ce serait trop long, le but étant de faire comprendre le fonctionnement des relation entre les tables et les databases.