Index: /branches/amp_4_0/platform/config/init_db.sql =================================================================== --- /branches/amp_4_0/platform/config/init_db.sql (revision 2646) +++ /branches/amp_4_0/platform/config/init_db.sql (working copy) @@ -396,4 +396,27 @@ ON UPDATE NO ACTION ON DELETE NO ACTION ); + +CREATE TABLE IF NOT EXISTS REPORT +( + id serial PRIMARY KEY, + name varchar(64) NOT NULL, + args TEXT DEFAULT NULL, + timeout integer DEFAULT 0, + tags varchar(64) DEFAULT NULL, + status integer DEFAULT 0, + 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, + start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + end_time TIMESTAMP DEFAULT NULL, + status integer DEFAULT 0, + result TEXT DEFAULT NULL, + FOREIGN KEY (report_id) REFERENCES REPORT(id) ON DELETE CASCADE, +); -- psql -U amp_admin -d cm -f /path/to/your/init_db.sql Index: /branches/amp_4_0/scripts/fetch_update_db_data.py =================================================================== --- /branches/amp_4_0/scripts/fetch_update_db_data.py (nonexistent) +++ /branches/amp_4_0/scripts/fetch_update_db_data.py (working copy) @@ -0,0 +1,133 @@ +import json +import psycopg2 +from influxdb_client import InfluxDBClient +from datetime import datetime, timedelta + +# === PostgreSQL Configuration === +PG_DB = "cm" +PG_USER = "amp_admin" +PG_PASS = "Array@123$" +PG_HOST = "localhost" +PG_PORT = "5432" + +# === InfluxDB Configuration === +INFLUX_URL = "http://localhost:8086" +INFLUX_TOKEN = "5vTtwB8fWzP9rkaLU8aaNnkM-RJzPl9yHdm1N28NdOtIyVvJTyb9JgyMjnAsHTLBlVtEQ7UMOencnJgHMvFabQ==" +ORG = "AN" +BUCKET = "AMP" + +conn = psycopg2.connect( + dbname=PG_DB, user=PG_USER, password=PG_PASS, + host=PG_HOST, port=PG_PORT +) + + +def get_report_info_from_db(name): + cur = conn.cursor() + cur.execute("""SELECT id, args, tags FROM report where name=%s""", (name,)) + row = cur.fetchone() + cur.close() + + if row: + id, args_json, tags_json = row + args = json.loads(args_json) + tags = json.loads(tags_json) + return id, args, tags + return None, {}, [] + + +def insert_report_log(report_id, name, tags): + with conn.cursor() as cur: + cur.execute(""" + INSERT INTO report_log (report_id, name, tags, status) + VALUES (%s, %s, %s, 0) + RETURNING id; + """, (report_id, name, tags)) + conn.commit() + return cur.fetchone()[0] + + +def update_report_log(name, status, filename, file_size): + result = { + "file_name": filename, + "format": "pdf", + "language": "en", + "message": "success" if status == 1 else "failure", + "size": file_size + } + with conn.cursor() as cur: + cur.execute(""" + UPDATE report_log + SET end_time = CURRENT_TIMESTAMP, + status = %s, + result = %s + WHERE name = %s; + """, (status, json.dumps(result), name)) + conn.commit() + + +def run_query(query): + client = InfluxDBClient(url=INFLUX_URL, token=INFLUX_TOKEN, org=ORG) + query_api = client.query_api() + tables = query_api.query(query) + results = [] + for table in tables: + for record in table.records: + record["time"] = record["_time"] + results.append(record.values) + return results + + +def fetch_influx_data_with_range(start_time, end_time): + byte_query = f''' +from(bucket: "AMP") + |> range(start: {start_time}, stop: {end_time}) + |> filter(fn: (r) => + r._measurement == "apv_virtual_stats" and + (r._field == "InBytePerSec" or r._field == "OutBytePerSec")) + |> aggregateWindow(every: 1h, fn: last, createEmpty: false) + |> pivot(rowKey: ["_time", "ServerId", "agent_host"], columnKey: ["_field"], valueColumn: "_value") + |> map(fn: (r) => ({{ + "_time": r._time, + "ServerId": r.ServerId, + "agent_host": r.agent_host, + "InBytePerSec_bps": float(v: r.InBytePerSec) * 8.0, + "OutBytePerSec_bps": float(v: r.OutBytePerSec) * 8.0 + }})) +''' + + conn_query = f''' +from(bucket: "AMP") + |> range(start: {start_time}, stop: {end_time}) + |> filter(fn: (r) => + r._measurement == "apv_virtual_stats" and r._field == "ConnCnt") + |> aggregateWindow(every: 1h, fn: last, createEmpty: false) + |> pivot(rowKey: ["_time", "ServerId", "agent_host"], columnKey: ["_field"], valueColumn: "_value") + |> map(fn: (r) => ({{ + "_time": r._time, + "ServerId": r.ServerId, + "agent_host": r.agent_host, + "ConnCnt": r.ConnCnt + }})) +''' + # Update the time interval Calculation when implementing the actual PDF (Refer LLBStatsDB : get_time_range) + print(byte_query) + print(conn_query) + byte_data = run_query(byte_query) + conncnt_data = run_query(conn_query) + return conncnt_data, byte_data + + +# === Test Run === +if __name__ == "__main__": + now = datetime.utcnow() + start = now - timedelta(minutes=5) + conncnt_data, byte_data = fetch_influx_data_with_range(start.isoformat() + "Z", now.isoformat() + "Z") + + print("Byte/sec Data:") + for row in byte_data: # sample + print(row) + + print("\nConnCnt Data:") + for row in conncnt_data: # sample + print(row) Index: /branches/amp_4_0/scripts/generate_pdf_report.py =================================================================== --- /branches/amp_4_0/scripts/generate_pdf_report.py (nonexistent) +++ /branches/amp_4_0/scripts/generate_pdf_report.py (working copy) @@ -0,0 +1,214 @@ +# generate_pdf_report.py + +import os +from reportlab.lib.pagesizes import A4 +from reportlab.lib.units import inch +from reportlab.pdfgen import canvas +from reportlab.lib import colors +from datetime import datetime, timezone + +from fetch_update_db_data import get_report_info_from_db, insert_report_log, update_report_log, \ + fetch_influx_data_with_range + + +def draw_section(c, y, title, data, page_number): + if not data: + return y, page_number + + width, height = A4 + is_conn_cnt = "InBytePerSec_bps" not in data[0] + + def draw_table_header(y_pos): + c.setFont("Helvetica-Bold", 10) + c.drawString(40, y_pos, "Time") + if is_conn_cnt: + c.drawString(220, y_pos, "Concurrent Connections") + else: + c.drawString(220, y_pos, "Inbound (bps)") + c.drawString(380, y_pos, "Outbound (bps)") + return y_pos - 20 + + # Section title + c.setFont("Helvetica-Bold", 12) + c.drawString(40, y, title) + y -= 20 + + # Server Info + server_id = data[0].get("ServerId", "N/A") + agent_host = data[0].get("agent_host", "N/A") + + c.setFont("Helvetica", 11) + c.drawString(40, y, f"Server ID: {server_id}") + y -= 16 + c.drawString(40, y, f"Host: {agent_host}") + y -= 24 + + # Draw initial table header + y = draw_table_header(y) + + # Table content + c.setFont("Helvetica", 10) + for record in data: + if y < 60: + # Page footer + c.setFont("Helvetica", 9) + c.setFillColor(colors.gray) + c.drawRightString(width - 40, 30, f"Page {page_number}") + c.setFillColor(colors.black) + + c.showPage() + page_number += 1 + y = height - 60 + + # Redraw header + y = draw_table_header(y) + y -= 6 + + # Time formatting + time_val = record.get("_time") + time_str = time_val.astimezone().strftime("%H:%M:%S") if isinstance(time_val, datetime) else str(time_val) + + c.setFont("Helvetica", 10) + c.drawString(40, y, time_str) + if is_conn_cnt: + c.drawString(240, y, str(record.get("ConnCnt", ""))) + else: + c.drawString(240, y, str(record.get("InBytePerSec_bps", ""))) + c.drawString(400, y, str(record.get("OutBytePerSec_bps", ""))) + y -= 18 + + return y, page_number + + +def generate_pdf(conncnt_data, byte_data, metadata, filename="influx_report.pdf"): + c = canvas.Canvas(filename, pagesize=A4) + width, height = A4 + page_number = 1 # Start page number + + # === Extract metadata === + if metadata: + service_name = metadata.get("service_name", "N/A") + service_type = metadata.get("service_type", "N/A") + device_ip = metadata.get("device_ip", "N/A") + from_time = metadata.get("from_time", "N/A") + to_time = metadata.get("to_time", "N/A") + device_name = metadata.get("device_name", "vAPV1") + else: + service_name = "vs1" + service_type = "SLB_vs" + device_ip = "192.168.85.101" + from_time = "N/A" + to_time = "N/A" + device_name = "vAPV1" + + # Logo and Header + c.drawImage("Array-Logo-en-2024.jpg", x=40, y=A4[1] - 80, width=1.5 * inch, height=0.5 * inch) + + c.setFont("Helvetica-Bold", 14) + c.setFillColor("#F26C20") + c.drawString(220, height - 90, "Service Status Report") + + c.setFont("Helvetica", 10) + c.setFillColor(colors.gray) + c.drawRightString(width - 40, height - 50, + f"Report Generated on {datetime.now(timezone.utc).strftime('%a, %d %B %Y %H:%M:%S UTC')}") + + c.setFillColor(colors.black) + c.setStrokeColor(colors.lightgrey) + c.line(40, height - 100, width - 40, height - 100) + + # Service Info + c.setFont("Helvetica", 11) + y = height - 120 + c.drawString(40, y, f"Service Name: {service_name}") + y -= 15 + c.drawString(40, y, f"Service Type: {service_type}") + y -= 15 + c.drawString(40, y, f"Device Name: {device_name}") + y -= 15 + c.drawString(40, y, f"Device IP: {device_ip}") + y -= 15 + c.drawString(40, y, f"From: {from_time}") + y -= 15 + c.drawString(40, y, f"To: {to_time}") + y -= 50 + + # Sections with page tracking + y, page_number = draw_section(c, y, "Connection Count", conncnt_data, page_number) + y -= 50 + y, page_number = draw_section(c, y, "Bandwidth Usage (Bytes/sec)", byte_data, page_number) + + # Final page number + c.setFont("Helvetica", 9) + c.setFillColor(colors.gray) + c.drawRightString(width - 40, 30, f"Page {page_number}") + + c.save() + print(f"PDF saved to {filename}") + + +def generate_report(): + id, args, tags = get_report_info_from_db("report1") + if not args: + print("No report data found in DB.") + return + + service_name = args["subject"]["name"] + service_type = args["subject"]["origin_type"] + device_ip = args["device_ip"] + from_time_raw = args["from"] + to_time_raw = args["to"] + device_name = "vAPV1" # Placeholder + + from_time = get_time_in_influx_format(from_time_raw) + to_time = get_time_in_influx_format(to_time_raw) + + print(f"Fetching InfluxDB data from {from_time} to {to_time}...") + + conncnt_data, byte_data = fetch_influx_data_with_range(from_time, to_time) + + metadata = { + "service_name": service_name, + "service_type": service_type, + "device_ip": device_ip, + "from_time": from_time_raw, + "to_time": to_time_raw, + "device_name": device_name + } + + insert_report_log(id, "report1", tags) + filename = f"{service_name}_report.pdf" + generate_pdf(conncnt_data, byte_data, metadata, filename) + file_size = os.path.getsize(filename) + update_report_log("report1", 1, filename, file_size) + + +@staticmethod +def get_time_in_influx_format(time_str): + time_mappings = { + "now": "now()", + "now-10m": "-10m", + "now-15m": "-15m", + "now-30m": "-30m", + "now-1h": "-1h", + "now-3h": "-3h", + "now-6h": "-6h", + "now-12h": "-12h", + "now-24h": "-24h", + "now-2d": "-2d", + "now-7d": "-7d", + "now-1w": "-7d", # 1w not allowed in Flux durations, use days + "now-30d": "-30d", + "now-60d": "-60d", + "now-90d": "-90d", + "now-180d": "-180d", + "now-365d": "-365d", + "now-730d": "-730d", + "now-1825d": "-1825d" + } + + return time_mappings.get(time_str, -1) + + +if __name__ == "__main__": + generate_report()