haproxy-wi/app/modules/db/metric.py

394 lines
11 KiB
Python

from typing import Literal
from datetime import datetime, timedelta
from peewee import fn
from app.modules.db.db_model import mysql_enable, Metrics, MetricsHttpStatus, Server, NginxMetrics, ApacheMetrics, WafMetrics
from app.modules.db.common import out_error
import app.modules.roxy_wi_tools as roxy_wi_tools
MODELS = {
'haproxy': Metrics,
'nginx': NginxMetrics,
'apache': ApacheMetrics,
'http': MetricsHttpStatus,
'waf': WafMetrics
}
def insert_service_metrics(service: Literal['haproxy', 'nginx', 'apache', 'waf', 'http'], **kwargs):
get_date = roxy_wi_tools.GetDate()
cur_date = get_date.return_date('regular')
kwargs['date'] = cur_date
model = MODELS[service]
try:
model.insert(**kwargs).execute()
except Exception as e:
out_error(e)
def delete_service_metrics(service: Literal['haproxy', 'http', 'waf', 'nginx', 'apache']) -> None:
get_date = roxy_wi_tools.GetDate()
cur_date = get_date.return_date('regular', timedelta_minus=3)
model = MODELS[service]
try:
model.delete().where(model.date < cur_date).execute()
except Exception as e:
out_error(e)
def select_metrics(serv, service, **kwargs):
try:
# Map service to the appropriate model
if service in ('nginx', 'apache', 'waf'):
if service == 'nginx':
model = NginxMetrics
elif service == 'apache':
model = ApacheMetrics
else: # waf
model = WafMetrics
elif service == 'http_metrics':
model = MetricsHttpStatus
else:
model = Metrics
# Get time range from kwargs
time_range = kwargs.get('time_range', '30') # Default to 30 minutes if not specified
# Create a base query
query = model.select().where(model.serv == serv)
# Add time-based filtering
now = datetime.utcnow()
if time_range == '1':
# Last 1 minute
query = query.where(model.date >= now - timedelta(minutes=1))
elif time_range == '60':
# Last 60 minutes
query = query.where(model.date >= now - timedelta(minutes=60))
elif time_range == '180':
# Last 180 minutes
query = query.where(model.date >= now - timedelta(minutes=180))
elif time_range == '360':
# Last 360 minutes
query = query.where(model.date >= now - timedelta(minutes=360))
elif time_range == '720':
# Last 720 minutes
query = query.where(model.date >= now - timedelta(minutes=720))
else:
# Default: last 30 minutes
query = query.where(model.date >= now - timedelta(minutes=30))
# Order by date
query = query.order_by(model.date.asc())
# For longer time ranges, we can sample the data to reduce the number of points
# This is similar to the original SQL's "group by `date` div X" or "rowid % X = 0"
if mysql_enable == '1' and time_range not in ('1', '30'):
# For MySQL, we can use the SQL function to sample data
from peewee import fn
sampling_rates = {
'60': 100,
'180': 200,
'360': 300,
'720': 500
}
if time_range in sampling_rates:
# Group by date div X to reduce data points
query = model.select().where(
(model.serv == serv) &
(model.date >= now - timedelta(minutes=int(time_range)))
).group_by(fn.DIV(model.date, sampling_rates[time_range])).order_by(model.date.asc())
elif not mysql_enable == '1' and time_range not in ('1', '30'):
# For SQLite, we need to fetch all data and then sample it in Python
# This is less efficient but maintains compatibility
results = list(query.dicts())
sampling_rates = {
'60': 2,
'180': 5,
'360': 7,
'720': 9
}
if time_range in sampling_rates:
# Sample data by taking every Nth row
return [results[i] for i in range(len(results)) if i % sampling_rates[time_range] == 0]
# Execute query and return results as dictionaries
return list(query.dicts())
except Exception as e:
out_error(e)
return []
def select_servers_metrics_for_master(group_id: int):
query = Server.select(Server.ip).where(
((Server.haproxy_metrics == 1) | (Server.nginx_metrics == 1) | (Server.apache_metrics == 1))
& (Server.group_id == group_id)
)
try:
return query.execute()
except Exception as e:
out_error(e)
def select_metrics_enabled(service: Literal['haproxy', 'nginx', 'apache']):
query_where = {
'haproxy': ((Server.haproxy_metrics == 1) & (Server.haproxy == 1)),
'nginx': ((Server.nginx_metrics == 1) & (Server.nginx == 1)),
'apache': ((Server.apache_metrics == 1) & (Server.apache == 1)),
}
try:
return Server.select(Server.ip).where(query_where[service] & (Server.enabled == 1)).execute()
except Exception as e:
out_error(e)
def select_table_metrics(group_id):
try:
# Get current time
now = datetime.now()
# Define time ranges
one_hour_ago = now - timedelta(hours=1)
one_day_ago = now - timedelta(hours=24)
three_days_ago = now - timedelta(days=3)
# Get servers with haproxy metrics enabled
server_query = Server.select(Server.ip, Server.hostname).where(
(Server.haproxy_metrics == 1) &
(Server.enabled == 1)
)
# Apply group filter if not admin group
if group_id != 1:
server_query = server_query.where(Server.group_id == group_id)
# Get list of server IPs
servers = list(server_query.execute())
if not servers:
return []
server_ips = [server.ip for server in servers]
# Create a dictionary to store results
results = {}
for server in servers:
results[server.ip] = {
'ip': server.ip,
'hostname': server.hostname,
'avg_sess_1h': 0,
'avg_sess_24h': 0,
'avg_sess_3d': 0,
'max_sess_1h': 0,
'max_sess_24h': 0,
'max_sess_3d': 0,
'avg_cur_1h': 0,
'avg_cur_24h': 0,
'avg_cur_3d': 0,
'max_con_1h': 0,
'max_con_24h': 0,
'max_con_3d': 0
}
# Calculate metrics for each time range
# 1 hour metrics
one_hour_metrics = (Metrics
.select(
Metrics.serv,
fn.ROUND(fn.AVG(Metrics.sess_rate), 1).alias('avg_sess'),
fn.MAX(Metrics.sess_rate).alias('max_sess'),
fn.ROUND(fn.AVG(Metrics.curr_con + Metrics.cur_ssl_con), 1).alias('avg_cur'),
fn.MAX(Metrics.curr_con).alias('max_con')
)
.where(
(Metrics.serv.in_(server_ips)) &
(Metrics.date >= one_hour_ago) &
(Metrics.date <= now)
)
.group_by(Metrics.serv)
)
for metric in one_hour_metrics:
if metric.serv in results:
results[metric.serv]['avg_sess_1h'] = metric.avg_sess or 0
results[metric.serv]['max_sess_1h'] = metric.max_sess or 0
results[metric.serv]['avg_cur_1h'] = metric.avg_cur or 0
results[metric.serv]['max_con_1h'] = metric.max_con or 0
# 24 hour metrics
day_metrics = (Metrics
.select(
Metrics.serv,
fn.ROUND(fn.AVG(Metrics.sess_rate), 1).alias('avg_sess'),
fn.MAX(Metrics.sess_rate).alias('max_sess'),
fn.ROUND(fn.AVG(Metrics.curr_con + Metrics.cur_ssl_con), 1).alias('avg_cur'),
fn.MAX(Metrics.curr_con).alias('max_con')
)
.where(
(Metrics.serv.in_(server_ips)) &
(Metrics.date >= one_day_ago) &
(Metrics.date <= now)
)
.group_by(Metrics.serv)
)
for metric in day_metrics:
if metric.serv in results:
results[metric.serv]['avg_sess_24h'] = metric.avg_sess or 0
results[metric.serv]['max_sess_24h'] = metric.max_sess or 0
results[metric.serv]['avg_cur_24h'] = metric.avg_cur or 0
results[metric.serv]['max_con_24h'] = metric.max_con or 0
# 3 day metrics
three_day_metrics = (Metrics
.select(
Metrics.serv,
fn.ROUND(fn.AVG(Metrics.sess_rate), 1).alias('avg_sess'),
fn.MAX(Metrics.sess_rate).alias('max_sess'),
fn.ROUND(fn.AVG(Metrics.curr_con + Metrics.cur_ssl_con), 1).alias('avg_cur'),
fn.MAX(Metrics.curr_con).alias('max_con')
)
.where(
(Metrics.serv.in_(server_ips)) &
(Metrics.date >= three_days_ago) &
(Metrics.date <= now)
)
.group_by(Metrics.serv)
)
for metric in three_day_metrics:
if metric.serv in results:
results[metric.serv]['avg_sess_3d'] = metric.avg_sess or 0
results[metric.serv]['max_sess_3d'] = metric.max_sess or 0
results[metric.serv]['avg_cur_3d'] = metric.avg_cur or 0
results[metric.serv]['max_con_3d'] = metric.max_con or 0
# Convert dictionary to list of dictionaries
return list(results.values())
except Exception as e:
out_error(e)
return []
def select_service_table_metrics(service: str, group_id: int):
try:
# Map service to the appropriate model
if service == 'nginx':
model = NginxMetrics
metrics_field = Server.nginx_metrics
elif service == 'apache':
model = ApacheMetrics
metrics_field = Server.apache_metrics
else:
return [] # Unsupported service
# Get current time
now = datetime.now()
# Define time ranges
one_hour_ago = now - timedelta(hours=1)
one_day_ago = now - timedelta(hours=24)
three_days_ago = now - timedelta(days=3)
# Get servers with service metrics enabled
server_query = Server.select(Server.ip, Server.hostname).where(
(metrics_field == 1) &
(Server.enabled == 1)
)
# Apply group filter if not admin group
if group_id != 1:
server_query = server_query.where(Server.group_id == group_id)
# Get list of server IPs
servers = list(server_query.execute())
if not servers:
return []
server_ips = [server.ip for server in servers]
# Create a dictionary to store results
results = {}
for server in servers:
results[server.ip] = {
'ip': server.ip,
'hostname': server.hostname,
'avg_cur_1h': 0,
'avg_cur_24h': 0,
'avg_cur_3d': 0,
'max_con_1h': 0,
'max_con_24h': 0,
'max_con_3d': 0
}
# Calculate metrics for each time range
# 1 hour metrics
one_hour_metrics = (model
.select(
model.serv,
fn.ROUND(fn.AVG(model.conn), 1).alias('avg_cur'),
fn.MAX(model.conn).alias('max_con')
)
.where(
(model.serv.in_(server_ips)) &
(model.date >= one_hour_ago) &
(model.date <= now)
)
.group_by(model.serv)
)
for metric in one_hour_metrics:
if metric.serv in results:
results[metric.serv]['avg_cur_1h'] = metric.avg_cur or 0
results[metric.serv]['max_con_1h'] = metric.max_con or 0
# 24 hour metrics
day_metrics = (model
.select(
model.serv,
fn.ROUND(fn.AVG(model.conn), 1).alias('avg_cur'),
fn.MAX(model.conn).alias('max_con')
)
.where(
(model.serv.in_(server_ips)) &
(model.date >= one_day_ago) &
(model.date <= now)
)
.group_by(model.serv)
)
for metric in day_metrics:
if metric.serv in results:
results[metric.serv]['avg_cur_24h'] = metric.avg_cur or 0
results[metric.serv]['max_con_24h'] = metric.max_con or 0
# 3 day metrics
three_day_metrics = (model
.select(
model.serv,
fn.ROUND(fn.AVG(model.conn), 1).alias('avg_cur'),
fn.MAX(model.conn).alias('max_con')
)
.where(
(model.serv.in_(server_ips)) &
(model.date >= three_days_ago) &
(model.date <= now)
)
.group_by(model.serv)
)
for metric in three_day_metrics:
if metric.serv in results:
results[metric.serv]['avg_cur_3d'] = metric.avg_cur or 0
results[metric.serv]['max_con_3d'] = metric.max_con or 0
# Convert dictionary to list of dictionaries
return list(results.values())
except Exception as e:
out_error(e)
return []