TP 2 — Lire l'état des stations dans leurs données métier (Grafana)

Auteur : Thibaut Fontaine
Institution : Université de la Réunion
Date : 2025

---

Table des matières

• Objectifs et Contexte
• Mission et Prérequis
• Consignes de Réalisation
• Questions de Validation
• Critères d’Évaluation
• Livrables Attendus
• Ressources et Conseils

---

Objectifs et Contexte

📡 Poste SOC · La Réunion — vous supervisez Port-aux-Français (Kerguelen), à ~3 300 km. Liaison SATCOM nominale.

🎯 Objectif métier — transformer les données métier TAAF (RH, météo, énergie) en dashboards qui aident à décider, pas seulement à observer.

Contexte

À Kerguelen, 34 personnes, six mois de rotation, des stocks qui se vident et une météo qui décide de tout. Ces chiffres existent déjà en base — personne ne les regarde. Au TP 1 vous avez vu tourner les conteneurs ; ici vous lisez ce qu’ils contiennent.

Vous construisez des dashboards à partir des tables métier (RH, communications, météo) : passer du « est-ce que ça tourne ? » au « qu’est-ce que ça raconte ? ». C’est cette lecture du normal qui fera ressortir l’anormal — un export hors-norme, un accès à une heure impossible.

🔴 Au fil de l’enquête

Le jour J à 10:12Z, l’attaquant exfiltrera la table genetic_samples (KER-2024-002) sans approved_by. Un dashboard métier qui connaît le volume normal d’accès à ces tables est ce qui transformera ce vol en pic visible. Vous apprenez ici à regarder les données — pour qu’un jour, leur fuite saute aux yeux.

---

Vous disposez d’une base de données PostgreSQL contenant les données opérationnelles des bases TAAF. Votre mission : créer deux dashboards de supervision pour permettre aux gestionnaires de suivre les activités critiques.

Architecture du TP2

graph LR
 subgraph "Base de Données"
 PG[(PostgreSQL
postgres-af
base_af)]
 end

 subgraph "Tables"
 T1[communications_satellite]
 T2[personnel_base]
 T3[conditions_meteo]
 T4[equipements_critiques]
 T5[stocks_approvisionnement]
 end

 subgraph "Visualisation"
 GRAF[Grafana
Port: 3000]
 D1[Dashboard
Communications]
 D2[Dashboard
RH Personnel]
 end

 PG --> T1
 PG --> T2
 PG --> T3
 PG --> T4
 PG --> T5

 T1 -->|requêtes SQL|GRAF
 T2 -->|requêtes SQL|GRAF

 GRAF --> D1
 GRAF --> D2

 U[Vous] -->|créez dashboards|GRAF

 classDef db fill:#ffcc99,stroke:#333,stroke-width:2px
 classDef tables fill:#e6e6e6,stroke:#333,stroke-width:1px
 classDef viz fill:#99ff99,stroke:#333,stroke-width:2px
 classDef user fill:#99ccff,stroke:#333,stroke-width:2px

 class PG db
 class T1,T2,T3,T4,T5 tables
 class GRAF,D1,D2 viz
 class U user

Dans ce TP, vous allez :

1. Connecter Grafana à la base de données PostgreSQL
2. Créer des requêtes SQL pour extraire les données métier
3. Construire 2 dashboards : Communications Satellite + Gestion RH

Base de données disponible

Base PostgreSQL utilisée dans ce TP

Pour ce TP, nous utilisons :

• Instance PostgreSQL : postgres-af (Alfred Faure) - Port 5432
• Bases de données disponibles : personnel_base, conditions_meteo, equipements_critiques, communications_satellite

L’instance unique postgres-af contient toutes les bases de données des bases polaires TAAF.

Tables disponibles

Table	Description
conditions_meteo	Données météorologiques
personnel_base	Effectifs et rotations
equipements_critiques	État du matériel
communications_satellite	Liaisons avec l’extérieur
stocks_approvisionnement	Inventaires critiques

Schéma des colonnes — à lire avant de construire les panels

Le schéma complet est dans la ressource Bases de données SQL. Table clé de ce TP, communications_satellite :

Colonne	Type	Description
id	SERIAL	Identifiant unique
timestamp_comm	TIMESTAMP	Date/heure de la communication
station_receptrice	VARCHAR	Station réceptrice (Toulouse / La Réunion)
type_communication	VARCHAR	Email / Voix / Données
qualite_signal	NUMERIC(5,2)	Qualité du signal (0–100 %)
volume_donnees_mb	NUMERIC(10,2)	Volume transmis (MB)
statut	VARCHAR	Réussie / Partielle / Échec
base_name	VARCHAR	Port-aux-Français / Alfred Faure / Dumont d’Urville

Pour les colonnes des autres tables (personnel_base, conditions_meteo…), lancez \d nom_table dans psql, ou voir la ressource ci-dessus.

---

Mission et Prérequis

Mission

Créer 2 dashboards Grafana fonctionnels :

1. Dashboard Communications Satellite
2. Dashboard Gestion RH Personnel

Prérequis techniques

Convention d’accès — localhost, IP_VM2 ou nom de service ?

L’adresse change selon d’où vous lancez la commande :

• curl / CLI en SSH sur la VM → localhost est correct (vous êtes sur la machine).
• Navigateur depuis votre poste (« Ouvrez http://localhost:3000 ») → utilisez http://IP_VM2:3000.
• Datasource Grafana (Prometheus, Loki, PostgreSQL) → nom de service Docker (http://prometheus:9090), jamais localhost.

OBLIGATOIRE - TP précédent :

• TP 1 - Grafana Metric terminé : Dashboard supervision conteneurs fonctionnel
• Stack de supervision démarrée (docker compose up -d)
• Interface Grafana accessible (http://localhost:3000)

Configuration supplémentaire :

• Source de données PostgreSQL ajoutée à Grafana
• Connexion à la base base_af fonctionnelle

---

Consignes de Réalisation

Étape 0 : Configuration Source de Données PostgreSQL

Avant de créer les dashboards, vous devez configurer la connexion à PostgreSQL dans Grafana.

0.1 Accéder à Grafana

1. Ouvrez votre navigateur et accédez à http://localhost:3000
2. Connectez-vous avec les identifiants par défaut :
   • Username : admin
   • Password : AdminTAAF2024!

0.2 Ajouter la Source de Données PostgreSQL

1. Dans le menu latéral gauche, cliquez sur ⚙️ Configuration → Data Sources
2. Cliquez sur le bouton Add data source
3. Recherchez et sélectionnez PostgreSQL dans la liste

0.3 Configuration de la Connexion

Configurez les paramètres suivants.

Grafana et postgres-af sont sur la même VM

Dans le modèle all-in-one, Grafana et PostgreSQL postgres-af tournent dans le même docker compose sur la VM monitoring. Ils partagent donc le réseau Docker interne : Grafana joint la base par son nom de service Docker, pas par une IP.

Host = nom de service Docker, jamais localhost

Grafana → PostgreSQL postgres-af = même réseau Docker.

Host à renseigner : postgres-af:5432. N’utilisez ni localhost:5432 (Grafana est dans un conteneur, localhost y désigne le conteneur Grafana lui-même) ni l’IP de la VM.

Configuration datasource PostgreSQL dans Grafana :

Paramètre	Valeur	Description
Name	PostgreSQL TAAF - Alfred Faure	Nom de la source de données
Host	postgres-af:5432	Nom de service Docker (même réseau)
Database	base_af	Nom de la base de données (unique)
User	taaf_admin	Utilisateur PostgreSQL
Password	(voir ci-dessous)	Mot de passe de l’utilisateur
TLS/SSL Mode	disable	Pas de chiffrement (réseau interne)

Trouver le mot de passe PostgreSQL :

# Sur la VM monitoring, consulter le docker-compose.yml
grep POSTGRES_PASSWORD docker-compose.yml

# Ou se connecter directement au conteneur
docker exec -it postgres-af env | grep POSTGRES_PASSWORD

0.4 Tester la Connexion

1. Faites défiler vers le bas de la page de configuration
2. Cliquez sur le bouton Save & Test
3. Vérifiez que le message suivant apparaît : ✅ “Database Connection OK”

Si vous obtenez une erreur, vérifiez :

• Que le conteneur PostgreSQL est bien démarré : docker ps | grep postgres
• Que les identifiants sont corrects
• Que la base de données base_af existe

0.5 Vérification avec une Requête Test

1. Retournez à l’accueil de Grafana
2. Cliquez sur Explore (icône boussole) dans le menu latéral
3. Sélectionnez PostgreSQL TAAF comme source de données
4. Passez en mode SQL Editor (bouton en haut à droite)
5. Testez la requête suivante :

SELECT COUNT(*) as total_communications
FROM communications_satellite;

6. Cliquez sur Run query
7. Vérifiez que le résultat retourne un nombre > 0

Si la requête fonctionne, votre source de données PostgreSQL est correctement configurée ! ✅

✅ Validation Étape 0 - Avant de continuer

Vérifiez que vous avez bien :

• Grafana accessible sur http://localhost:3000
• Source de données PostgreSQL ajoutée avec succès
• Message “Database Connection OK” confirmé
• Requête de test retournant un résultat > 0

Si l’un de ces points échoue, ne passez pas à l’Étape 1. Corrigez le problème en vous référant à la section de dépannage ou consultez votre enseignant.

---

Étape 1 : Analyse des données

1. Connectez-vous à votre base PostgreSQL et explorez les tables

Requêtes d’exploration recommandées

-- Exemples de requêtes d'exploration
SELECT COUNT(*) FROM communications_satellite;
SELECT DISTINCT type_communication FROM communications_satellite;
SELECT DISTINCT statut FROM personnel_base;

✅ Validation Étape 1 - Avant de continuer

Vérifiez que vous avez bien :

• Exploré les tables communications_satellite et personnel_base
• Identifié les colonnes pertinentes pour votre dashboard
• Testé au moins 3 requêtes SQL différentes avec succès
• Compris la structure des données (types, timestamps, statuts)

Passez à l’Étape 2 uniquement si vous êtes à l’aise avec les requêtes SQL sur ces tables.

Étape 2 : Dashboard Communications

2. Créer un dashboard contenant au minimum :

Panneaux requis

• Panneau 1 : Tableau résumé des communications par station
• Panneau 2 : Graphique temporel de la qualité du signal
• Panneau 3 : Répartition des statuts (réussie/partielle/échec)
• Panneau 4 : Volume de données transférées par type
• Panneau 5 : Liste des communications problématiques

Requête de départ — Panneau 1 (résumé par station)

Un panneau Table avec cette requête, pour démarrer :

SELECT station_receptrice,
       COUNT(*)                      AS nb_comms,
       ROUND(AVG(qualite_signal), 1) AS qualite_moy,
       SUM(volume_donnees_mb)        AS volume_total_mb
FROM communications_satellite
GROUP BY station_receptrice
ORDER BY nb_comms DESC;

Les autres panneaux se déclinent sur le même modèle : filtrer sur statut, grouper par type_communication, ou série temporelle sur timestamp_comm (panneau Time series).

✅ Validation Étape 2 - Avant de continuer

Vérifiez que vous avez bien :

• Dashboard “Communications Satellite” créé dans Grafana
• Au moins 5 panels fonctionnels affichant des données réelles
• Titre du dashboard clair et descriptif
• Chaque panel a un titre explicite
• Les données s’affichent correctement (pas d’erreur “No data”)

Testez votre dashboard en rafraîchissant la page. Les données doivent s’afficher immédiatement.

Étape 3 : Dashboard RH

3. Créer un dashboard contenant au minimum :

Panneaux requis

• Panneau 1 : Effectifs actuels par statut
• Panneau 2 : Répartition du personnel par fonction
• Panneau 3 : Évolution des arrivées/départs

✅ Validation Étape 3 - Avant de continuer

Vérifiez que vous avez bien :

• Dashboard “Gestion RH Personnel” créé dans Grafana
• Au moins 3 panels fonctionnels affichant les données RH
• Les statuts du personnel sont clairement visibles
• Les graphiques temporels montrent l’évolution (arrivées/départs)
• Dashboard sauvegardé et accessible depuis l’accueil Grafana

Vous devriez maintenant avoir 2 dashboards complets : Communications et RH.

Étape 4 : Configuration avancée

4. Configuration des fonctionnalités avancées :

• Ajouter des seuils d’alerte avec codes couleurs
• Configurer le rafraîchissement automatique
• Définir des variables template pour filtrer les données

✅ Validation Finale Étape 4 - TP2 Terminé

Félicitations ! Vérifiez que vous avez bien complété :

• 2 dashboards fonctionnels (Communications + RH)
• Seuils d’alerte configurés avec couleurs (vert/orange/rouge)
• Rafraîchissement automatique activé (5min ou 10min)
• Au moins 1 variable template fonctionnelle
• Dashboards exportés au format JSON (sauvegarde)

Votre TP2 est complet. Vous pouvez maintenant répondre aux Questions de Validation ci-dessous et préparer votre livrable.

---

Questions de Validation

Les 10 questions suivantes valident votre compréhension et votre réalisation (difficulté croissante) :

Question 1 - Données de base (Facile)

“Combien de lignes contient la table communications_satellite au total ?”

Réponse attendue : Requête SQL COUNT(*) et résultat numérique.

Question 2 - Filtrage simple (Facile)

“Combien de communications ont eu un statut ‘Réussie’ dans les 7 derniers jours ?”

Réponse attendue : Requête SQL avec WHERE et conditions temporelles + résultat numérique.

Question 3 - Personnel actuel (Facile)

“Combien de personnes ont actuellement le statut ‘Présent’ sur la base ?”

Réponse attendue : Requête SQL simple sur personnel_base + nombre de personnes.

Question 4 - Calculs de moyenne (Moyen)

“Quelle est la qualité de signal moyenne de la station ‘Toulouse’ sur les 30 derniers jours ?”

Réponse attendue : Requête SQL avec AVG() et filtres + valeur avec 2 décimales + capture dashboard.

Question 5 - Analyse par type (Moyen)

“Quels sont les 3 types de communication qui génèrent le plus de volume de données (en MB) ?”

Réponse attendue : Requête SQL avec GROUP BY et ORDER BY + liste des 3 types + volumes correspondants.

Question 6 - Analyse temporelle (Moyen)

“À quelle heure de la journée observe-t-on le plus grand nombre de communications satellite ?”

Réponse attendue : Requête SQL avec EXTRACT(hour) + graphique Grafana + heure optimale justifiée.

Question 7 - Analyse personnel longue mission (Difficile)

“Identifiez le personnel dont la mission dépasse 4 mois et analysez la répartition par fonction. Quels sont les risques opérationnels pour les missions prolongées ?”

Réponse attendue : Requête SQL avec calcul de durée (CURRENT_DATE - date_arrivee) + répartition par fonction + analyse risques polaires détaillée (fatigue, isolement, rotation).

Question 8 - Analyse des équipements critiques (Difficile)

“Listez tous les équipements critiques en panne depuis plus de 48h. Pour chaque équipement, indiquez le type, la localisation et proposez une priorisation de maintenance.”

Réponse attendue : Requête SQL sur table equipements_critiques avec calcul de durée de panne + liste des équipements + priorisation justifiée + dashboard panel correspondant.

Question 9 - Dashboard d’alertes (Difficile)

“Créez un panel d’alertes critiques affichant : personnel mission > 90 jours, communications interrompues > 24h, équipements critiques en panne.”

Réponse attendue : Requête SQL complexe + configuration panel Grafana + capture écran fonctionnelle + explication seuils.

Question 10 - Optimisation opérationnelle (Expert)

“Analysez les patterns dans vos données et proposez 3 améliorations concrètes pour la supervision TAAF avec métriques de suivi.”

Réponse attendue : Analyse approfondie des données + 3 améliorations détaillées + métriques KPI proposées + impact opérationnel + faisabilité technique.

---

Critères d’Évaluation

Technique (60%)

Critère	Description	Points
Dashboards fonctionnels	Les panneaux affichent les bonnes données	20%
Requêtes SQL correctes	Syntaxe PostgreSQL respectée	15%
Interface utilisable	Lisibilité, organisation, couleurs	15%
Configuration avancée	Seuils, variables, rafraîchissement	10%

Analyse (40%)

Critère	Description	Points
Réponses aux questions	Exactitude des valeurs trouvées	15%
Justifications	Explication des méthodes utilisées	10%
Contexte TAAF	Compréhension des enjeux opérationnels polaires	10%
Suggestions d’amélioration	Propositions d’évolutions des dashboards	5%

---

Livrables Attendus

Livrables obligatoires

• Fichiers JSON des 2 dashboards exportés
• Document de réponses aux 10 questions avec justifications
• Captures d’écran des dashboards en fonctionnement
• Rapport technique (1 page) : difficultés rencontrées, solutions apportées

Format de remise

Consignes de rendu OBLIGATOIRES

Plateforme de rendu : Moodle de l’Université de la Réunion

Nommage STRICT : nom-prénom-promo.PDF
Exemple : dupont-marie-m2info.PDF

ATTENTION : Tout fichier ne respectant pas ce format de nommage entraînera une pénalité de -5 points sur la note finale.

Contenu du rendu :

• Format unique : Document PDF consolidé
• Pages maximum : 10 pages (annexes incluses)
• Structure obligatoire :
  1. Page de garde avec informations étudiant
  2. Réponses aux 10 questions de validation
  3. Captures d’écran des dashboards fonctionnels
  4. Rapport technique (difficultés et solutions)
  5. Fichiers JSON des dashboards (en annexe ou lien de téléchargement)

Critères de lisibilité :

• Police : Minimum 11pt, recommandé 12pt
• Marges : Minimum 2cm
• Images : Haute résolution, bien contrastées
• Structure : Titres et sous-titres clairs
• Orthographe : Relecture obligatoire

Lisibilité et compréhensibilité : Ces critères représentent 15% de la note finale. Un rapport illisible, mal structuré ou incompréhensible sera sanctionné même si le contenu technique est correct.

Date limite : À préciser par l’enseignant

---

Ressources et Conseils

Note sur Grafana Alloy (TP3 à venir)

Dans le TP3 - Intégration Loki, vous découvrirez Grafana Alloy, l’agent de collecte unifié qui remplace Grafana Agent.

Grafana Alloy sera utilisé pour :

• Collecter les logs des conteneurs Docker
• Envoyer les logs vers Loki pour l’analyse
• Corréler les logs avec les métriques Prometheus

Pour le moment (TP2), concentrez-vous sur les métriques et les données PostgreSQL. Les logs seront intégrés au prochain TP.

Ressources disponibles

• Documentation Grafana : Panels, queries, variables
• PostgreSQL : Fonctions d’agrégation, filtrage temporel
• Base de données : Structure et contenu des tables TAAF

Requêtes utiles pour débuter :

-- Communications des derniers jours
SELECT * FROM communications_satellite
WHERE timestamp_comm >= NOW() - INTERVAL '7 days';

-- Personnel actuel avec durée de mission
SELECT *, EXTRACT(DAY FROM (NOW() - date_arrivee)) as duree_jours
FROM personnel_base
WHERE statut IN ('Présent', 'Mission extérieure');

-- Statistiques par station
SELECT station_receptrice,
       COUNT(*) as total,
       AVG(qualite_signal) as signal_moyen
FROM communications_satellite
GROUP BY station_receptrice;

---

Conseils pratiques

Dashboard Communications

• Utilisez des couleurs distinctes pour chaque station (Toulouse/La Réunion)
• Configurez des seuils : signal > 70% = vert, < 40% = rouge
• Filtrez les données sur les 30 derniers jours pour la performance

Dashboard RH

• Alertes visuelles pour communications > 7 jours (rouge)
• Couleurs par statut : Présent = vert, Évacuation médicale = rouge
• Graphiques temporels pour visualiser les rotations saisonnières

Performance

• Limitez les requêtes lourdes avec LIMIT
• Utilisez des agrégations pour les gros volumes
• Configurez le cache Grafana approprié