#73 — bandwidth-trending | RGZ NOC

PLANIFIÉ

Priorité: 🟠 HAUTE · Type: C (Celery) · Conteneur: rgz-beat · Code: app/tasks/trending.pyDépendances: #37 dashboards-grafana, #4 rgz-db

Description

Analyse des tendances bande passante sur périodes 7 jours et 30 jours. Collecte les données brutes de NetFlow (#42) et SNMP polling (#39) → agrégation dans TimescaleDB → calcul tendances → prévision linéaire de saturation.

Délivre à chaque revendeur une projection de quand la bande passante atteindra 80% de la capacité contractuelle, permettant :

Planification: Upgrade préventif avant saturation
Alertes: Si tendance montre saturation < 30 jours → P2 alert NOC
Facturation: Data pour justifier surcoûts si dépassement volumétrique

Généré quotidiennement, intégré au dashboard revendeur (#51).

Architecture Interne

Flux de Génération

Quotidiennement 06:00 UTC:
  ↓
Celery Beat déclenche rgz.trending.bandwidth
  ↓
Pour chaque revendeur actif:
  1. Requête TimescaleDB:
     SELECT SUM(bytes_in + bytes_out) as volume
     FROM netflow_records
     WHERE reseller_id = {id} AND timestamp >= NOW() - 7 days
     GROUP BY DATE_TRUNC('day', timestamp)
     ORDER BY timestamp
  2. Calcul metrics:
     - Moyenne 7j en Mbps
     - Pic 7j en Mbps
     - Tendance 7j (linear regression)
     - Moyenne 30j en Mbps
     - Pic 30j en Mbps
     - Tendance 30j (linear regression)
  3. Prévision saturation:
     - Capacité contractuelle (de reseller.contracted_bandwidth_mbps)
     - Seuil alerte: 80% capacité
     - Si (avg + trend_slope * 30days) > 0.8 * capacity
       → saturation_date = calculate_intercept()
       → SI saturation_date < NOW() + 30j → créer P2 alert
  ↓
Enregistrer dans DB + Redis cache (TTL 24h)
  ↓
Publier event pour dashboard revendeur

Schéma de Données

sql

-- Tables source
TABLE netflow_records (créée par #42):
  id UUID PK
  reseller_id UUID FK
  bytes_in BIGINT
  bytes_out BIGINT
  timestamp TIMESTAMP

TABLE resellers:
  id UUID PK
  name TEXT
  contracted_bandwidth_mbps INT (ex: 100)

-- Table de tendance (créée par #73)
TABLE bandwidth_trending:
  id UUID PK
  reseller_id UUID FK
  timestamp DATE
  period CHECK(7d|30d)
  avg_mbps DECIMAL(10,2)
  peak_mbps DECIMAL(10,2)
  trend_slope DECIMAL(10,4) (linear regression slope, Mbps per day)
  projected_peak_30d DECIMAL(10,2) (avg + slope*30)
  utilization_percent DECIMAL(5,2) (avg / capacity * 100)
  saturation_date DATE (NULL if no saturation forecast)
  saturation_days_remaining INT (NULL if > 30d or no saturation)
  created_at TIMESTAMP

-- Alertes saturation (créée par #73, lue par #58)
TABLE bandwidth_alerts:
  id UUID PK
  reseller_id UUID FK
  alert_type CHECK(approaching_saturation|exceeded_capacity)
  threshold_percent INT (ex: 80)
  current_utilization DECIMAL(5,2)
  projected_date DATE
  created_at TIMESTAMP
  acknowledged_at TIMESTAMP
  acknowledged_by UUID FK (manager)

Exemples Métriques

REVENDEUR: Tech Connect (100 Mbps contract)

PÉRIODE 7j:
  Données brutes: Day1=12.3GB, Day2=14.5GB, ..., Day7=18.2GB
  Moyenne:        15.2 Mbps
  Pic:            22.5 Mbps
  Tendance:       +0.8 Mbps/day (pente)
  Utilisation:    15.2 / 100 = 15.2% ✓

PÉRIODE 30d:
  Moyenne:        18.4 Mbps
  Pic:            28.1 Mbps
  Tendance:       +1.2 Mbps/day (pente plus élevée)
  Utilisation:    18.4 / 100 = 18.4% ✓

PRÉVISION:
  Proj. 30d:      18.4 + (1.2 * 30) = 54.4 Mbps
  À 80% seuil:    100 * 0.8 = 80 Mbps
  Saturation:     À environ 51 jours (> 30j, OK)
  Alerte:         Aucune (saturation > 30j)

─────────────────────────────────────────

REVENDEUR: Digital Benin (50 Mbps contract)

PÉRIODE 30d:
  Moyenne:        38.2 Mbps
  Tendance:       +1.5 Mbps/day (croissance forte)
  Projection:     38.2 + (1.5 * 30) = 83.2 Mbps > 80% seuil ⚠️

PRÉVISION:
  À 80% seuil (40 Mbps):  (40 - 38.2) / 1.5 ≈ 1.2 jours
  Saturation:    2026-02-23 (dans 2 jours!) 🚨

ALERTE:
  P2 créée: "Digital Benin approaching saturation"
  NOC notifié
  Revendeur notifié (email + SMS)
  Recommandation: Upgrade contrat ou limiter clients

Configuration

env

# Bandwidth Trending
BANDWIDTH_TRENDING_PERIOD_DAYS_SHORT=7
BANDWIDTH_TRENDING_PERIOD_DAYS_LONG=30
BANDWIDTH_SATURATION_THRESHOLD_PERCENT=80
BANDWIDTH_ALERT_LEAD_TIME_DAYS=30  # Alerte si saturation < 30j
BANDWIDTH_TREND_METHOD=linear_regression  # ou exponential

# NetFlow/SNMP sources
NETFLOW_POLLING_INTERVAL_MIN=5
SNMP_POLLING_INTERVAL_MIN=5

# Prévention false positives
BANDWIDTH_MIN_DATA_POINTS=28  # Besoin 28+ jours pour tendance 30j
BANDWIDTH_TREND_CONFIDENCE_MIN=0.8  # R² >= 0.8 pour projection

# Alertes
BANDWIDTH_ALERT_CHANNELS=email,slack,sms  # Où notifier
BANDWIDTH_ALERT_RECIPIENT_ROLE=reseller_admin,noc_team

Endpoints API

Méthode	Route	Description	Réponse
GET	`/api/v1/reports/bandwidth?reseller_id={uuid}&period=7d`	Tendance 7j
GET	`/api/v1/reports/bandwidth?reseller_id={uuid}&period=30d`	Tendance 30j	Same +
GET	`/api/v1/resellers/{reseller_id}/bandwidth/forecast`	Prévision compète
GET	`/api/v1/reports/bandwidth/alerts?priority=P2`	Alertes saturation	List[{reseller, threshold, current, proj_date}]

Authentification: Revendeur voit ses données, NOC voit tous, Admin full access

Celery Task

Champ	Valeur
Task name	`rgz.trending.bandwidth`
Schedule	Daily 06:00 UTC
Queue	`rgz.reports`
Timeout	600s
Retry	2x

Logique esquisse:

python

@app.task(name='rgz.trending.bandwidth', bind=True)
def analyze_bandwidth_trending(self):
    """
    Analyse tendances bande passante tous revendeurs
    """
    resellers = db.query(Reseller).filter(
        Reseller.status == ResellerStatus.active
    ).all()

    results = []

    for reseller in resellers:
        try:
            # 1. Récupérer NetFlow 7j et 30j
            data_7d = _fetch_netflow_period(reseller.id, days=7)
            data_30d = _fetch_netflow_period(reseller.id, days=30)

            if len(data_7d) < 7:  # Pas assez de données
                logger.warn(f"Insufficient data for {reseller.id} (7d)")
                continue

            # 2. Calculer metrics
            metrics_7d = _calculate_metrics(data_7d, period='7d')
            metrics_30d = _calculate_metrics(data_30d, period='30d')

            # 3. Prévision saturation
            capacity_mbps = reseller.contracted_bandwidth_mbps
            threshold_mbps = capacity_mbps * 0.8

            saturation_forecast = _project_saturation(
                metrics_30d,
                threshold_mbps,
                days_ahead=30
            )

            # 4. Créer alerte si nécessaire
            if (saturation_forecast['will_saturate'] and
                saturation_forecast['days_remaining'] < 30):
                alert = BandwidthAlert(
                    reseller_id=reseller.id,
                    alert_type='approaching_saturation',
                    threshold_percent=80,
                    current_utilization=metrics_30d['utilization_percent'],
                    projected_date=saturation_forecast['saturation_date']
                )
                db.add(alert)

                # Notifier revendeur
                send_alert.delay(
                    reseller_id=reseller.id,
                    channel='email',
                    template='bandwidth_saturation_warning',
                    context={'forecast': saturation_forecast}
                )

            # 5. Enregistrer trending
            trending_7d = BandwidthTrending(
                reseller_id=reseller.id,
                timestamp=datetime.utcnow().date(),
                period='7d',
                **metrics_7d
            )
            trending_30d = BandwidthTrending(
                reseller_id=reseller.id,
                timestamp=datetime.utcnow().date(),
                period='30d',
                **metrics_30d,
                saturation_date=saturation_forecast.get('saturation_date'),
                saturation_days_remaining=saturation_forecast.get('days_remaining')
            )
            db.add(trending_7d)
            db.add(trending_30d)

            results.append({
                'reseller_id': reseller.id,
                'status': 'success'
            })

        except Exception as e:
            logger.error(f"Trending failed {reseller.id}: {e}")
            results.append({
                'reseller_id': reseller.id,
                'status': 'failed'
            })

    db.commit()
    logger.info(f"Bandwidth trending: {len(results)} resellers processed")
    return {'status': 'completed', 'results': results}

def _project_saturation(metrics: dict, threshold_mbps: float, days_ahead: int) -> dict:
    """
    Linear regression pour prédire saturation
    """
    current_avg = metrics['avg_mbps']
    trend_slope = metrics['trend_slope']  # Mbps per day

    projected_avg_days_ahead = current_avg + (trend_slope * days_ahead)

    if projected_avg_days_ahead <= threshold_mbps:
        return {
            'will_saturate': False,
            'saturation_date': None,
            'days_remaining': None
        }

    # Résoudre: current_avg + slope*days = threshold
    days_to_saturation = (threshold_mbps - current_avg) / trend_slope
    saturation_date = datetime.utcnow().date() + timedelta(days=days_to_saturation)

    return {
        'will_saturate': True,
        'saturation_date': saturation_date,
        'days_remaining': int(days_to_saturation),
        'projected_at_saturation_date': threshold_mbps
    }

Commandes Utiles

bash

# Déclencher trending manuellement
docker-compose exec rgz-api celery -A app.celery_app call rgz.trending.bandwidth

# Récupérer tendance 30j pour revendeur
curl -H "Authorization: Bearer {token}" \
  "http://api-rgz.duckdns.org/api/v1/reports/bandwidth?reseller_id={uuid}&period=30d" | jq

# Lister alertes saturation actives
curl -H "Authorization: Bearer {admin_token}" \
  "http://api-rgz.duckdns.org/api/v1/reports/bandwidth/alerts?status=active" | jq

# Télécharger rapport tendance CSV (export pour BI)
curl -H "Authorization: Bearer {admin_token}" \
  "http://api-rgz.duckdns.org/api/v1/reports/bandwidth/csv?period=30d" \
  -o bandwidth_trending.csv

# Logs trending
docker-compose logs rgz-beat | grep "rgz.trending.bandwidth"

# Vérifier NetFlow data disponibilité (debug)
docker-compose exec rgz-db psql -U $POSTGRES_USER -d $POSTGRES_DB -c \
  "SELECT reseller_id, COUNT(*), MAX(timestamp) FROM netflow_records GROUP BY reseller_id LIMIT 10;"

Implémentation TODO

[ ] Schéma DB bandwidth_trending + bandwidth_alerts
[ ] Tâche Celery rgz.trending.bandwidth dans app/tasks/trending.py
[ ] Fonction _fetch_netflow_period() (requête TimescaleDB)
[ ] Fonction _calculate_metrics() (avg, peak, trend slope)
[ ] Fonction _project_saturation() (linear regression)
[ ] Endpoints API GET /api/v1/reports/bandwidth*
[ ] Intégration NetFlow (#42) source data
[ ] Alertes P2 revendeur (email/SMS)
[ ] Tests: données synthétiques (7j croissance, prévision saturation)
[ ] Dashboard: graphe tendance 7d/30d avec projection
[ ] Documentation: formule linear regression, interprétation pente

Dernière mise à jour: 2026-02-21

#73 — bandwidth-trending ​

Description ​

Architecture Interne ​

Flux de Génération ​

Schéma de Données ​

Exemples Métriques ​

Configuration ​

Endpoints API ​

Celery Task ​

Commandes Utiles ​

Implémentation TODO ​

#73 — bandwidth-trending