Index: /branches/amp_4_0/platform/config/init_db.sql =================================================================== --- /branches/amp_4_0/platform/config/init_db.sql (revision 2738) +++ /branches/amp_4_0/platform/config/init_db.sql (working copy) @@ -403,20 +403,25 @@ CREATE TABLE IF NOT EXISTS REPORT ( - id serial PRIMARY KEY, + id serial PRIMARY KEY, name varchar(64) NOT NULL, - args TEXT DEFAULT NULL, - timeout integer DEFAULT 0, - tags varchar(64) DEFAULT NULL, + device_ip varchar(64) DEFAULT NULL, + from_time varchar(64) NOT NULL, + to_time varchar(64) DEFAULT NULL, + timeout integer DEFAULT 0, + subject_type varchar(64) DEFAULT NULL, + subject_name varchar(64) DEFAULT NULL, + send_to varchar(64) DEFAULT NULL, status integer DEFAULT 0, - time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS REPORT_LOG ( id serial PRIMARY KEY, report_id integer NOT NULL, - tags varchar(64) DEFAULT NULL, + subject_type varchar(64) DEFAULT NULL, + subject_name varchar(64) DEFAULT NULL, start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, end_time TIMESTAMP DEFAULT NULL, status integer DEFAULT 0, \ No newline at end of file @@ -440,4 +445,4 @@ CONSTRAINT host_pkey PRIMARY KEY (id), CONSTRAINT host_name_key UNIQUE (name) ); --- psql -U amp_admin -d cm -f /path/to/your/init_db.sql +-- psql -U amp_admin -d cm -f /path/to/your/init_db.sql \ No newline at end of file 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 2738) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/djproject/urls.py (working copy) @@ -26,6 +26,7 @@ from hive.controller.utils import handle_observability_status_req from hive.an_opensearch import opensearch_proxy, get_opensearch_sso_token from hive.controller.system_metrics import handle_get_latest_system_metrics, handle_get_historical_system_metrics +from hive.controller.generic_contoller import handle_service_query_req js_info_dict = { #'packages': ('your.app.package',), @@ -73,7 +74,9 @@ re_path(r'^device_metrics(?:/(?P.*))?$', handle_device_metrics_req), re_path(r'^ssl_vpn_stats(?:/(?P.*))?$', handle_ssl_vpn_stats_req), re_path(r'^llb_stats(?:/(?P.*))?$', handle_llb_stats_req), - re_path(r'^report/(?P.*)$', handle_report_generation), + re_path(r"^report/?$", handle_report_generation), + re_path(r"^report/(?P\d+)(?:/(?P.*))?$", handle_report_generation), + re_path(r"^report/(?P.*)$", handle_report_generation), re_path(r'^log/(?P\w+)$', handle_log_location_app), re_path(r'^backup/(?P.*)$', handle_backup_req), re_path(r'^restore/(?P.*)$', handle_restore_req), @@ -96,6 +99,7 @@ re_path(r'^cm/get_ha_log$', get_ha_log), re_path(r'^cm/update_ha_setting$', update_ha_setting), re_path(r'^cm/delete_ha_setting$', delete_ha_setting), + re_path(r'^cm/services', handle_service_query_req), re_path(r'^(?P\w+)/(?P.*)$', hive_router), re_path(r'^login$', login_handler), re_path(r'^logout$', logout_handler), Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/controller/generic_controller.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/controller/generic_controller.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/controller/generic_controller.py (working copy) @@ -0,0 +1,18 @@ +import json +from django.http import JsonResponse +from hive.services.generic_service import ServiceManager + + +def handle_service_query_req(request): + try: + data = json.loads(request.body or "{}") + except json.JSONDecodeError: + return JsonResponse({"error": "Invalid JSON body"}, status=400) + + service_type = data.get("serviceType") + device_ip = data.get("device_ip") + + response = ServiceManager.fetch_services(service_type, device_ip) + + status = 400 if "error" in response else 200 + return JsonResponse(response, status=status) 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 (revision 2738) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/db_client.py (working copy) @@ -10,7 +10,11 @@ DB_PASSWORD = os.environ.get("TIMESCALEDB_PASSWORD", "Array@123$") DB_PORT = int(os.environ.get("TIMESCALEDB_PORT", 5432)) +POSTGRES_DB_NAME = os.environ.get("POSTGRES_DB", "cm") +POSTGRES_DB_USER = os.environ.get("POSTGRESDB_USER", "amp_admin") +POSTGRES_DB_PASSWORD = os.environ.get("POSTGRESDB_PASSWORD", "Array@123$") + # ToDo: Implement Connection Pool def get_db_connection(): return psycopg2.connect( @@ -21,13 +25,22 @@ ) +def get_postgres_db_conn(): + return psycopg2.connect( + host=DB_HOST, + dbname=POSTGRES_DB_NAME, + user=POSTGRES_DB_USER, + password=POSTGRES_DB_PASSWORD + ) + + class DBClient: def __init__(self): pass @staticmethod - def execute_query(req_dict): + def execute_query(req_dict, db=None): """ Executes a Timescale/Postgres query and returns a structured JSON response. req_dict = { @@ -38,7 +51,10 @@ conn = None cur = None try: - conn = get_db_connection() + if db == 'postgres': + conn = get_postgres_db_conn() + else: + conn = get_db_connection() cur = conn.cursor() # Execute query (with params if provided) @@ -47,6 +63,9 @@ else: cur.execute(req_dict["query"]) + if req_dict["query"].strip().lower().startswith("insert"): + conn.commit() + rows = cur.fetchall() col_names = [desc[0] for desc in cur.description] result = [dict(zip(col_names, row)) for row in rows] @@ -79,7 +98,30 @@ if conn: conn.close() + @staticmethod + def execute_modify_query(req_dict, db=None): + """Execute INSERT, UPDATE, DELETE queries and return affected rows""" + conn, cur = None, None + try: + conn = get_postgres_db_conn() if db == "postgres" else get_db_connection() + cur = conn.cursor() + if "params" in req_dict: + cur.execute(req_dict["query"], req_dict["params"]) + else: + cur.execute(req_dict["query"]) + + conn.commit() + return {"status": 200, "message": "success", "data": {"affected_rows": cur.rowcount}} + + except Exception as e: + return {"status": 500, "message": "Error", "data": str(e)} + + finally: + if cur: cur.close() + if conn: conn.close() + + def with_db_connection(func): def wrapper(*args, **kwargs): conn = None Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/generic_db_queries.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/generic_db_queries.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/db/generic_db_queries.py (working copy) @@ -0,0 +1,52 @@ +from hive.db.db_client import DBClient + + +class DBManager: + @staticmethod + def get_services_query(service_type, device_ip): + + """ + Build SQL query to fetch distinct service names for a given serviceType. + """ + + query = "" + + if service_type == "virtualService": + query = """ + SELECT DISTINCT serverid AS service_name + FROM apv_virtual_stats + WHERE time > NOW() - INTERVAL '20 seconds' + """ + if device_ip: + query += f" AND agent_host = '{device_ip}'" + + elif service_type == "realService": + query = """ + SELECT DISTINCT real_server_id AS service_name + FROM apv_real_stats + WHERE time > NOW() - INTERVAL '20 seconds' + """ + if device_ip: + query += f" AND agent_host = '{device_ip}'" + + elif service_type == "sslvpnService": + query = """ + SELECT DISTINCT id AS service_name + FROM ag_virtual_site_stats + WHERE time > NOW() - INTERVAL '20 seconds' + """ + if device_ip: + query += f" AND agent_host = '{device_ip}'" + + elif service_type == "LLB": + query = """ + SELECT DISTINCT link_name AS service_name + FROM apv_llb_stats + WHERE time > NOW() - INTERVAL '20 seconds' + """ + if device_ip: + query += f" AND agent_host = '{device_ip}'" + + query += ";" + response = DBClient.execute_query({"query": query}) + return response Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_pdf_report.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_pdf_report.py (revision 2738) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_pdf_report.py (working copy) @@ -1,3 +1,4 @@ +import os from reportlab.lib import colors from reportlab.lib.pagesizes import letter from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle @@ -36,6 +37,7 @@ """ Generate a PDF report with a table and an optional chart. """ + os.makedirs(REPORT_FILE_PATH, exist_ok=True) pdf = SimpleDocTemplate(REPORT_FILE_PATH + filename, pagesize=letter) elements = [] styles = getSampleStyleSheet() @@ -84,9 +86,14 @@ elements.append(Paragraph("Link Throughput(bps)", styles["Heading4"])) elements.append(Spacer(1, 20)) + # Clean up timezone info in time strings + formatted_time = [ + str(t).split('+')[0] if isinstance(t, str) else t.strftime("%Y-%m-%d %H:%M:%S") + for t in llb_link_data["time"] + ] headers = ["Time", "Inbound (bps)", "Outbound (bps)"] llb_data = list( - zip(llb_link_data["time"], llb_link_data["inbound_list"], llb_link_data["outbound_list"])) + zip(formatted_time, llb_link_data["inbound_list"], llb_link_data["outbound_list"])) table = self.get_table_data(headers, llb_data) elements.append(table) elements.append(Spacer(1, 30)) @@ -120,5 +127,5 @@ pdf.build(elements) except Exception as e: - oper_log('error', 'system', e.message) + oper_log('error', 'system', str(e)) raise e # Raise to crash and see error Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_report.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_report.py (revision 2738) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_report.py (working copy) @@ -1,149 +1,386 @@ import json import os import time -from django.http import HttpResponse +from datetime import datetime +from django.http import JsonResponse, StreamingHttpResponse from cm.lib.libbasic_operation import oper_log from hive.custom_exceptions import generic_exception as ge from hive.db.llb_stats_queries import LLBStatsDB -# from hive.controller.llb_stats import reformat_data_from_db +from hive.report.report_queries import ReportDB +from hive.services.utils import convert_time from hive.report.generate_pdf_report import Report +from hive.utils import andebug + REPORT_FILE_PATH = "/ca/webui/htdocs/new/src/hive/media/docs/" -def handle_report_generation(request, path=None): + + +def handle_report_generation(request, path=None, report_id=None): + andebug('an.model.cli', 'Inside handle_report_generation') try: - if path == 'pdf' and request.method == 'POST': - return generate_pdf_report(request) + if request.method == 'POST': + if path == 'save': + return save_report(request) + elif path == 'generate' and report_id: + return generate_report(request, report_id) + + elif request.method == 'PUT' and report_id: + andebug('an.model.cli', 'Inside update_report else') + return update_report(request, report_id) + + elif request.method == 'GET': + if path == 'download': + return download_report(request.GET.get('filename')) + else : + return get_all_reports(request) + + elif request.method == 'DELETE' and report_id: + return delete_report(report_id) else: - return HttpResponse(json.dumps({ - 'error': 405, - 'message': "Invalid HTTP method" - }), content_type='application/json') + return JsonResponse({"error": "Invalid path or parameters"}, status=400) except ge.GenericError as e: - oper_log('error', 'system', e.message) + oper_log("error", "system", str(e)) 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 generate_pdf_report(request): + oper_log("error", "system", f"Unexpected error: {str(e)}") + return JsonResponse({"error": f"Unexpected error: {str(e)}"}, status=500) + + +def get_all_reports(request): + """ + Handle GET request to fetch all reports. + """ try: - req = json.loads(request.body) - if 'query' in req and 'from_time' in req['query'] and 'to_time' in req['query']: - from_time = req['query'].get('from_time', '') - to_time = req['query'].get('to_time', '') - category = req['query'].get('category') - link_name = req['query'].get('link_name', '') - else: - oper_log('error', 'system', - "Query cannot be empty. Please specify from_time and to_time and link_name in the query.") - raise ge.GenericError(400, "Query cannot be empty. Please specify from_time and to_time and link_name in " - "the query.") - if category and str(category).upper() == 'LLB': - data_dict = get_llb_detailed_data_for_report(from_time, to_time, link_name) - data_summary_dict = get_link_summary_data_for_report(from_time, to_time) - else: - oper_log('error', 'system', - "category cannot be empty. Please specify category in the query.") - raise ge.GenericError(400, "category cannot be empty. Please specify category in the query.") - report_generator = Report() - intervalStart, intervalEnd = LLBStatsDB.get_time_range(from_time, to_time) - start_time = time.strftime("%Y_%m_%d_%H_%M_%S", time.localtime(intervalEnd / 1000)) - end_time = time.strftime("%Y_%m_%d_%H_%M_%S", - time.localtime(intervalStart / 1000)) - report_name = str(category).upper() + "_" + "Statistics_Report_from_%s_to_%s.pdf" % ( - start_time, end_time) - report_generator.generate_pdf_report(report_name, category, data_dict, data_summary_dict, intervalStart, - intervalEnd) - if os.path.exists(REPORT_FILE_PATH + report_name): - return HttpResponse(json.dumps({"result": True, "filename": report_name}), content_type='application/json') - else: - return HttpResponse(json.dumps({"result": False}), content_type='application/json') - except ge.GenericError as e: - oper_log('error', 'system', e.message) - return ge.handle_exception(e) + if request.method != 'GET': + return JsonResponse({"error": "Invalid request method"}, status=405) + + reports = ReportDB.get_all_reports() + + return JsonResponse({"reports": reports}, status=200, safe=False) except Exception as e: - oper_log('error', 'system', e.message) - return HttpResponse(json.dumps({"error": "Error while generating PDF", - }), content_type='application/json') -def get_link_summary_data_for_report(from_time, to_time): - """ Returns data for LLB Summary report """ + oper_log("error", "system", f"Unexpected error in get_all_reports: {str(e)}") + return JsonResponse({"error": f"Unexpected error: {str(e)}"}, status=500) + + +def save_report(request): + """ Save New Report """ try: - response = LLBStatsDB.get_llb_summary_data(from_time, to_time) - # Handle cases where response is None or not JSON - if not response: - return [] - if isinstance(response, str): - response = json.loads(response) - # response_data = reformat_data_from_db(response, 'historical') - llb_dict = {} - # if response_data: - # for res_data in response_data: - # data = res_data.get("values") - # link_name = res_data.get("linkName") - # for llb_point in data: - # llb_dict[link_name] = { - # 'time': llb_point[0], - # 'avg_link_hits': llb_point[1], - # 'avg_link_conn': llb_point[2], - # 'avg_bandwidth_in': llb_point[3], - # 'avg_bandwidth_out': llb_point[4], - # 'avg_link_usage': llb_point[5], - # } - # else: - # if 'results' in response and all(len(res) == 1 and 'statement_id' in res for res in response['results']): - # return HttpResponse(json.dumps({ - # "error": "No valid data found" - # }), content_type='application/json') - # else: - # return HttpResponse(json.dumps(response), content_type='application/json') - return llb_dict + data = json.loads(request.body.decode("utf-8") or "{}") + except json.JSONDecodeError: + return JsonResponse({"error": "Invalid JSON body"}, status=400) + + required_fields = ["name", "subject_type", "subject_name", "from_time", "to_time"] + if not all(data.get(field) for field in required_fields): + return JsonResponse({"error": "Missing required fields"}, status=400) + + try: + report_id = ReportDB.insert_report( + data.get("name"), + data.get("subject_type"), + data.get("subject_name"), + data.get("from_time"), + data.get("to_time"), + data.get("device_ip"), + data.get("send_to"), + ) + + if not report_id: + return JsonResponse({"error": "Failed to save report data"}, status=500) + + return JsonResponse({"message": "Report data saved successfully", "report_id": report_id}, status=201) except Exception as e: - return HttpResponse(json.dumps({"error": "Error while fetching LLB Summary data", - }), content_type='application/json') -def get_llb_detailed_data_for_report(from_time, to_time, link_name): - """ Returns data for LLB Detailed report """ + oper_log("error", "system", f"Report data insert failed: {str(e)}") + return JsonResponse({"error": f"Failed to save report data: {str(e)}"}, status=500) + + +def update_report(request, report_id): + """ Update existing report """ try: - time_list = [] - cc_list = [] - bandwidth_usage_list = [] - inbound_list = [] - outbound_list = [] - hit_list = [] - response = LLBStatsDB.get_llb_historical_data(from_time, to_time, link_name) - # Handle cases where response is None or not JSON - if not response: - return [] - if isinstance(response, str): - response = json.loads(response) - # response_data = reformat_data_from_db(response, 'historical') + data = json.loads(request.body.decode("utf-8") or "{}") + except json.JSONDecodeError: + return JsonResponse({"error": "Invalid JSON body"}, status=400) + + if not report_id: + return JsonResponse({"error": "Missing report_id"}, status=400) + + try: + updated = ReportDB.update_report( + report_id, + data.get("name"), + data.get("subject_type"), + data.get("subject_name"), + data.get("device_ip"), + data.get("from_time"), + data.get("to_time"), + data.get("send_to"), + ) + + if not updated: + return JsonResponse({"error": "Report update failed or no changes detected"}, status=400) + + oper_log("info", "system", f"Report {report_id} updated successfully") + return JsonResponse({"message": f"Report {report_id} updated successfully"}, status=200) + except Exception as e: + oper_log("error", "system", f"Report update failed: {str(e)}") + return JsonResponse({"error": f"Failed to update report: {str(e)}"}, status=500) + + +def delete_report(report_id): + """ Delete report and its logs """ + if not report_id: + return JsonResponse({"error": "Missing report_id"}, status=400) + + try: + # Delete logs first, then report + ReportDB.delete_report_log(report_id) + deleted = ReportDB.delete_report(report_id) + + if not deleted: + return JsonResponse({"error": f"Failed to delete report data with report id: {report_id}"}, status=400) + + oper_log("info", "system", f"Report {report_id} data deleted successfully") + return JsonResponse({"message": f"Report {report_id} data deleted successfully"}, status=200) + except Exception as e: + oper_log("error", "system", f"Report data delete failed: {str(e)}") + return JsonResponse({"error": f"Failed to delete report data with report id: {report_id}"}, status=500) + + +def download_report(filename): + """ Download Report """ + try: + if not filename: + return JsonResponse({ + "error": 400, + "message": "Filename not provided. Please provide the Report name" + }, status=400) + + file_path = os.path.join(REPORT_FILE_PATH, filename) + + if not os.path.exists(file_path): + return JsonResponse({ + "error": 404, + "message": "Report does not exist" + }, status=404) + + # Using Stream Http to get the file response as chunks of data + # instead of loading whole file in memory which is memory intensive + response = StreamingHttpResponse(file_iterator(file_path), content_type='application/octet-stream') + response['Content-Disposition'] = 'attachment; filename="{}"'.format(filename) + return response + except IOError as e: + message = str(e).replace("'", "") + oper_log('error', 'system', 'Error downloading Report!, details: {}'.format(message)) + raise ge.GenericError(500, "Error downloading Report!") + + +def file_iterator(file_path, chunk_size=8192): + """ Iterates over the file contents returning a chunk of data each time """ + with open(file_path, 'rb') as file: + while True: + chunk = file.read(chunk_size) + if not chunk: + break + yield chunk + + +def generate_report(request, report_id): + """ Generate Report by ID """ + try: + report_id = int(report_id) + except (TypeError, ValueError): + return JsonResponse({"error": "Invalid report_id"}, status=400) + + report_details = ReportDB.get_report_details(report_id) + if not report_details: + return JsonResponse({"error": "Report not found"}, status=404) + + report = report_details[0] + + # Insert a new report_log entry + log_response = ReportDB.insert_report_log(report_id, report["subject_type"], report["subject_name"]) + log_id = log_response[0]["id"] if isinstance(log_response, list) else log_response + + if report.get("subject_type") == "service_status": + sub_name = report.get("subject_name", "") + if "LLB" in sub_name: + from_time = report.get("from_time") + to_time = report.get("to_time") + time_interval = get_dynamic_interval(from_time, to_time) + + data_dict = get_llb_detailed_data_for_report(from_time, to_time, None, time_interval) + data_summary_dict = get_link_summary_data_for_report(from_time, to_time) + + if isinstance(from_time, str) and from_time.startswith("now"): + if "-" in from_time: # e.g., now-15m, now-1h + interval_start = _convert_relative_time(from_time) + else: + interval_start = int(time.time() * 1000) # plain 'now' + interval_end = int(time.time() * 1000) + else: + interval_start = _to_epoch_ms(from_time) + interval_end = _to_epoch_ms(to_time) + + filename_start_time_str = time.strftime("%Y_%m_%d_%H_%M_%S", time.localtime(interval_start / 1000)) + filename_end_time_str = time.strftime("%Y_%m_%d_%H_%M_%S", time.localtime(interval_end / 1000)) + + display_start_time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(interval_start / 1000)) + display_end_time_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(interval_end / 1000)) + + report_name = f"LLB_Statistics_Report_from_{filename_start_time_str}_to_{filename_end_time_str}.pdf" + + report_generator = Report() + report_generator.generate_pdf_report( + report_name, "LLB", data_dict, data_summary_dict, display_start_time_str, display_end_time_str + ) + + file_path = os.path.join(REPORT_FILE_PATH, report_name) + file_size = os.path.getsize(file_path) if os.path.exists(file_path) else 0 + status = 1 if os.path.exists(file_path) else 0 + + # Update DB + ReportDB.update_report_log(report_id, status, report_name, file_size) + ReportDB.update_report_status(status, report_id) + + return JsonResponse( + { + "message": "Report generated successfully" if status else "Report generation failed", + "report_id": report_id, + "log_id": log_id, + "file_name": report_name, + "file_size": file_size, + }, + status=200 if status else 500, + ) + else: + return JsonResponse({"error": f"Unsupported report type: {report.get('subject_type')}"}, status=400) + + +def get_dynamic_interval(from_time, to_time): + """ + Determine optimal interval size for querying time-series data, + based on the total range between from_time and to_time. + """ + try: + # Convert string timestamps (ISO 8601 or epoch) to integers (milliseconds) + if isinstance(from_time, str) and from_time.startswith("now"): + if "-" in from_time: # e.g., now-15m, now-1h + from_time_ms = _convert_relative_time(from_time) + else: + from_time_ms = int(time.time() * 1000) # plain 'now' + to_time_ms = int(time.time() * 1000) + else: + from_time_ms = _to_epoch_ms(from_time) + to_time_ms = _to_epoch_ms(to_time) + + diff_ms = abs(to_time_ms - from_time_ms) + diff_hours = diff_ms / (1000 * 60 * 60) + + # Decide on interval granularity based on time range + if diff_hours <= 6: + return "1m" # up to 6 hours → 1-minute resolution + elif diff_hours <= 24: + return "5m" # 6–24 hours → 5-minute resolution + elif diff_hours <= 72: + return "15m" # 1–3 days → 15-minute resolution + elif diff_hours <= 168: + return "1h" # up to 1 week → 1-hour resolution + elif diff_hours <= 720: + return "6h" # up to 1 month → 6-hour resolution + else: + return "1d" # over a month → daily resolution + + except Exception as e: + oper_log('error', 'system', f"Error computing interval: {str(e)}") + return "20s" # fallback + + +def _to_epoch_ms(value): + """Convert ISO 8601 string or epoch (sec/ms) to milliseconds""" + if isinstance(value, (int, float)): + return int(value if value > 1e12 else value * 1000) + elif isinstance(value, str): + try: + return int(datetime.fromisoformat(value.replace("Z", "+00:00")).timestamp() * 1000) + except Exception: + return int(value) # fallback if numeric string + return int(time.time() * 1000) + + +def _convert_relative_time(value): + """Handle 'now-10m', 'now-1h', etc.""" + units = {"m": 60 * 1000, "h": 3600 * 1000, "d": 86400 * 1000} + if not value.startswith("now"): + return int(time.time() * 1000) + num = int(''.join([c for c in value if c.isdigit()])) + unit = ''.join([c for c in value if c.isalpha()])[-1] + return int(time.time() * 1000) - (num * units.get(unit, 0)) + + +def get_link_summary_data_for_report(time_from, time_to): + """ Returns data for LLB Summary Report """ + try: + from_time = convert_time(time_from, "now() - interval '15 minutes'") + to_time = convert_time(time_to, "now()") + response = LLBStatsDB.get_llb_summary_data(from_time, to_time) + + if not response or "data" not in response: + return {} + + summary_dict = {} + for row in response["data"]: + link_name = row.get("link_name") + summary_dict[link_name] = { + "avg_link_hits": row.get("avg_link_hits", 0), + "avg_link_conn": row.get("avg_link_conn", 0), + "avg_bandwidth_in": row.get("avg_bandwid_in", 0), + "avg_bandwidth_out": row.get("avg_bandwid_out", 0), + "avg_link_usage": row.get("avg_link_usage", 0) + } + + return summary_dict + + except Exception as e: + return JsonResponse({"error": f"Error while fetching LLB data: {str(e)}"}, status=500) + + +def get_llb_detailed_data_for_report(time_from, time_to, link_name=None, interval="20s"): + """ Returns data for LLB Detailed report. """ + try: + from_time = convert_time(time_from, "now() - interval '15 minutes'") + to_time = convert_time(time_to, "now()") + response = LLBStatsDB.get_llb_historical_data(from_time, to_time, link_name, interval) + + # Defensive check + if not response or "data" not in response or not isinstance(response["data"], list): + return {} + llb_dict = {} - # if response_data: - # for res_data in response_data: - # data = res_data.get("values") - # link_name = res_data.get("linkName") - # for llb_point in data: - # time_list.append(llb_point[0]) - # inbound_list.append(llb_point[1]) - # outbound_list.append(llb_point[2]) - # bandwidth_usage_list.append(llb_point[3]) - # cc_list.append(llb_point[4]) - # hit_list.append(llb_point[5]) - # - # llb_dict[link_name] = { - # 'time': time_list, - # 'inbound_list': inbound_list, - # 'outbound_list': outbound_list, - # 'bandwidth_usage_list': bandwidth_usage_list, - # 'cc_list': cc_list, - # 'hit_list': hit_list, - # } - # else: - # if 'results' in response and all(len(res) == 1 and 'statement_id' in res for res in response['results']): - # return HttpResponse(json.dumps({ - # "error": "No valid data found" - # }), content_type='application/json') - # else: - # return HttpResponse(json.dumps(response), content_type='application/json') + + for row in response["data"]: + link = row.get("link_name", "unknown") + ts = row.get("ts") + if not ts: + continue # Skip incomplete rows + + # Initialize dict for link_name + if link not in llb_dict: + llb_dict[link] = { + "time": [], + "inbound_list": [], + "outbound_list": [], + "bandwidth_usage_list": [], + "cc_list": [], + "hit_list": [] + } + + # Append metrics to each list + llb_dict[link]["time"].append(ts) + llb_dict[link]["inbound_list"].append(row.get("avg_bandwid_in", 0)) + llb_dict[link]["outbound_list"].append(row.get("avg_bandwid_out", 0)) + llb_dict[link]["bandwidth_usage_list"].append(row.get("avg_link_usage", 0)) + llb_dict[link]["cc_list"].append(row.get("avg_link_conn", 0)) + llb_dict[link]["hit_list"].append(row.get("avg_link_hits", 0)) + return llb_dict + except Exception as e: - return HttpResponse(json.dumps({"error": "Error while fetching LLB data", - }), content_type='application/json') \ No newline at end of file + return JsonResponse({"error": f"Error while fetching LLB data: {str(e)}"}, status=500) Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_queries.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_queries.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_queries.py (working copy) @@ -0,0 +1,244 @@ +from hive.db.db_client import DBClient +from hive.utils import andebug +from cm.lib.libbasic_operation import oper_log + + +class ReportDB: + + @staticmethod + def get_all_reports(): + """ + Fetch all reports from the report table. + """ + query = """ + SELECT + id, + name, + subject_type, + subject_name, + device_ip, + from_time, + to_time, + send_to + FROM report + ORDER BY id ASC; + """ + req = {"query": query} + response = DBClient.execute_query(req, "postgres") + + if response.get("status") == 200: + return response.get("data", []) + else: + oper_log('error', 'system', f"DB Error: {response.get('message')} - {response.get('data')}") + return [] + + @staticmethod + def get_report_details(report_id): + """ + Fetch report details by report_id. + """ + query = f""" + SELECT + name, + subject_type, + subject_name, + device_ip, + from_time, + to_time, + send_to + FROM report + WHERE id = {report_id}; + """ + req = {"query": query} + response = DBClient.execute_query(req, "postgres") + + if response.get("status") == 200: + return response.get("data", []) + else: + oper_log('error', 'system', f"DB Error: {response.get('message')} - {response.get('data')}") + return [] + + @staticmethod + def insert_report(name, subject_type, subject_name, from_time, to_time, device_ip=None, send_to=None): + """ + Insert a new report entry and return its id. + """ + query = """ + INSERT INTO report (name, subject_type, subject_name, device_ip, from_time, to_time, send_to) + VALUES (%s, %s, %s, %s, %s, %s, %s) + RETURNING id; + """ + params = (name, subject_type, subject_name, device_ip, from_time, to_time, send_to) + req = {"query": query, "params": params} + response = DBClient.execute_query(req, "postgres") + + if response.get("status") == 200 and response.get("data"): + return response["data"][0]["id"] + else: + oper_log('error', 'system', f"Failed to insert report: {response.get('message')} - {response.get('data')}") + return None + + @staticmethod + def update_report(report_id, name, subject_type, subject_name, device_ip, from_time, to_time, send_to): + """ + Update an existing report entry. + """ + query = """ + UPDATE report + SET + name = %s, + subject_type = %s, + subject_name = %s, + device_ip = %s, + from_time = %s, + to_time = %s, + send_to = %s + WHERE id = %s; + """ + params = (name, subject_type, subject_name, device_ip, from_time, to_time, send_to, report_id) + req = {"query": query, "params": params} + response = DBClient.execute_modify_query(req, "postgres") + + if response.get("status") == 200: + return True + else: + oper_log('error', 'system', f"Failed to update report: {response.get('message')} - {response.get('data')}") + return False + + + @staticmethod + def update_report_status(status, report_id): + """ + Update the report status field. + """ + query = """ + UPDATE report + SET status = %s + WHERE id = %s; + """ + params = (status, report_id) + req = {"query": query, "params": params} + response = DBClient.execute_modify_query(req, "postgres") + + if response.get("status") == 200: + return True + else: + oper_log('error', 'system', + f"Failed to update report status: {response.get('message')} - {response.get('data')}") + return False + + @staticmethod + def delete_report(report_id): + """ + Delete a report by ID. + """ + query = "DELETE FROM report WHERE id = %s;" + params = (report_id,) + req = {"query": query, "params": params} + response = DBClient.execute_modify_query(req, "postgres") + + if response.get("status") == 200 and response.get("rows_affected", 0) > 0: + return True + else: + oper_log('error', 'system', f"Report {report_id} not found or already deleted or DB error") + return False + + @staticmethod + def get_report_log(report_id): + """ + Fetch report log entries by report_id. + """ + query = f""" + SELECT + id, + subject_type, + subject_name, + start_time, + status, + result + FROM report_log + WHERE report_id = {report_id}; + """ + req = {"query": query} + response = DBClient.execute_query(req, "postgres") + + if response.get("status") == 200: + return response.get("data", []) + else: + oper_log('error', 'system', + f"Failed to fetch report_log: {response.get('message')} - {response.get('data')}") + return [] + + @staticmethod + def insert_report_log(report_id, subject_type, subject_name): + """ + Insert a new report_log entry and return its id. + """ + query = """ + INSERT INTO report_log (report_id, subject_type, subject_name) + VALUES (%s, %s, %s) + RETURNING id; + """ + params = (report_id, subject_type, subject_name) + req = {"query": query, "params": params} + response = DBClient.execute_query(req, "postgres") + + if response.get("status") == 200 and response.get("data"): + return response["data"][0]["id"] + else: + oper_log('error', 'system', + f"Failed to insert report_log: {response.get('message')} - {response.get('data')}") + return None + + @staticmethod + def update_report_log(report_id, status, filename, file_size): + """ + Update the latest report_log entry with status and result details. + """ + result = { + "file_name": filename or "", + "format": "pdf", + "language": "en", + "message": "success" if status == 1 else "failure", + "size": file_size or "" + } + query = """ + UPDATE report_log + SET + end_time = CURRENT_TIMESTAMP, + status = %s, + result = %s + WHERE id = ( + SELECT id FROM report_log + WHERE report_id = %s + ORDER BY start_time DESC + LIMIT 1 + ); + """ + params = (status, str(result), report_id) + req = {"query": query, "params": params} + response = DBClient.execute_modify_query(req, "postgres") + + if response.get("status") == 200: + return True + else: + oper_log('error', 'system', + f"Failed to update report_log: {response.get('message')} - {response.get('data')}") + return False + + @staticmethod + def delete_report_log(report_id): + """ + Delete all report_log entries for a given report_id. + """ + query = "DELETE FROM report_log WHERE report_id = %s;" + params = (report_id,) + req = {"query": query, "params": params} + response = DBClient.execute_modify_query(req, "postgres") + + if response.get("status") == 200: + return True + else: + oper_log('error', 'system', + f"Failed to delete report_log: {response.get('message')} - {response.get('data')}") + return False Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/services/generic_service.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/services/generic_service.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/services/generic_service.py (working copy) @@ -0,0 +1,32 @@ + + +from hive.db.generic_db_queries import DBManager + + +class ServiceManager: + """ + Service layer for handling service-type-based data retrieval and formatting. + """ + + VALID_SERVICE_TYPES = {"virtualService", "realService", "sslvpnService", "LLB"} + + @staticmethod + def fetch_services(service_type, device_ip=None): + """ + Validate input, call DB layer, and format response. + """ + + if not service_type: + return {"error": "Missing 'serviceType' in request"} + + if service_type not in ServiceManager.VALID_SERVICE_TYPES: + return {"error": f"Invalid serviceType '{service_type}'"} + + db_response = DBManager.get_services_query(service_type, device_ip) + + if not db_response: + return {"services": []} + + services = [row.get("service_name") for row in db_response if "service_name" in row] + + return {"services": services}