Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/djproject/urls.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/djproject/urls.py (revision 2704) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/djproject/urls.py (working copy) @@ -12,6 +12,8 @@ from hive.composer import KibanaProxyView, elastic_proxy, reporting_downloading_handler, reporting_logo_handler, composer_config, composer_status, composer_query, composer_proxy from hive.storage import storage_mangement from hive.log_location import handle_log_location_app +from hive.device_metrics import handle_device_metrics_req +from hive.slb_stats import handle_slb_stats_req from hive.llb_stats import handle_llb_stats_req from hive.report.generate_report import handle_report_generation from hive.controller.backup_controller import handle_backup_req @@ -61,6 +63,8 @@ re_path(r'^composer_config/(?P\w+)', composer_config), re_path(r'^composer_status/(?P\w+)', composer_status), re_path(r'^composer_query$', composer_query), + re_path(r'^apv/slb/(?P.*)$', handle_slb_stats_req), + re_path(r'^device_metrics(?:/(?P.*))?$', handle_device_metrics_req), re_path(r'^llb/(?P.*)$', handle_llb_stats_req), re_path(r'^report/(?P.*)$', handle_report_generation), re_path(r'^log/(?P\w+)$', handle_log_location_app), Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/db_client.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/db_client.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/db_client.py (working copy) @@ -0,0 +1,70 @@ +import psycopg2 +import json +import time + +from hive.custom_exceptions import generic_exception as ge + + +class DBClient: + + def __init__(self): + pass + + @staticmethod + def execute_query(req_dict): + """ + Executes a Timescale/Postgres query and returns a structured JSON response. + req_dict = { + "query": "", + "params": [] + } + """ + conn = None + cur = None + try: + conn = psycopg2.connect( + dbname="amp_ts", + user="amp_ts_user", + password="Array@123$", + host="localhost", + port=5432 + ) + cur = conn.cursor() + + # Execute query (with params if provided) + if "params" in req_dict: + cur.execute(req_dict["query"], req_dict["params"]) + else: + cur.execute(req_dict["query"]) + + rows = cur.fetchall() + colnames = [desc[0] for desc in cur.description] + result = [dict(zip(colnames, row)) for row in rows] + + return { + "status": 200, + "message": "success", + "data": result + } + + except ge.GenericError as e: + raise e + except psycopg2.Error as e: + return { + "status": 500, + "message": "Database Error", + "data": str(e) + } + + except Exception as e: + return { + "status": 500, + "message": "Internal Error", + "data": str(e) + } + + finally: + if cur: + cur.close() + if conn: + conn.close() Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/device_metric_queries.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/device_metric_queries.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/device_metric_queries.py (working copy) @@ -0,0 +1,279 @@ +from hive.db.utils import Utils + +from hive.db.db_client import DBClient +from hive.utils import andebug + + +class DeviceMetrics: + + @staticmethod + def get_device_stats(time_from, time_to): + """ + Fetch latest CPU, MEM, NET_MEM, Connections per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + agent_host, + (last(cpu_usage, time)) AS cpu, + (last(mem_usage, time)) AS mem, + (last(net_mem_usage, time)) AS net_mem, + (last(connections, time)) AS connections + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY agent_host; + """ + + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_throughput_data(interval, time_from, time_to): + """ + Fetch throughput (received/sent in bps) per agent_host over time buckets + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + WITH bucketed AS ( + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + first(total_in, time)::double precision AS total_in, + first(total_out, time)::double precision AS total_out + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts, agent_host + ) + SELECT + ts, + agent_host, + COALESCE( + (total_in - lag(total_in) OVER (PARTITION BY agent_host ORDER BY ts)) + / NULLIF(EXTRACT(EPOCH FROM (ts - lag(ts) OVER (PARTITION BY agent_host ORDER BY ts))), 0) + * 8, + 0 + ) AS received, + COALESCE( + (total_out - lag(total_out) OVER (PARTITION BY agent_host ORDER BY ts)) + / NULLIF(EXTRACT(EPOCH FROM (ts - lag(ts) OVER (PARTITION BY agent_host ORDER BY ts))), 0) + * 8, + 0 + ) AS sent + FROM bucketed + ORDER BY ts; + """ + + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_cpu_usage_metrics(agent_host, interval, time_from, time_to): + """ + CPU mean over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + AVG(cpu_usage) AS cpu + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_mem_usage_metrics(agent_host, interval, time_from, time_to): + """ + Memory metrics over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + COALESCE(AVG(mem_usage), 0) AS memory_usage, + COALESCE(AVG(net_mem_usage), 0) AS network_mem_usage + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_disk_usage_metrics(agent_host, interval, time_from, time_to): + """ + Disk usage percentage over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + COALESCE(AVG(used) * 100.0 / NULLIF(AVG(size), 0), 0) AS disk_usage + FROM an_device_storage + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_network_throughput(agent_host, interval, time_from, time_to): + """ + Network throughput (bits/sec) over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + ts, + agent_host, + COALESCE( + GREATEST( + ((total_out - LAG(total_out) OVER (PARTITION BY agent_host ORDER BY ts)) * 8.0) + / NULLIF(EXTRACT(EPOCH FROM (ts - LAG(ts) OVER (PARTITION BY agent_host ORDER BY ts))), 0), + 0 + ), 0 + ) AS sent, + COALESCE( + GREATEST( + ((total_in - LAG(total_in) OVER (PARTITION BY agent_host ORDER BY ts)) * 8.0) + / NULLIF(EXTRACT(EPOCH FROM (ts - LAG(ts) OVER (PARTITION BY agent_host ORDER BY ts))), 0), + 0 + ), 0 + ) AS received + FROM ( + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + first(total_out, time) AS total_out, + first(total_in, time) AS total_in + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ) subq + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_ssl_connections(agent_host, interval, time_from, time_to): + """ + SSL connections (sum of total_openssl_conns) over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + SUM(total_openssl_conns) AS ssl_connection + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_connections(agent_host, interval, time_from, time_to): + """ + Average active connections over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + AVG(connections) AS connection + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_requests(agent_host, interval, time_from, time_to): + """ + Average active requests over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + AVG(requests) AS connection + FROM an_device_metrics + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) + + @staticmethod + def get_ssl_core_utilization(agent_host, interval, time_from, time_to): + """ + SSL AE and SE core utilization (mean) over time buckets per agent_host + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + agent_host, + AVG(ssl_ae_core_utilization) AS ssl_ae_core, + AVG(ssl_se_core_utilization) AS ssl_se_core + FROM an_device_performance + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND agent_host = '{agent_host}' + GROUP BY ts, agent_host + ORDER BY ts; + """ + req = {"query": query} + return DBClient.execute_query(req) Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/slb_stats_queries.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/slb_stats_queries.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/slb_stats_queries.py (working copy) @@ -0,0 +1,444 @@ +from hive.db.db_client import DBClient +from hive.custom_exceptions import generic_exception as ge +from hive.utils import andebug +from hive.db.utils import Utils + + +class SLBStats: + + def __init__(self): + pass + + @staticmethod + def get_virtual_service_stats(agent_host): + base_query = """ + WITH stats AS ( + SELECT + time, + agent_host, + serverid, + addr, + port, + protocol, + health_status, + ( + url_hits + hostname_hits + perstnt_cookie_hits + qos_cookie_hits + + default_hits + perstnt_url_hits + static_hits + qos_network_hits + + qos_url_hits + backup_hits + cache_hits + regex_hits + + rcookie_hits + icookie_hits + qos_client_port_hits + + qos_body_hits + header_hits + hash_url_hits + redirect_hits + ) AS total_hits, + conn_cnt, + in_byte_per_sec, + out_byte_per_sec + FROM apv_virtual_stats + WHERE time > now() - interval '20 seconds' + ) + SELECT + max(total_hits) - min(total_hits) AS hits_diff, + (array_agg(conn_cnt ORDER BY time DESC))[1] AS last_conn_cnt, + (array_agg(in_byte_per_sec ORDER BY time DESC))[1] * 8 AS last_in_bps, + (array_agg(out_byte_per_sec ORDER BY time DESC))[1] * 8 AS last_out_bps, + serverid, + agent_host, + addr, + port, + protocol, + health_status + FROM stats + """ + + params = [] + if agent_host: + base_query += " WHERE agent_host ~ %s" + params.append(agent_host) + + base_query += " GROUP BY serverid, agent_host, addr, port, protocol, health_status;" + + req_dict = { + "query": base_query, + "params": params + } + + return DBClient.execute_query(req_dict) + + # --- Convert "from" / "to" --- + + @staticmethod + def get_network_throughput(agent_host, virtual_server_id, interval, time_from=None, time_to=None): + + """ + Fetch network throughput (Sent, Received) from apv_virtual_stats + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + avg(in_byte_per_sec) AS received, + avg(out_byte_per_sec) AS sent + FROM apv_virtual_stats + WHERE agent_host ~ %s + AND serverid ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + req_dict = { + "query": query, + "params": [agent_host, virtual_server_id] + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_total_hits(agent_host, virtual_server_id, interval, time_from, time_to): + """ + Fetch total hits (difference over time) from apv_virtual_stats. + Uses MAX - MIN per time bucket to calculate increments, avoids NULLs. + """ + + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE( + MAX( + url_hits + hostname_hits + perstnt_cookie_hits + qos_cookie_hits + + default_hits + perstnt_url_hits + static_hits + qos_network_hits + + qos_url_hits + backup_hits + cache_hits + regex_hits + + rcookie_hits + icookie_hits + qos_client_port_hits + + qos_body_hits + header_hits + hash_url_hits + redirect_hits + ) - + MIN( + url_hits + hostname_hits + perstnt_cookie_hits + qos_cookie_hits + + default_hits + perstnt_url_hits + static_hits + qos_network_hits + + qos_url_hits + backup_hits + cache_hits + regex_hits + + rcookie_hits + icookie_hits + qos_client_port_hits + + qos_body_hits + header_hits + hash_url_hits + redirect_hits + ), + 0) AS hit_diff + FROM apv_virtual_stats + WHERE time >= {time_from_sql} + AND time <= {time_to_sql} + AND serverid ~ %s + AND agent_host ~ %s + GROUP BY ts + ORDER BY ts; + """ + + params = [virtual_server_id, agent_host] + + req_dict = { + "query": query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_hits_distribution(agent_host, virtual_server_id, time_from, time_to): + """ + Fetch Hits Distribution from apv_virtual_stats. + For each hit type, we compute: MAX(value) - MIN(value) in the given range. + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + (max(url_hits) - min(url_hits)) AS url_hits, + (max(hostname_hits) - min(hostname_hits)) AS hostname_hits, + (max(qos_cookie_hits) - min(qos_cookie_hits)) AS qos_cookie_hits, + (max(qos_network_hits) - min(qos_network_hits)) AS qos_network_hits, + (max(qos_url_hits) - min(qos_url_hits)) AS qos_url_hits, + (max(qos_client_port_hits) - min(qos_client_port_hits)) AS qos_client_port_hits, + (max(qos_body_hits) - min(qos_body_hits)) AS qos_body_hits, + (max(perstnt_cookie_hits) - min(perstnt_cookie_hits)) AS perstnt_cookie_hits, + (max(default_hits) - min(default_hits)) AS default_hits, + (max(perstnt_url_hits) - min(perstnt_url_hits)) AS perstnt_url_hits, + (max(static_hits) - min(static_hits)) AS static_hits, + (max(backup_hits) - min(backup_hits)) AS backup_hits, + (max(cache_hits) - min(cache_hits)) AS cache_hits, + (max(regex_hits) - min(regex_hits)) AS regex_hits, + (max(rcookie_hits) - min(rcookie_hits)) AS rcookie_hits, + (max(icookie_hits) - min(icookie_hits)) AS icookie_hits, + (max(header_hits) - min(header_hits)) AS header_hits, + (max(hash_url_hits) - min(hash_url_hits)) AS hash_url_hits, + (max(redirect_hits) - min(redirect_hits)) AS redirect_hits + FROM apv_virtual_stats + WHERE agent_host ~ %s + AND serverid ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql}; + """ + + params = [agent_host, virtual_server_id] + + req_dict = { + "query": query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_connection_count(agent_host, virtual_server_id, interval, time_from, time_to): + """ + Fetch connection count (average of ConnCnt over time buckets) from apv_virtual_stats + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE(SUM(conn_cnt), 0) AS connections + FROM apv_virtual_stats + WHERE agent_host ~ %s + AND serverid ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + params = [agent_host, virtual_server_id] + + req_dict = { + "query": query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_connections_per_sec(agent_host, virtual_server_id, interval, time_from, time_to): + """ + Fetch average connections per second (ConnPerSec) over time buckets from apv_virtual_stats + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + avg(conn_per_sec) AS connection + FROM apv_virtual_stats + WHERE agent_host ~ %s + AND serverid ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + params = [agent_host, virtual_server_id] + + req_dict = { + "query": query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_real_service_stats(agent_host): + """ + Fetch real service stats from apv_real_stats (TimescaleDB). + """ + + base_query = """ + WITH stats AS ( + SELECT + time, + agent_host, + real_server_id, + addr, + port, + protocol, + status, + rs_cnt_of_req, + rs_conn_per_sec, + rs_in_byte_per_sec, + rs_out_byte_per_sec + FROM apv_real_stats + WHERE time > now() - interval '20 seconds' + ) + SELECT + (array_agg(rs_cnt_of_req ORDER BY time DESC))[1] AS last_req_cnt, + (array_agg(rs_conn_per_sec ORDER BY time DESC))[1] AS last_conn_per_sec, + (array_agg(rs_in_byte_per_sec ORDER BY time DESC))[1] * 8 AS last_in_bps, + (array_agg(rs_out_byte_per_sec ORDER BY time DESC))[1] * 8 AS last_out_bps, + real_server_id, + agent_host, + addr, + port, + protocol, + status + FROM stats + """ + + params = [] + if agent_host: + base_query += " WHERE agent_host ~ %s" + params.append(agent_host) + + base_query += " GROUP BY real_server_id, agent_host, addr, port, protocol, status;" + + req_dict = { + "query": base_query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_real_network_throughput(agent_host, real_server_id, interval, time_from=None, time_to=None): + """ + Fetch network throughput (Sent, Received) from apv_real_stats + """ + + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE(avg(rs_in_byte_per_sec), 0) * 8 AS received, + COALESCE(avg(rs_out_byte_per_sec), 0) * 8 AS sent + FROM apv_real_stats + WHERE agent_host ~ %s + AND real_server_id ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + req_dict = { + "query": query, + "params": [agent_host, real_server_id] + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_outstanding_requests(agent_host, real_server_id, interval, time_from=None, time_to=None): + """ + Fetch Outstanding Requests (sum of rsCntOfReq) from apv_real_stats + """ + + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + sum(rs_cnt_of_req) AS request + FROM apv_real_stats + WHERE agent_host ~ %s + AND real_server_id ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + req_dict = { + "query": query, + "params": [agent_host, real_server_id] + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_real_stats_total_hits(agent_host, real_server_id, interval, time_from=None, time_to=None): + """ + Fetch Requests Number (Hits per interval) from apv_real_stats + """ + + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE(MAX(rs_total_hits) - MIN(rs_total_hits), 0) AS hits + FROM apv_real_stats + WHERE agent_host ~ %s + AND real_server_id ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + req_dict = { + "query": query, + "params": [agent_host, real_server_id] + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_real_connection_count(agent_host, real_server_id, interval, time_from, time_to): + """ + Fetch connection count (average of rs_conn_cnt over time buckets) from apv_real_stats + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE(MAX(rs_conn_cnt)::INT, 0) AS connections_per_sec + FROM apv_real_stats + WHERE agent_host ~ %s + AND real_server_id ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + params = [agent_host, real_server_id] + + req_dict = { + "query": query, + "params": params + } + + return DBClient.execute_query(req_dict) + + @staticmethod + def get_real_connections_per_sec(agent_host, real_server_id, interval, time_from=None, time_to=None): + """ + Fetch average connections per second (rs_conn_per_sec) from apv_real_stats + """ + time_from_sql = Utils.convert_time(time_from, "now() - interval '15 minutes'") + time_to_sql = Utils.convert_time(time_to, "now()") + + query = f""" + SELECT + time_bucket_gapfill('{interval}', time, {time_from_sql}, {time_to_sql}) AS ts, + COALESCE(AVG(rs_conn_per_sec), 0) AS connections + FROM apv_real_stats + WHERE agent_host ~ %s + AND real_server_id ~ %s + AND time >= {time_from_sql} + AND time <= {time_to_sql} + GROUP BY ts + ORDER BY ts; + """ + + req_dict = { + "query": query, + "params": [agent_host, real_server_id] + } + return DBClient.execute_query(req_dict) Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/utils.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/utils.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/utils.py (working copy) @@ -0,0 +1,30 @@ +import dateutil +from hive.custom_exceptions import generic_exception as ge + +class Utils: + @staticmethod + def convert_time(value, default): + if not value: + return default + if isinstance(value, str): + if value.startswith("now"): + # handle relative formats like "now-15m", "now-1h" + if "-" in value: + value = value.split("-")[1] + num = int(''.join([c for c in value if c.isdigit()])) + unit = ''.join([c for c in value if c.isalpha()]) + if unit == "m": + return f"now() - interval '{num} minutes'" + elif unit == "h": + return f"now() - interval '{num} hours'" + elif unit == "d": + return f"now() - interval '{num} days'" + return "now()" + else: + # Assume ISO8601 timestamp + try: + dt = dateutil.parser.isoparse(value) + return f"'{dt.strftime('%Y-%m-%d %H:%M:%S')}'" + except Exception: + raise ge.GenericError(400, f"Invalid time format: {value}") + return default Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/device_metrics.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/device_metrics.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/device_metrics.py (working copy) @@ -0,0 +1,110 @@ +from django.http import JsonResponse + +from cm.lib.libbasic_operation import oper_log +from hive.custom_exceptions import generic_exception as ge +from hive.db.slb_stats_queries import SLBStats +from hive.db.device_metric_queries import DeviceMetrics +import json +from hive.utils import andebug + + +def handle_device_metrics_req(request, path=None): + try: + if request.method in ['GET', 'POST']: + return get_device_metrics(request) + else: + return JsonResponse({ + 'error': 405, + 'message': "Invalid HTTP method" + }, content_type='application/json') + except ge.GenericError as e: + oper_log('error', 'system', e.message) + return ge.handle_exception(e) + except Exception as e: + oper_log('error', 'system', 'An Unexpected error occurred, details: {}'.format(e)) + e.message = 'An Unexpected error occurred, details: {}'.format(e) + return ge.handle_exception(e) + + +def get_device_metrics(request): + """ Fetch the device metrics data """ + try: + req_body = json.loads(request.body) + if 'query' in req_body and 'agent_host' not in req_body['query']: + oper_log('error', 'system', "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + raise ge.GenericError(400, "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + agent_host = req_body.get("agent_host", None) + interval = req_body.get("interval", "1m") + time_from = req_body.get("from", None) + time_to = req_body.get("to", None) + metric_name = req_body.get("metric_name", None) + json_response = {} + + if metric_name and metric_name == 'cpu_usage': + metrics = DeviceMetrics.get_cpu_usage_metrics(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name and metric_name == 'memory_usage': + metrics = DeviceMetrics.get_mem_usage_metrics(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name and metric_name == 'disk_usage': + metrics = DeviceMetrics.get_disk_usage_metrics(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name and metric_name == 'network_throughput': + metrics = DeviceMetrics.get_network_throughput(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name == "ssl_connections": + metrics = DeviceMetrics.get_ssl_connections(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name == "connections": + metrics = DeviceMetrics.get_connections(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name == "requests": + metrics = DeviceMetrics.get_requests(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + elif metric_name == "ssl_core_utilization": + metrics = DeviceMetrics.get_ssl_core_utilization(agent_host, interval, time_from, time_to) + json_response = { + "status": 1, + "data": metrics + } + else: + device_stats = DeviceMetrics.get_device_stats(time_from, time_to)['data'] + net_throughput = DeviceMetrics.get_throughput_data(interval, time_from, time_to)['data'] + json_response = { + "status": 1, + "data": { + "device_stats": device_stats, + "net_throughput": net_throughput + } + } + + return JsonResponse(json_response) + + except Exception as e: + return JsonResponse({ + "status": 0, + "error": str(e) + }, status=500) Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/slb_stats.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/slb_stats.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/slb_stats.py (working copy) @@ -0,0 +1,113 @@ +from django.http import JsonResponse + +from cm.lib.libbasic_operation import oper_log +from hive.custom_exceptions import generic_exception as ge +from hive.db.slb_stats_queries import SLBStats +from hive.utils import andebug +import json + + +def handle_slb_stats_req(request, path=None): + try: + if path == 'virtual_stats' and request.method in ['GET', 'POST']: + return get_virtual_service_stats(request) + elif path == 'real_stats' and request.method in ['GET', 'POST']: + return get_real_service_stats(request) + else: + return JsonResponse({ + 'error': 405, + 'message': "Invalid HTTP method" + }, content_type='application/json') + except ge.GenericError as e: + oper_log('error', 'system', e.message) + return ge.handle_exception(e) + except Exception as e: + oper_log('error', 'system', 'An Unexpected error occurred, details: {}'.format(e)) + e.message = 'An Unexpected error occurred, details: {}'.format(e) + return ge.handle_exception(e) + + +def get_virtual_service_stats(request): + """ Fetch data for SLB Virtual stats data """ + try : + req = {} + req = json.loads(request.body) + if 'query' in req and 'agent_host' not in req['query']: + oper_log('error', 'system', "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + raise ge.GenericError(400, "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + + agent_host = req.get("agent_host") + server_id = req.get("server_id", None) + interval = req.get("interval", "20s") + time_from = req.get("from") + time_to = req.get("to") + if not agent_host: + raise ge.GenericError(400, "agent_host field is required for Network Throughput stats") + + if "stat_name" in req and req["stat_name"] == "network_throughput": + response = SLBStats.get_network_throughput(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req and req["stat_name"] == "total_hits": + response = SLBStats.get_total_hits(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req and req["stat_name"] == "hits_distribution": + response = SLBStats.get_hits_distribution(agent_host, server_id, time_from, time_to) + elif req.get("stat_name") == "connection_count": + response = SLBStats.get_connection_count(agent_host, server_id, interval, time_from, time_to) + elif req.get("stat_name") == "connections_per_sec": + response = SLBStats.get_connections_per_sec(agent_host, server_id, interval, time_from, time_to) + else: + response = SLBStats.get_virtual_service_stats(agent_host) + + return JsonResponse({ + "status": 1, + "data": response + }) + except Exception as e: + return JsonResponse({ + "status": 0, + "error": str(e) + }, status=500) + + +def get_real_service_stats(request): + """ + Fetch data for SLB Real stats data + """ + try: + req_body = json.loads(request.body) + + if 'query' in req_body and 'agent_host' not in req_body['query']: + oper_log('error', 'system', "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + raise ge.GenericError(400, "Query must contain only agent_host details. Please specify agent_host in the " + "query.") + agent_host = req_body.get("agent_host", None) + server_id = req_body.get("server_id", None) + interval = req_body.get("interval", "1m") + time_from = req_body.get("from", None) + time_to = req_body.get("to", None) + + if "stat_name" in req_body and req_body["stat_name"] == "network_throughput": + response = SLBStats.get_real_network_throughput(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req_body and req_body["stat_name"] == "outstanding_requests": + response = SLBStats.get_outstanding_requests(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req_body and req_body["stat_name"] == "hits": + response = SLBStats.get_real_stats_total_hits(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req_body and req_body["stat_name"] == "connection_count": + response = SLBStats.get_real_connection_count(agent_host, server_id, interval, time_from, time_to) + elif "stat_name" in req_body and req_body["stat_name"] == "connections_per_sec": + response = SLBStats.get_real_connections_per_sec(agent_host, server_id, interval, time_from, time_to) + else: + response = SLBStats.get_real_service_stats(agent_host) + + return JsonResponse({ + "status": 1, + "data": response + }) + + except Exception as e: + return JsonResponse({ + "status": 0, + "error": str(e) + }, status=500)