Build an automated SLA breach alerting system for vulnerability remediation, including a database schema for SLA tracking, breach detection logic, notification dispatch, a scheduled check runner, and a KPI/compliance metrics dashboard. Use when implementing severity-based SLA timelines (critical/high/medium/low), detecting and escalating SLA breaches, or building vulnerability remediation compliance reporting.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Build an automated SLA breach alerting system for vulnerability remediation, including a database schema for SLA tracking, breach detection logic, notification dispatch, a scheduled check runner, and a KPI/compliance metrics dashboard. Use when implementing severity-based SLA timelines (critical/high/medium/low), detecting and escalating SLA breaches, or building vulnerability remediation compliance reporting.
Vulnerability remediation SLAs define maximum timeframes for addressing security findings based on severity. This skill covers building an automated alerting system that tracks remediation timelines, detects SLA breaches, sends escalation notifications, and generates compliance reports. Industry-standard SLA targets are: Critical (24-48 hours), High (15-30 days), Medium (60 days), Low (90 days).
When to Use
When deploying or configuring implementing vulnerability sla breach alerting capabilities in your environment
When establishing security controls aligned to compliance requirements
When building or improving security architecture for this domain
When conducting security assessments that require this implementation
Prerequisites
Python 3.9+ with requests, pandas, jinja2, smtplib libraries
Vulnerability management platform with API access (DefectDojo, Qualys, Tenable)
SMTP server or webhook endpoint (Slack, Microsoft Teams, PagerDuty)
CREATE TABLE vulnerability_sla (
id SERIAL PRIMARY KEY,
cve_id VARCHAR(20) NOT NULL,
finding_id VARCHAR(100) NOT NULL,
asset_hostname VARCHAR(255),
severity VARCHAR(20) NOT NULL,
cvss_score DECIMAL(3,1),
discovered_at TIMESTAMPNOT NULL,
sla_deadline TIMESTAMPNOT NULL,
remediated_at TIMESTAMP,
status VARCHAR(20) DEFAULT'open',
owner_email VARCHAR(255),
escalation_level INTEGERDEFAULT0,
last_alert_sent TIMESTAMP,
created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP
);
CREATE INDEX idx_sla_status ON vulnerability_sla(status);
CREATE INDEX idx_sla_deadline ON vulnerability_sla(sla_deadline);
CREATE INDEX idx_sla_severity ON vulnerability_sla(severity);
Step 2: SLA Breach Detection Logic
from datetime import datetime, timedelta, timezone
import yaml
defload_sla_policy(policy_path="sla_policy.yaml"):
withopen(policy_path, "r") as f:
return yaml.safe_load(f)
defget_sla_tier(cvss_score, policy):
for tier_name, tier in policy["sla_tiers"].items():
if tier["cvss_min"] <= cvss_score <= tier["cvss_max"]:
return tier_name, tier
return"low", policy["sla_tiers"]["low"]
defcalculate_sla_deadline(discovered_at, cvss_score, policy):
tier_name, tier = get_sla_tier(cvss_score, policy)
deadline = discovered_at + timedelta(days=tier["remediation_days"])
return deadline, tier_name
defcheck_sla_status(discovered_at, sla_deadline, remediated_at=None):
now = datetime.now(timezone.utc)
if remediated_at:
if remediated_at <= sla_deadline:
return"remediated_within_sla"return"remediated_breach"if now > sla_deadline:
overdue_days = (now - sla_deadline).days
returnf"breached_{overdue_days}d_overdue"
remaining = sla_deadline - now
total_sla = sla_deadline - discovered_at
pct_elapsed = ((total_sla - remaining) / total_sla) * 100if pct_elapsed >= 80:
return"approaching_breach"return"within_sla"
defcalculate_sla_metrics(db_connection, period_start, period_end):
metrics = {
"total_findings": 0,
"remediated_within_sla": 0,
"sla_breach_count": 0,
"mean_time_to_remediate": {},
"sla_compliance_rate": 0.0,
"current_overdue": 0,
}
# Query findings in period grouped by severity
query = """
SELECT severity, COUNT(*) as total,
SUM(CASE WHEN remediated_at <= sla_deadline THEN 1 ELSE 0 END) as within_sla,
AVG(EXTRACT(EPOCH FROM (COALESCE(remediated_at, NOW()) - discovered_at))/86400) as avg_days
FROM vulnerability_sla
WHERE discovered_at BETWEEN %s AND %s
GROUP BY severity
"""return metrics
