Index: /branches/amp_4_0/amp.spec =================================================================== --- /branches/amp_4_0/amp.spec (revision 2915) +++ /branches/amp_4_0/amp.spec (working copy) @@ -53,6 +53,8 @@ fi %post +%systemd_post report_scheduler.service +%systemd_post report_scheduler.timer python /ca/extensions/license_server/license_server_db.py -c bash /ca/extensions/monitoring/start.sh cp -r /ca/extensions/auditing/syslogd/kibana.yml /etc/kibana/kibana.yml || echo "Warning: Failed to copy kibana.yml" @@ -86,6 +88,7 @@ /bin/systemctl disable sshd >/dev/null 2>&1 || : /bin/systemctl enable cassh.service >/dev/null 2>&1 || : /bin/systemctl daemon-reload >/dev/null 2>&1 || : +/bin/systemctl enable report_scheduler.timer >/dev/null 2>&1 || : if [ "$1" = "1" ]; then create_res=`PGPASSWORD=Array@123$ psql -h 127.0.0.1 -p 5432 -U amp_admin --command "CREATE DATABASE cm;"` @@ -96,7 +99,11 @@ > /ca/etc/array fi + %preun +%systemd_preun report_scheduler.service +%systemd_preun report_scheduler.timer + if [ "$1" = "0" ]; then bash /ca/extensions/license_server/stop.sh python /ca/extensions/license_server/license_server_db.py -d @@ -110,6 +117,9 @@ fi %postun +%systemd_postun_with_restart report_scheduler.service +%systemd_postun report_scheduler.timer + if [ "$1" = "0" ]; then rm -rf /var/crash/license.info rm -rf /var/crash/timer.rec @@ -131,6 +141,7 @@ fi fi + %build make && cd extensions/license_server/license_server && make @@ -163,6 +174,8 @@ cp -a extensions/auditing/syslogd %{buildroot}/ca/extensions/auditing/ install -Dm 0644 extensions/auditing/syslogd/acm_syslogd.service %{buildroot}%{_unitdir}/acm_syslogd.service +install -Dm 0644 scripts/report_scheduler.service %{buildroot}%{_unitdir}/report_scheduler.service +install -Dm 0644 scripts/report_scheduler.timer %{buildroot}%{_unitdir}/report_scheduler.timer install -p -D -m 0755 extensions/avx_mgmt/metadata.json %{buildroot}/ca/extensions/avx_mgmt/metadata.json install -p -D -m 0755 extensions/avx_mgmt/start.sh %{buildroot}/ca/extensions/avx_mgmt/start.sh @@ -353,6 +366,8 @@ %attr (755,root,root)/ca/bin/check_crontab_add.py %attr (755,root,root)/ca/bin/clean_oper_log.py %attr (755,root,root)/ca/bin/amp_sync_postfix_smtp.sh +%attr (644,root,root)%{_unitdir}/report_scheduler.service +%attr (644,root,root)%{_unitdir}/report_scheduler.timer %define __debug_install_post \ %{_rpmconfigdir}/find-debuginfo.sh %{?_find_debuginfo_opts} "%{_builddir}/%{?buildsubdir}"\ Index: /branches/amp_4_0/platform/config/init_db.sql =================================================================== --- /branches/amp_4_0/platform/config/init_db.sql (revision 2915) +++ /branches/amp_4_0/platform/config/init_db.sql (working copy) @@ -407,22 +407,28 @@ ON DELETE NO ACTION ); -CREATE TABLE IF NOT EXISTS REPORT +CREATE TABLE IF NOT EXISTS report ( - id serial PRIMARY KEY, - name varchar(64) NOT NULL, - device_ip varchar(64) DEFAULT NULL, - device_type 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, - create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + id SERIAL PRIMARY KEY, + name VARCHAR(64) NOT NULL, + device_ip VARCHAR(64) DEFAULT NULL, + device_type 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(256) DEFAULT NULL, + status INTEGER DEFAULT 0, + -- Always store timestamps in UTC + create_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, + enabled BOOLEAN NOT NULL DEFAULT TRUE, + schedule_cron VARCHAR(64), + next_run TIMESTAMPTZ, + last_run TIMESTAMPTZ ); + CREATE TABLE IF NOT EXISTS REPORT_LOG ( id serial PRIMARY KEY, \ No newline at end of file Index: /branches/amp_4_0/scripts/amp-report-scheduler.service =================================================================== --- /branches/amp_4_0/scripts/amp-report-scheduler.service (nonexistent) +++ /branches/amp_4_0/scripts/amp-report-scheduler.service (working copy) @@ -0,0 +1,12 @@ +[Unit] +Description=AMP Scheduled Report Generator +After=backend.service postgresql.service + +[Service] +Type=oneshot +WorkingDirectory=/ca/webui/htdocs/new/src +Environment=PYTHONPATH=/ca/webui/htdocs/new/src +ExecStart=python3.13 /ca/webui/htdocs/new/src/hive/report/run_scheduled_reports.py + +[Install] +WantedBy=multi-user.target \ No newline at end of file Index: /branches/amp_4_0/scripts/amp-report-scheduler.timer =================================================================== --- /branches/amp_4_0/scripts/amp-report-scheduler.timer (nonexistent) +++ /branches/amp_4_0/scripts/amp-report-scheduler.timer (working copy) @@ -0,0 +1,10 @@ +[Unit] +Description=Run AMP scheduled reports every 5 minutes + +[Timer] +OnBootSec=2min +OnUnitActiveSec=5min +AccuracySec=1min + +[Install] +WantedBy=timers.target \ No newline at end of file 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 2915) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/generate_report.py (working copy) @@ -26,7 +26,7 @@ if path == 'save': return save_report(request) elif path == 'generate' and report_id: - return generate_report(request, report_id) + return generate_report(report_id) elif request.method == 'PUT' and report_id: return update_report(request, report_id) @@ -175,6 +175,8 @@ data.get("device_ip"), data.get("device_type", None), data.get("send_to", None), + data.get("enabled", True), + data.get("schedule_cron", None), ) if not report_id: @@ -275,7 +277,7 @@ yield chunk -def generate_report(request, report_id): +def generate_report(report_id): """ Generate Report by ID """ try: report_id = int(report_id) Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_data_service.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_data_service.py (revision 2915) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_data_service.py (working copy) @@ -336,6 +336,8 @@ updated_from_time = update_payload.get("from_time", existing.get("from_time")) updated_to_time = update_payload.get("to_time", existing.get("to_time")) updated_send_to = update_payload.get("send_to", existing.get("send_to")) + updated_enabled = update_payload.get("enabled", existing.get("enabled")) + updated_schedule_cron = update_payload.get("schedule_cron", existing.get("schedule_cron")) # 3. Update DB updated = ReportDB.update_report( @@ -348,6 +350,8 @@ updated_from_time, updated_to_time, updated_send_to, + updated_enabled, + updated_schedule_cron ) if not updated: 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 (revision 2905) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/report_queries.py (working copy) @@ -1,10 +1,13 @@ -from hive.db.db_client import DBClient -from hive.utils import andebug -from cm.lib.libbasic_operation import oper_log -import json import ast +import json +from cm.lib.libbasic_operation import oper_log +from hive.db.db_client import DBClient +from datetime import datetime, timezone +from croniter import croniter + + class ReportDB: @staticmethod @@ -22,7 +25,7 @@ # Fallback for Python string representation (single quotes) row['result'] = ast.literal_eval(row['result']) except (ValueError, SyntaxError): - pass # Keep as string if parsing fails + pass # Keep as string if parsing fails return data @staticmethod @@ -40,7 +43,9 @@ device_type, from_time, to_time, - send_to + send_to, + enabled, + schedule_cron FROM report ORDER BY id ASC; """ @@ -67,7 +72,9 @@ device_type, from_time, to_time, - send_to + send_to, + enabled, + schedule_cron FROM report WHERE id = {report_id}; """ @@ -81,53 +88,114 @@ return [] @staticmethod - def insert_report(name, subject_type, subject_name, from_time, to_time, device_ip=None, device_type=None, send_to=None): + def insert_report(name, subject_type, subject_name, from_time, to_time, device_ip=None, device_type=None, + send_to=None, enabled=True, schedule_cron=None): """ Insert a new report entry and return its id. """ query = """ - INSERT INTO report (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to) - VALUES (%s, %s, %s, %s, %s, %s, %s, %s) + INSERT INTO report (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to, enabled, schedule_cron) + VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id; """ - params = (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to) + params = (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to, enabled, schedule_cron) 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: + if response.get("status") != 200 or not response.get("data"): oper_log('error', 'system', f"Failed to insert report: {response.get('message')} - {response.get('data')}") return None + report_id = response["data"][0]["id"] + + # ------------------------------------------------- + # Initialize next_run if scheduling is enabled + # ------------------------------------------------- + if schedule_cron: + try: + now = datetime.now(timezone.utc) + itr = croniter(schedule_cron, now) + next_run = itr.get_next(datetime) + + update_query = """ + UPDATE report + SET next_run = %s + WHERE id = %s; + """ + + DBClient.execute_modify_query( + {"query": update_query, "params": (next_run, report_id)}, + "postgres" + ) + + except Exception as e: + oper_log( + "error", + "report", + f"Failed to initialize next_run for report {report_id}: {str(e)}" + ) + + return report_id + @staticmethod - def update_report(report_id, name, subject_type, subject_name, device_ip, device_type, 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, - device_type = %s, - from_time = %s, - to_time = %s, - send_to = %s - WHERE id = %s; - """ - params = (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to, report_id) - req = {"query": query, "params": params} - response = DBClient.execute_modify_query(req, "postgres") + def update_report(report_id, name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to, + enabled, schedule_cron): + from datetime import datetime, timezone + from croniter import croniter - if response.get("status") == 200: - return True + # ------------------------------------------------- + # Determine next_run logic + # ------------------------------------------------- + if enabled and schedule_cron: + try: + now = datetime.now(timezone.utc) + itr = croniter(schedule_cron, now) + next_run = itr.get_next(datetime) + except Exception as e: + oper_log( + "error", + "report", + f"Invalid cron expression for report {report_id}: {str(e)}" + ) + return False else: - oper_log('error', 'system', f"Failed to update report: {response.get('message')} - {response.get('data')}") + # Disabled OR no cron → clear next_run + next_run = None + + query = """ + UPDATE report + SET + name = %s, + subject_type = %s, + subject_name = %s, + device_ip = %s, + device_type = %s, + from_time = %s, + to_time = %s, + send_to = %s, + enabled = %s, + schedule_cron = %s, + next_run = %s + WHERE id = %s; + """ + + params = (name, subject_type, subject_name, device_ip, device_type, from_time, to_time, send_to, enabled, + schedule_cron, next_run, report_id) + + response = DBClient.execute_modify_query( + {"query": query, "params": params}, + "postgres" + ) + + if response.get("status") != 200: + oper_log( + "error", + "system", + f"Failed to update report: {response.get('message')} - {response.get('data')}" + ) return False + return True @staticmethod def update_report_status(status, report_id): @@ -153,21 +221,137 @@ @staticmethod def delete_report(report_id): """ - Delete a report by ID. + Delete a report and its associated logs by ID. """ - query = "DELETE FROM report WHERE id = %s;" - params = (report_id,) - req = {"query": query, "params": params} - response = DBClient.execute_modify_query(req, "postgres") - affected_rows = response.get("data", {}).get("affected_rows", 0) - if response.get("status") == 200 and affected_rows > 0: + # First delete dependent logs (if no ON DELETE CASCADE in DB) + delete_logs_query = "DELETE FROM report_log WHERE report_id = %s;" + delete_report_query = "DELETE FROM report WHERE id = %s;" + + try: + # Delete logs + DBClient.execute_modify_query( + {"query": delete_logs_query, "params": (report_id,)}, + "postgres" + ) + + # Delete report + response = DBClient.execute_modify_query( + {"query": delete_report_query, "params": (report_id,)}, + "postgres" + ) + + if response.get("status") != 200: + oper_log( + 'error', + 'system', + f"DB error while deleting report {report_id}: " + f"{response.get('message')} - {response.get('data')}" + ) + return False + + affected_rows = response.get("data", {}).get("affected_rows", 0) + + if affected_rows == 0: + oper_log( + 'warning', + 'system', + f"Report {report_id} not found." + ) + return False + return True - else: - oper_log('error', 'system', f"Report {report_id} not found or already deleted or DB error") + + except Exception as e: + oper_log( + 'error', + 'system', + f"Unexpected error while deleting report {report_id}: {str(e)}" + ) return False + + @staticmethod + def get_due_reports(now: datetime): + """ + Fetch all reports that are due for execution. + """ + + query = """ + SELECT + id, + name, + schedule_cron, + next_run + FROM report + WHERE enabled = true + AND schedule_cron IS NOT NULL + AND next_run <= %(now)s + """ + params = {"now": now} + + req = { + "query": query, + "params": params + } + + response = DBClient.execute_query(req, "postgres") + + # Normalize empty response + if response.get("status") == 200: + return response.get("data", []) + else: + oper_log( + "error", + "report", + f"Failed to fetch due reports: {response.get('message')}" + ) + return [] + @staticmethod + def update_next_run(report_id: int) -> None: + # Fetch schedule + query = """ + SELECT schedule_cron + FROM report + WHERE id = %(id)s + AND enabled = true + FOR UPDATE + """ + + resp = DBClient.execute_query({ + "query": query, + "params": {"id": report_id} + }, "postgres") + + if resp.get("status") != 200 or not resp.get("data"): + return + + schedule_cron = resp["data"][0]["schedule_cron"] + + # Always compute in UTC + now = datetime.now(timezone.utc) + + itr = croniter(schedule_cron, now) + next_run = itr.get_next(datetime) + + update_query = """ + UPDATE report + SET last_run = %(last_run)s, + next_run = %(next_run)s + WHERE id = %(id)s + """ + + DBClient.execute_modify_query({ + "query": update_query, + "params": { + "id": report_id, + "last_run": now, + "next_run": next_run + } + }, "postgres") + + @staticmethod def get_all_report_log(): """ Fetch report log entries by report_id. @@ -192,8 +376,6 @@ f"Failed to fetch report_log: {response.get('message')} - {response.get('data')}") return [] - - @staticmethod def delete_report_log(log_id): """ @@ -238,7 +420,6 @@ f"Failed to fetch report_log: {response.get('message')} - {response.get('data')}") return [] - @staticmethod def get_log_by_report_id(report_id): """ Index: /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/run_scheduled_reports.py =================================================================== --- /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/run_scheduled_reports.py (nonexistent) +++ /branches/amp_4_0/src/webui/webui/htdocs/new/src/hive/report/run_scheduled_reports.py (working copy) @@ -0,0 +1,19 @@ +import datetime +from cm.lib.libbasic_operation import oper_log +from hive.report.report_queries import ReportDB +from hive.report.generate_report import generate_report +import sys +sys.path.append('/ca/webui/htdocs/new/src') + +now = datetime.utcnow() + +due_reports = ReportDB.get_due_reports(now) + +for report in due_reports: + try: + generate_report(report["id"]) + ReportDB.update_next_run(report["id"]) + except Exception as e: + oper_log("error", "report", f"Scheduled report failed: {e}") + +