Policy Compliance Audit Log Analysis and Incident Reporting Script Builder

Role: Principal Compliance Data Analyst specializing in governance reporting automation.

Context

• Database engine: {{audit_log_database_engine}}
• Compliance standard: {{compliance_framework_standard}}
• Target role scope: {{monitored_user_roles}}
• Audit lookback window: {{violation_lookback_window}}
• Severity taxonomy: {{incident_severity_classification}}
• Alert endpoint: {{notification_webhook_target}}

Task

Develop an automated SQL extraction query and Python wrapper script that scans security event logs, identifies unauthorized permission escalations and policy breaches according to compliance requirements, categorizes incidents by risk tier, and produces a structured summary report sent to an incident webhook.

Method

1. Formulate a parameterized SQL extraction query tailored for {{audit_log_database_engine}}.
2. Filter access logs across the designated {{violation_lookback_window}} specifically isolating {{monitored_user_roles}}.
3. Implement parsing logic against policy evaluation rules defined by {{compliance_framework_standard}}.
4. Map raw violation patterns against {{incident_severity_classification}} to establish risk scores.
5. Aggregate incident totals by role category, resource path, and root-cause event code.
6. Compile high-severity findings into a concise markdown-formatted digest payload.
7. Dispatch the payload via HTTP POST to {{notification_webhook_target}} with retry logic.

Constraints

• Queries MUST use parameterized inputs to prevent SQL injection.
• MUST NOT expose raw passwords, user secrets, or personal identifiable information (PII) in output payloads.
• Script MUST exit cleanly with code 0 on zero violations and code 1 on unhandled database errors.
• Log volume estimation must include index utilization recommendations for performance optimization.

Output format

Provide the response structured as follows:

1. SQL Audit Extraction Query (clean, optimized SQL block with parameter placeholders).
2. Python Dispatcher Script (production-ready Python script handling extraction, classification, and webhook dispatch).
3. Payload Schema (JSON sample of the summary incident digest).

Self-review

• Verify that the SQL query matches syntax rules for {{audit_log_database_engine}}.
• Confirm that severity classification logic covers all edge cases specified in {{incident_severity_classification}}.
• Ensure webhook timeout and retry mechanics are explicitly declared in the Python script.