#73 — bandwidth-trending
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 revendeurSché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 clientsConfiguration
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_teamEndpoints 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.bandwidthdansapp/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