Security Findings Management and Remediation Tracking Questions

Approach

Turn each finding into a ticket dictionary, map severity to a numeric priority (1 is most urgent), and compute a dedup key for every finding. Two findings with the same key describe the same issue on the same host, so they become one ticket that collects both sources and evidence. Keys of tickets that already exist are passed in so a later scan run does not reopen a second ticket.

Code (run it as-is: nothing is random, and the hash is deterministic)

import hashlib
import ipaddress
import re

SEVERITY_TO_PRIORITY = {"critical": 1, "high": 2, "medium": 3, "low": 4, "info": 5}


def dedup_key(finding):
    """Same issue on the same asset gives the same key, whichever scanner reported it."""
    cve = (finding.get("cve") or "").strip().upper()
    if cve:
        issue = cve
    else:
        issue = re.sub(r"\s+", " ", (finding.get("title") or "").strip().lower())
    ip = str(ipaddress.ip_address(finding["asset_ip"].strip()))
    return hashlib.sha256(f"{issue}|{ip}".encode()).hexdigest()[:12]   # 12 hex chars: short label, fine for a demo


def map_findings_to_tickets(findings, existing_keys=None):
    """Return one ticket dict per distinct issue; skip keys that already have a ticket."""
    known = set(existing_keys or ())
    tickets = {}
    for f in findings:
        key = dedup_key(f)
        priority = SEVERITY_TO_PRIORITY.get((f.get("severity") or "").strip().lower())
        if key in known:
            continue
        if key in tickets:
            t = tickets[key]
            t["sources"].append(f["scanner_id"])
            t["evidence"].append(f["evidence"])
            if priority is not None and (t["priority"] is None or priority < t["priority"]):
                t["priority"] = priority
            continue
        tickets[key] = {
            "dedup_key": key,
            "summary": f"{f['title']} on {f['asset_ip']}",
            "description": f["description"],
            "priority": priority,
            "sources": [f["scanner_id"]],
            "evidence": [f["evidence"]],
        }
    for t in tickets.values():
        if t["priority"] is None:
            t["priority"] = 3
            t["needs_triage"] = True
    return list(tickets.values())


if __name__ == "__main__":
    findings = [
        {"scanner_id": "scanA", "title": "Log4Shell RCE", "description": "JNDI lookup", "severity": "critical",
         "cve": "CVE-2021-44228", "asset_ip": "10.0.0.5", "evidence": "scanA: payload echoed"},
        {"scanner_id": "scanB", "title": "Apache Log4j remote code execution", "description": "JNDI lookup",
         "severity": "High", "cve": "cve-2021-44228", "asset_ip": "10.0.0.5", "evidence": "scanB: DNS callback"},
        {"scanner_id": "scanA", "title": "Weak TLS  cipher", "description": "RC4 offered", "severity": "medium",
         "cve": "", "asset_ip": "10.0.0.6", "evidence": "scanA: RC4 accepted"},
        {"scanner_id": "scanA", "title": "Server banner", "description": "Version leak", "severity": "weird",
         "cve": None, "asset_ip": "10.0.0.6", "evidence": "scanA: banner"},
    ]
    first = map_findings_to_tickets(findings)
    for t in first:
        print(t["dedup_key"], t["priority"], t["sources"], t.get("needs_triage", False))
    print("rerun with known keys ->", len(map_findings_to_tickets(findings, {t["dedup_key"] for t in first})))

Output:

7b3b3521bbd5 1 ['scanA', 'scanB'] False
0ac7fb66114e 3 ['scanA'] False
170c7ba40593 3 ['scanA'] True
rerun with known keys -> 0

Walking the 4 input findings to 3 tickets

1. scanA reports "Log4Shell RCE" (Log4Shell is a critical 2021 flaw in the Java logging library Log4j, where a crafted string makes the server run attacker code, hence RCE, remote code execution; the JNDI lookup in the description is the Java feature abused) on 10.0.0.5, CVE-2021-44228, critical. Key = hash of CVE-2021-44228|10.0.0.5 (7b3b...). New ticket, priority 1.
2. scanB reports the same flaw under a different title, with the CVE typed in lowercase and severity High. .upper() turns the CVE back into CVE-2021-44228, so the key is the same. It is merged: sources become scanA and scanB, evidence has two entries, and priority stays 1 because the merge keeps the smaller (more urgent) number.
3. scanA reports "Weak TLS cipher" (two spaces; RC4 is an old encryption algorithm now considered broken) on 10.0.0.6, no CVE, medium. No CVE, so the key uses the lowercased title with spaces collapsed. New ticket, priority 3.
4. scanA reports "Server banner" on 10.0.0.6 with severity weird. Different title, so a different key: new ticket, but the severity is not in the table, so priority defaults to 3 and needs_triage is set.

Four findings in, three tickets out.

Key points

• Dedup key: normalized CVE (an ID for a specific known flaw) if present, otherwise the lowercased, whitespace-collapsed title, joined with the canonical IP form (the one standard spelling of an address: for example the IPv6 address ::1 can also be written 0:0:0:0:0:0:0:1, and both must hash the same). The hash is cut to 12 hex characters to keep keys readable in a ticket label; in production keep more characters (or all 64) so two different issues are very unlikely to share a key. The first two findings use different titles and different case in the CVE, yet merge, because the key uses the CVE. It uses the asset and issue, not scanner_id, since two scanners reporting one problem is the case to catch.
• Merge rule: the ticket keeps the most severe priority seen and all evidence and sources.
• Second layer, across runs: the caller passes the keys of open tickets (store the key on the ticket as a label), so re-scanning does not open a duplicate. Passing the key list is the difference between deduping within one batch and deduping over time.
• Unknown severity: defaults to priority 3 and is flagged needs_triage rather than crashing or silently becoming low.

Complexity

Time O(n) for n findings: the work grows in step with the input (double the findings, roughly double the time), because each finding costs one hash and one dictionary lookup. Space O(n): the tickets held in memory also grow in step with the input.

Edge cases

• Same CVE on different IPs gives separate tickets by design (different owners may fix them). If you would rather have one ticket per CVE across many hosts, drop the IP from the key and list assets on the ticket.
• Invalid IP: ipaddress.ip_address raises ValueError; in production catch it per finding and route it to a rejects list.
• Key exists on a closed ticket: a real system should reopen it (a regression) rather than skip it; here existing_keys should hold only open ones.
• Same title from different scanners for a finding with no CVE will not merge if the wording differs: accept that, or add a fuzzy-match review.

Direct answer

Read the CSV with csv.DictReader, keep rows whose status is open-like, parse due_date as ISO 8601 (no guessing of other formats), and compare it to today's date in one declared "policy" time zone. Rows whose date cannot be parsed are never silently dropped or guessed at: they go to a separate "bad dates" list that is printed for a human to fix.

Terms. ISO 8601 is the standard date format year-month-day (2026-09-28), optionally followed by a time and an offset (T23:30:00-07:00, meaning 7 hours behind UTC, the world reference time). A datetime with an offset is called aware (it says exactly which instant it is); one with no zone information, including a bare date, is called naive (it could be any zone). A policy zone is the single zone the organisation declares as the meaning of "due", and America/New_York is an IANA zone name (the standard tz database name that also knows daylight-saving rules). A quarantined row is one set aside in a separate list for a human to fix instead of being processed.

Time-zone rules

• Pick one policy zone for what "due" means (here America/New_York) and write it in the script. A date-only value (2026-09-28) means that calendar day in the policy zone.
• A value with an offset (2026-09-27T23:30:00-07:00) is converted to the policy zone first, and only then reduced to a date. Taking the date before converting gives the wrong day: that timestamp is 02:30 on 2026-09-28 in New York, so it is not overdue on 09-28, while a naive reading of the raw text (09-27) would call it 1 day overdue.
• "Today" must come from the same zone (datetime.now(POLICY_TZ).date()), otherwise items flip to overdue hours early or late.
• Overdue means due date strictly before today; an item due today is not yet overdue.

Bad-date rules

• Empty, impossible (2026-02-30) and ambiguous (09/12/2026 could be 9 December or 12 September) values are rejected, not guessed. Guessing produces confident wrong numbers.
• Every rejection is reported with its line number, ticket id and reason, so the count of overdue items is never quietly understated.

Script (Python 3.11 or newer, because earlier versions of datetime.fromisoformat accept only a narrow set of ISO forms; standard library only; today is pinned so the output is reproducible). Note that 3.11+ also accepts a space in place of the T (ticket 8, 2026-09-01 08:00:00), which is still ISO-like, so "strict" here means slash formats such as 09/12/2026 are refused, not that only one spelling passes.

import csv
import io
from datetime import date, datetime
from zoneinfo import ZoneInfo

POLICY_TZ = ZoneInfo("America/New_York")   # the org's declared "due date" timezone
OPEN_STATUSES = {"open", "in progress", "blocked"}

CSV_TEXT = """id,summary,owner,due_date,status
1,Patch OpenSSL,ana,2026-09-20,Open
2,Rotate keys,raj,2026-09-27T23:30:00-07:00,open
3,Fix XSS,li,2026-09-28,open
4,Close S3 bucket,sam,2026-02-30,open
5,Old finding,ana,2026-08-01,Closed
6,Enable MFA,raj,09/12/2026,open
7,Update TLS,li,,In Progress
8,Harden SSH,sam,2026-09-01 08:00:00,blocked
"""


def parse_due(raw):
    """Return a date in POLICY_TZ, or raise ValueError. Refuses ambiguous formats."""
    raw = raw.strip()
    if not raw:
        raise ValueError("empty due_date")
    dt = datetime.fromisoformat(raw)          # ISO 8601 only: 2026-09-20 or 2026-09-20T10:00:00+02:00
    if dt.tzinfo is None:                     # date-only or naive: interpret in the policy zone
        return dt.date()
    return dt.astimezone(POLICY_TZ).date()    # aware: convert first, then take the calendar date


def overdue(rows, today):
    good, bad = [], []
    for line_no, row in enumerate(rows, start=2):          # line 1 is the header
        if row["status"].strip().lower() not in OPEN_STATUSES:
            continue
        try:
            due = parse_due(row["due_date"])
        except ValueError as exc:
            bad.append((line_no, row["id"], row["due_date"], str(exc)))
            continue
        if due < today:
            good.append((row["id"], row["owner"], due, (today - due).days))
    good.sort(key=lambda r: -r[3])
    return good, bad


if __name__ == "__main__":
    today = date(2026, 9, 28)   # pinned; in production: datetime.now(POLICY_TZ).date()
    good, bad = overdue(csv.DictReader(io.StringIO(CSV_TEXT)), today)
    for tid, owner, due, days in good:
        print(f"OVERDUE id={tid} owner={owner} due={due} days_overdue={days}")
    for line_no, tid, raw, why in bad:
        print(f"BAD_DATE line={line_no} id={tid} value={raw!r} reason={why}")

Output:

OVERDUE id=8 owner=sam due=2026-09-01 days_overdue=27
OVERDUE id=1 owner=ana due=2026-09-20 days_overdue=8
BAD_DATE line=5 id=4 value='2026-02-30' reason=day 30 must be in range 1..28 for month 2 in year 2026
BAD_DATE line=7 id=6 value='09/12/2026' reason=Invalid isoformat string: '09/12/2026'
BAD_DATE line=8 id=7 value='' reason=empty due_date

The reason text after reason= comes from the interpreter and varies by version: the wording shown for 2026-02-30 is from Python 3.14, while 3.11 to 3.13 print day is out of range for month. The rows flagged, their order and the day counts are identical on every version from 3.11.

Reading the result

• Ticket 1: 2026-09-28 minus 2026-09-20 is 8 days. Ticket 8: 09-01 to 09-28 is 27 days.
• Ticket 2 (offset timestamp) and ticket 3 (due today) are correctly NOT overdue. Ticket 5 is closed, so skipped.
• Three rows are quarantined, and the overdue list is sorted worst first.

Complexity and edge cases

• O(n log n) for the sort, O(n) memory; for a huge export stream rows and sort only the good list.
• Watch for a byte-order mark (an invisible marker some tools write at the start of a file, which would corrupt the first header name id) or different header names/case in real exports, statuses with odd spelling ("Re-opened"), and due dates in the far past that hint at data-entry errors. In production, exit non-zero or alert if the bad-date count exceeds a threshold, so a broken export cannot masquerade as "nothing is overdue".

Direct answer

The guidance is staged by urgency: contain now, fix properly this sprint, prove the fix works, then watch for misuse. The fix is parameterized queries. Filters are stopgaps only.

Remediation guidance: SQL injection (CWE-89, the numbered entry for this weakness in the Common Weakness Enumeration catalogue) in the order-lookup endpoint

1. Now (within 24 to 48 hours)

• Review database and web logs since deployment for injection strings (' OR, which closes a quote and adds an always-true condition; UNION, which bolts a second query onto the first to pull data from other tables; and unexpected SQL errors) to learn whether it was already exploited. A typical suspicious log line (illustrative): GET /orders/lookup?customer=x' UNION SELECT ... followed by a 500 response.
• Add a temporary web application firewall rule for this parameter. State plainly that this is a stopgap: filters can be bypassed.
• If the log review shows exploitation, escalate to the incident process, and rotate (change to new values) any credentials the database account could read.
• Reduce the application's database account to the minimum tables and permissions it needs, so a success gives the attacker less.

2. This sprint

• Replace string-built queries with parameterized queries (the SQL text and the user values travel separately, so values can never be read as commands).
• Sort columns, table names and other identifiers cannot be parameters: accept them only from a fixed allow-list.
• Search the code base for the same pattern in every other query.
• Prefer the framework's query builder or object-relational mapper (a library that generates SQL from objects), but check raw-SQL escape hatches in it too.

Code-level recommendation (this runs as shown, using Python's built-in sqlite3, where the placeholder is ?; other drivers use %s or named parameters):

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER, customer TEXT, total REAL)")
db.executemany("INSERT INTO orders VALUES (?, ?, ?)",
               [(1, "alice", 20.0), (2, "bob", 35.5)])

def find_orders_vulnerable(customer):
    return db.execute(f"SELECT id, total FROM orders WHERE customer = '{customer}'").fetchall()

def find_orders_fixed(customer):
    return db.execute("SELECT id, total FROM orders WHERE customer = ?", (customer,)).fetchall()

payload = "x' OR '1'='1"
print("vulnerable:", find_orders_vulnerable(payload))
print("fixed:     ", find_orders_fixed(payload))
print("fixed, normal input:", find_orders_fixed("alice"))

Output:

vulnerable: [(1, 20.0), (2, 35.5)]
fixed:      []
fixed, normal input: [(1, 20.0)]

The attack string turns the vulnerable query into "return every row"; the parameterized one treats it as a customer name that does not exist.

3. How to test the fix

• Add an automated regression test (a test that fails if the bug ever returns) that sends the payload above and asserts no rows and no error leak (the response must not contain database error text that reveals the query).
• Ask the original tester to replay the exact original exploit (retest), plus variants such as time-based payloads (the attacker makes the database pause for a few seconds, for example with a sleep call, to infer yes or no answers when nothing is printed) and error-based payloads (the attacker provokes error messages that leak data).
• Run static analysis (SAST, Static Application Security Testing: scanning source code) on every pull request to flag string-built SQL, and dynamic scanning (DAST, Dynamic Application Security Testing: attacking the running app) against the staging deployment, in the pipeline.

4. What to monitor afterwards

• Alerts on database syntax errors from the application account, for example more than 5 in 10 minutes (illustrative threshold; set it from your normal baseline, which is close to zero). A sample line (illustrative): ERROR app_orders: syntax error at or near "'" (statement from /orders/lookup).
• Alerts on unusually large result sets or slow queries from that endpoint, for example an order lookup returning over 1,000 rows when normal lookups return under 50.
• Track WAF hits on the parameter for several weeks; remove the stopgap rule only after the retest passes.

Pitfalls

• Escaping quotes by hand or blocklisting words: bypassable, not a fix.
• Closing when the WAF rule blocks the tester's payload: the flaw is still in the code.

Direct answer

A stopgap that is not tracked becomes permanent. So the moment service is restored I turn it into a recorded, owned, time-boxed risk: written down in the incident timeline, converted to a ticket with a named owner and a due date, communicated to the people who own the risk, and closed only when the stopgap is removed and someone independent confirms it.

Worked scenario (illustrative)

During an outage, to let a failover database replica connect, an engineer changed a firewall rule (the network filter that decides who may connect) so the database port (5432, the door clients use to talk to it) accepted connections from every address on the internet (written 0.0.0.0/0) instead of only the replica's own address (10.0.4.17). Service returned in 10 minutes. The gap: the database is now reachable from outside, by anyone who can guess or find it, instead of by one server.

Step by step, in order

1. Document during the incident. In the incident timeline: exact change, who made it, time, and the gap it created. A one-line note beats memory later.
2. Create a temporary-mitigation record the same day. Fields: what changed and where, why, what risk it adds (who could reach what), compensating controls now in place (a compensating control is a temporary safeguard that reduces the risk while the real fix is pending, such as extra logging or an alert on connections from unexpected addresses), owner, expiry date, and the permanent-fix ticket link. Filled in for this scenario:

Record:        TM-2026-041
Change:        Firewall rule db-failover-allow, port 5432, source 0.0.0.0/0 (was 10.0.4.17/32)
Why:           Failover replica could not connect during the 2026-09-28 outage
Risk added:    Anyone on the internet can attempt to log in to the production database
Compensating:  Password-only logins disabled for non-replica users; alert on any 5432
               connection from outside 10.0.0.0/8; connection log reviewed daily
Owner:         Payments platform team lead
Expires:       2026-10-05
Permanent fix: SEC-2210 (restrict source to 10.0.4.17/32), due 2026-10-05

3. Turn it into tracked work. One ticket for the permanent fix (restrict to the specific hosts), owned by the service team, with a due date (for example 5 business days, since this is a live exposure). Also add it as an action item in the postmortem (the blameless review after an incident: it asks what failed in the system, not who to blame).
4. Communicate the risk. A short message to the service owner, the security team and the on-call manager: what is exposed, what compensates, when it ends, what triggers escalation. Plain wording, no blame. For example: "Since 14:10 today the production database port accepts connections from any internet address, a side effect of restoring failover. We have restricted logins and are alerting on outside connections. Fix is ticketed (SEC-2210), due Oct 5. If the alert fires, security will block the port immediately and we will take the failover replica offline until it is fixed."
5. Make the expiry enforceable. Tag the change with an expiry date, and run an alert that fires when an expired tag is still present. A calendar reminder alone is not enough, because it depends on someone remembering. Concretely: put a label expires=2026-10-05 on the firewall rule, and run a daily job that lists every rule whose expires date is earlier than today and pages the owner. On 2026-10-06 the broad rule shows up in that list if it still exists, without anyone having to recall it.
6. Replace and verify. Apply the proper rule, remove the broad one, test the failover still works, and have someone other than the author confirm from outside that the port is no longer reachable. Attach the evidence and close both the record and the ticket.

What would change my call

If the permanent fix needs weeks (for example a network redesign), I stop calling it temporary: it becomes a formal, time-limited risk acceptance (a written, signed decision by the person who owns the risk that the organisation will live with it for a stated period), with stronger compensating controls and a renewal date.

Pitfalls

• Closing the incident and the ticket together, so nothing carries the exposure forward.
• Blame-heavy wording that discourages people from disclosing stopgaps next time.
• "Fixed" declared without confirming the old rule is gone.

Direct answer

Make "done" checkable before work starts and make the tracker refuse to close a ticket that has neither a passing check nor attached evidence. Concretely: write acceptance criteria into every remediation ticket when it is created, split "fixed" (developer) from "verified closed" (someone else), require an evidence field that the workflow enforces, automate the check wherever a tool can run it, and provide a visible exception path (risk acceptance) so the gate is not bypassed by frustration.

Why tickets close unverified

The typical ticket says "fix the XSS on the search page". Nothing in it tells the developer or the reviewer what a fixed state looks like, so closure defaults to "the developer said so". The fix is to change what a ticket has to contain.

The design

1. Acceptance criteria at creation (a written test for what "fixed" means, not a vague wish), in a fixed shape (given the situation, do this action, expect this result), tied to the finding class. Two terms: SQL injection is an attack that smuggles database commands into a request, and cross-site scripting (XSS) smuggles script into a page that other users' browsers then run. A regression test is an automated test that keeps checking the fix stays in place after later changes.

2. Workflow states: Open, In progress, Fixed pending verification, Verified closed, Risk accepted. The developer can reach the third state (Fixed pending verification), not the fourth (Verified closed); the fifth (Risk accepted) needs a senior owner's signed decision.
3. Enforced evidence field: the transition to Verified closed is blocked by a required field, and the field must reference a check that ran after the fix was deployed.
4. Automation: for classes a tool can check (scans, configuration rules, regression tests), a bot reruns the check when the ticket reaches Fixed pending verification and closes it only on a pass. Humans verify what the bot cannot.
5. Exceptions: an item that truly cannot be tested becomes a documented risk acceptance with an owner, a reason and an expiry date, never a silent close.

Worked example: an SQL injection ticket

Title: SQL injection in GET /orders?sort=
Owner: orders team, assignee named
Due: 7 days (critical)
Acceptance criteria:
  1. GIVEN the orders endpoint on production, WHEN the request from finding F-118 is sent (sort=1;select pg_sleep(5)), THEN it returns 400 (rejected), not a 200 that arrives after about 5 seconds.
  2. A regression test covering that parameter exists and runs in continuous integration (CI, the automated build-and-test run).
  3. Security re-runs the request in production after deploy and attaches the result.
Evidence required: CI run link + retest output dated after deploy.

Why that payload: pg_sleep(5) is a PostgreSQL function that makes the database pause for 5 seconds. If the original request came back about 5 seconds late, the database ran the attacker's command, which proves the injection without stealing data. After the fix, the input is rejected before it reaches the database, so a quick 400 means fixed and a slow 200 means still vulnerable.

The developer can move the ticket to Fixed pending verification only with the pull request linked. Verified closed needs criteria 1 to 3 satisfied.

Traced lifecycle (illustrative dates): Mon 1 June the ticket is Open. Wed 3 June the developer links the pull request and moves it to Fixed pending verification. Thu 4 June security replays the request and it still delays 5 seconds on the sibling parameter filter, so it returns to In progress with that evidence. Mon 8 June the developer fixes both, security replays it, gets 400, attaches the output, and sets Verified closed on the 7-day deadline day. The reopen rate metric counts tickets that bounced back like this one, divided by tickets that reached Fixed pending verification.

Rollout

1. Start with critical and high findings on new tickets only, so the change is small.
2. Pilot with two teams, adjust the templates from their feedback, then enforce everywhere.
3. Measure the baseline first: audit a sample of past closed tickets and see how many reproduce. Repeat quarterly.
4. Track: percentage closed with evidence, reopen rate after independent retest, and the extra days verification adds.

Trade-offs and pitfalls

• Evidence theatre: a screenshot proves nothing. Require artifacts that carry a timestamp and reference the deployed change.
• Rubber-stamp verification under deadline pressure: sample-check the verifier's closures.
• Friction: the gate slows closure. That is the cost of honesty; show the reopen-rate drop to keep support for it.
• Not everything is testable. Forcing a fake criterion is worse than an honest risk acceptance.