Learning From Failure and Continuous Improvement Questions

from datetime import datetime, timedelta, timezone
import re

LOG_RE = re.compile(r'^(?P<ts>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z)\s+.*\blevel=(?P<level>\w+)\b')

def parse_logs(log_lines, window_minutes, error_threshold):
    """
    Return True if errors-per-minute over the last window_minutes (relative to latest log)
    exceeds error_threshold.
    """
    timestamps = []
    levels = []

    for line in log_lines:
        m = LOG_RE.search(line)
        if not m:
            continue  # ignore malformed lines
        ts = datetime.strptime(m.group('ts'), "%Y-%m-%dT%H:%M:%SZ").replace(tzinfo=timezone.utc)
        timestamps.append(ts)
        levels.append(m.group('level').upper())

    if not timestamps:
        return False

    latest = max(timestamps)
    window_start = latest - timedelta(minutes=window_minutes)

    error_count = sum(1 for ts, lvl in zip(timestamps, levels) if lvl == 'ERROR' and ts >= window_start)

    error_rate = error_count / float(window_minutes) if window_minutes > 0 else float('inf')
    return error_rate > error_threshold

WITH recent AS (
  SELECT
    service,
    AVG(EXTRACT(EPOCH FROM (resolved_at - started_at))) / 60.0 AS mttr_minutes,
    COUNT(*) AS incidents
  FROM incidents
  WHERE resolved_at IS NOT NULL
    AND started_at >= now() - INTERVAL '90 days'
  GROUP BY service
)
SELECT service, mttr_minutes, incidents
FROM recent
ORDER BY mttr_minutes DESC;

WITH recent AS (
  SELECT service,
         AVG(EXTRACT(EPOCH FROM (resolved_at - started_at))) / 60.0 AS mttr_recent,
         COUNT(*) AS cnt_recent
  FROM incidents
  WHERE resolved_at IS NOT NULL
    AND started_at >= now() - INTERVAL '90 days'
  GROUP BY service
),
prior AS (
  SELECT service,
         AVG(EXTRACT(EPOCH FROM (resolved_at - started_at))) / 60.0 AS mttr_prior,
         COUNT(*) AS cnt_prior
  FROM incidents
  WHERE resolved_at IS NOT NULL
    AND started_at >= now() - INTERVAL '180 days'
    AND started_at <  now() - INTERVAL '90 days'
  GROUP BY service
)
SELECT r.service, r.mttr_recent, p.mttr_prior, r.cnt_recent, p.cnt_prior
FROM recent r
JOIN prior p ON r.service = p.service
WHERE p.mttr_prior > 0
  AND r.mttr_recent > 1.5 * p.mttr_prior
  AND r.cnt_recent >= 5   -- optional: require minimum samples
  AND p.cnt_prior  >= 5
ORDER BY (r.mttr_recent / p.mttr_prior) DESC;