Step 7: The report, and the same questions in SQL

Package the analysis in one function that returns the headline numbers, and print them as a report. A function can be tested and run on tomorrow's log.

Examplepython

import re
from collections import Counter, defaultdict

LOG = """2026-11-05 09:00:01 10.0.0.5 GET /home 200 120
2026-11-05 09:00:03 10.0.0.9 GET /login 200 80
2026-11-05 09:00:07 10.0.0.9 POST /login 401 95
2026-11-05 09:00:09 10.0.0.9 POST /login 401 91
2026-11-05 09:00:12 10.0.0.9 POST /login 401 99
2026-11-05 09:00:15 10.0.0.9 POST /login 401 97
2026-11-05 09:01:02 10.0.0.5 GET /courses 200 340
2026-11-05 09:01:20 10.0.0.7 GET /courses/python 200 410
2026-11-05 09:01:44 10.0.0.7 GET /missing 404 30
2026-11-05 09:02:05 10.0.0.5 GET /api/progress 500 1200
2026-11-05 09:02:09 10.0.0.5 GET /api/progress 500 1180
2026-11-05 09:02:30 10.0.0.8 GET /home 200 110
2026-11-05 09:03:10 10.0.0.8 GET /courses 200 360
2026-11-05 09:03:12 10.0.0.7 GET /home 200 105
2026-11-05 09:03:50 10.0.0.6 GET /api/progress 200 220
this line is not a valid log entry
2026-11-05 09:04:01 10.0.0.6 GET /home 200 98
2026-11-05 09:04:33 10.0.0.7 GET /courses/sql 200 395
2026-11-05 09:05:02 10.0.0.8 GET /missing 404 28"""

PATTERN = re.compile(
    r"^(?P<date>\d{4}-\d{2}-\d{2}) (?P<time>\d{2}:\d{2}:\d{2}) "
    r"(?P<ip>\S+) (?P<method>[A-Z]+) (?P<path>\S+) (?P<status>\d{3}) (?P<ms>\d+)$"
)

def parse(text):
    entries = []
    for line in text.splitlines():
        m = PATTERN.match(line)
        if m:
            e = m.groupdict()
            e["status"], e["ms"] = int(e["status"]), int(e["ms"])
            entries.append(e)
    return entries

entries = parse(LOG)

def analyse(entries):
    errors = [e for e in entries if e["status"] >= 400]
    failed = Counter(e["ip"] for e in entries if e["path"] == "/login" and e["status"] == 401)
    slow = max(entries, key=lambda e: e["ms"])
    return {
        "requests": len(entries),
        "error_rate": round(len(errors) / len(entries), 3),
        "top_path": Counter(e["path"] for e in entries).most_common(1)[0][0],
        "slowest": f"{slow['path']} ({slow['ms']} ms)",
        "suspicious_ips": sorted(ip for ip, n in failed.items() if n >= 3),
    }

for key, value in analyse(entries).items():
    print(f"{key:>14}: {value}")

Open in editor »

For big logs the data would live in a database. Every question above is also a short SQL query. Compare them with the Python versions:

Examplesql

CREATE TABLE requests (id INTEGER PRIMARY KEY, at TEXT, ip TEXT, method TEXT, path TEXT, status INTEGER, ms INTEGER);
INSERT INTO requests (at, ip, method, path, status, ms) VALUES
  ('09:00:01', '10.0.0.5', 'GET', '/home', 200, 120), ('09:00:03', '10.0.0.9', 'GET', '/login', 200, 80),
  ('09:00:07', '10.0.0.9', 'POST', '/login', 401, 95), ('09:00:09', '10.0.0.9', 'POST', '/login', 401, 91),
  ('09:00:12', '10.0.0.9', 'POST', '/login', 401, 99), ('09:01:02', '10.0.0.5', 'GET', '/courses', 200, 340),
  ('09:02:05', '10.0.0.5', 'GET', '/api/progress', 500, 1200), ('09:02:09', '10.0.0.5', 'GET', '/api/progress', 500, 1180),
  ('09:03:10', '10.0.0.8', 'GET', '/courses', 200, 360), ('09:05:02', '10.0.0.8', 'GET', '/missing', 404, 28);

SELECT status / 100 || 'xx' AS class, COUNT(*) AS n FROM requests GROUP BY status / 100 ORDER BY class;

SELECT path, COUNT(*) AS hits, ROUND(AVG(ms)) AS avg_ms FROM requests GROUP BY path ORDER BY avg_ms DESC;

SELECT ip, COUNT(*) AS failed_logins FROM requests WHERE path = '/login' AND status = 401 GROUP BY ip HAVING COUNT(*) >= 3;

Open in editor »

GROUP BY replaces the Counter, AVG replaces the manual sum and divide, and HAVING COUNT(*) >= 3 is the suspicious-IP threshold. You have built a log analyser: parsing, counting, percentiles, time buckets, simple security detection and a report. Where next: stream the file line by line instead of loading it whole, chart the numbers, and alert by email when the error rate passes a limit.

Quick check

Which SQL clause is the equivalent of the threshold check for failed logins?

HAVING COUNT(*) >= 3LIMIT 3ORDER BY 3

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.