#! /usr/bin/env python3 # -*- coding: utf-8 -*- # Imports import os import sys import time import datetime from calendar import monthrange import sqlite3 import numpy as np # Constantes STAT_DIR = '/srv/visio.chapril.org/statistiques/' STATS_TOT_FIELDS = ['total_conferences_created','total_failed_conferences','total_conferences_completed','total_conference_seconds','total_bytes_received','total_bytes_sent','conferences','videochannels','endpoints_sending_audio', 'total_conference', 'average conferences by day', 'total_conference_time', ] STATS_FR_TOT_FIELDS = ['conferences creees total','conferences totalement echouees','conferences terminees total','duree totale conferences','total octets reçus','total octets envoyés','nombre de conferences','canaux video','canaux audio', 'Conferences total', 'moyenne de conferences par jour', 'duree totale conferences', ] STATS_MAX_FIELDS = ['total_participants','largest_conference',] STATS_FR_MAX_FIELDS = ['plus grand nombre de connexions simultanées','plus grande conference',] dbPath = '/srv/visio.chapril.org/statistiques/stats_sqlite.db' dbName = 'jitsi_stats' # Classes class Stats: def __init__(self,year,mois): self.db = SQLite() self.year = year self.mois = mois self.files = os.listdir(STAT_DIR) self.startDate = self.EndDate = None self.consolided = {} self.consolided_datas = {} self.__initBounds() def __initBounds(self): self.__setStartDate(f'{self.year}-{self.mois}-01 00:00:00') maxDays = monthrange(self.year,self.mois)[1] self.__setEndDate(f'{self.year}-{self.mois}-{maxDays} 23:59:59') def __setStartDate(self,thisDate): self.startDate = self.__date2timestamp(thisDate) def getStartDate(self): if self.startDate is not None: return self.startDate else: return 'undefiined' def getEndDate(self): if self.endDate is not None: return self.endDate else: return 'undefiined' def __setEndDate(self,thisDate): self.endDate = self.__date2timestamp(thisDate) def __date2timestamp(self,thisDate): timestamp = time.mktime(time.strptime(thisDate, '%Y-%m-%d %H:%M:%S')) return int(timestamp) def __conv(self,octets,dataType='b'): if dataType == 'b': unit = 'octets' if int(octets) > 1024: octets = int(octets) / 1024 unit = 'ko' if int(octets) > 1024: octets = int(octets) / 1024 unit = 'Mo' if int(octets) > 1024: octets = int(octets) / 1024 unit = 'Go' elif dataType == 't': unit = 's' if int(octets) > 60: unit = 'min' octets = octets / 60 if int(octets) > 60: unit = 'h' octets = octets / 60 octets = int(octets * 10) / 10 return octets,unit def parse(self): tot_part_start = tot_part_end = 0 try: tot_part_start = self.db.dbQuery(f"""SELECT value_field FROM {dbName} WHERE timestamp = {self.startDate} AND key_field = 'total_participants'""")[-1][0] except IndexError: tot_part_start = 0 tot_part_end = self.db.dbQuery(f"""SELECT value_field FROM {dbName} WHERE timestamp > {self.startDate} AND timestamp < {self.endDate} AND key_field = 'total_participants'""") max_tot = [] for mx in tot_part_end: max_tot.append(int(mx[0])) tot_part_end = np.max(max_tot) tot_part = int(tot_part_end) - int(tot_part_start) res = self.db.dbQuery(f"""SELECT * FROM {dbName} WHERE timestamp > {self.startDate} AND timestamp < {self.endDate} ORDER by id""") consolided = {} moy_conf_by_day = 0 for line in res: field = line[2] if field in STATS_TOT_FIELDS: if field in consolided: if 'total' not in field: consolided[field] = consolided[field] + int(line[3]) else: if consolided[field] < int(line[3]): consolided[field] = int(line[3]) else: consolided[field] = int(line[3]) if field == 'conferences': moy_conf_by_day += 1 if field in STATS_MAX_FIELDS: if field in consolided: if consolided[field] < int(line[3]): consolided[field] = int(line[3]) else: consolided[field] = int(line[3]) consolided['total_participants'] = tot_part temp_keys = list(consolided.keys()) for k in temp_keys: if 'bytes' in k: (v,u) = self.__conv(consolided[k]) consolided[k] = f"{v} {u}" if 'seconds' in k: (v,u) = self.__conv(consolided[k],dataType='t') consolided.pop(k) n_k = k.replace('_seconds','_time') consolided[n_k] = f"{v} {u}" if moy_conf_by_day > 1: tot = consolided['conferences'] moy_conf_by_day = int(moy_conf_by_day / 12 + 0.5) moy = int(tot/moy_conf_by_day + 0.5) consolided.pop('conferences') consolided['average conferences by day'] = moy return consolided class SQLite: def __init__(self): if not os.path.isfile(dbPath): self.__initDb() def __initDb(self): self.__openDb() self.cursor.execute('''create table jitsi_stats( id integer primary key autoincrement, timestamp text, key_field text, value_field text )''') self.conn.commit() self.__closeDb() def __openDb(self): self.conn = sqlite3.connect(dbPath) self.cursor = self.conn.cursor() def __closeDb(self): self.cursor.close() self.conn.close() def dbQuery(self,query='SELECT'): rows = None while rows is None: try: self.__openDb() self.cursor.execute(query) rows = self.cursor.fetchall() self.__closeDb() except sqlite3.OperationalError: rows = None time.sleep(30) return rows def dbInsert(self,ts,k,v): self.__openDb() self.cursor.execute(f"""INSERT INTO jististats (timestamp,key_field,value_field) VALUES ('{ts}','{k}','{v}')""") self.conn.commit() self.__closeDb() # Fonctions # Principal def runMain(): if len(sys.argv) <= 1: print('Argument manquant: mois') sys.exit(1) try: mois = int(sys.argv[1]) except ValueError: print('Le mois doit etre un nombre compris entre 1 et 12 !') sys.exit(1) if mois < 1 or mois > 12: print('Le mois doit etre un nombre compris entre 1 et 12 !') sys.exit(1) currentDate = datetime.date.today() if len(sys.argv) >= 3: year = int(sys.argv[2]) else: if currentDate.month == 1 and mois == 12: year = currentDate.year - 1 else: year = currentDate.year stats = Stats(year,mois) res = stats.parse() for (k,v) in res.items(): if k in STATS_TOT_FIELDS: i = STATS_TOT_FIELDS.index(k) fr = STATS_FR_TOT_FIELDS[i] print(f"{fr}={v}") elif k in STATS_MAX_FIELDS: i = STATS_MAX_FIELDS.index(k) fr = STATS_FR_MAX_FIELDS[i] print(f"{fr}={v}") else: pass try: moyenne_part = int(res['total_participants']) / int(res['total_conferences_completed']) moyenne_part = int((moyenne_part + 0.5) * 10) / 10 print(f"Moyenne participants par conference={moyenne_part}") except: pass if 'total_conference_time' in res: moyenne_duree = float(res['total_conference_time'].split()[0]) * 60 / int(res['total_conferences_completed']) moyenne_duree = int((moyenne_duree + 0.5) * 10) / 10 else: moyenne_duree = 0 print(f"duree moyenne des conferences={moyenne_duree} min") if __name__ == '__main__': runMain() # Fin du programme