Finding timeline report — show when findings were first seen and resolved

[hasecon]

Summary

As a KAT user, I want a report that shows when each finding was first detected and when it was resolved, so I can track the lifecycle of vulnerabilities over time and demonstrate remediation progress to stakeholders.

Currently there is no way to get a timeline view of findings. The existing reports show a point-in-time snapshot, but not when findings appeared or disappeared.

Use cases

• Compliance reporting: Show auditors that findings are being addressed within SLA timeframes
• Trend analysis: Track whether the overall security posture is improving or degrading over time
• Remediation tracking: Identify findings that have been open the longest and need attention
• Change correlation: Correlate finding appearances/resolutions with infrastructure changes

Current state

• Octopoes already has get_entity_history() (octopoes/octopoes/xtdb/client.py) which calls XTDB's entity-history API. This returns transaction records per entity, including valid-time ranges.
• FindingsReport and VulnerabilityReport already call get_history() per finding, but only to determine first-seen date — and they do it as N+1 individual calls, which is a known performance bottleneck.
• XTDB v1 (>= 1.22.1) supports get-start-valid-time and get-end-valid-time Datalog predicates, which would allow fetching timeline data for all findings in a single query instead of N+1 calls.

Proposed approach

Phase 1: Timeline report with existing API

• New report type in Rocky: FindingTimelineReport
• Uses existing get_entity_history() to collect first-seen and last-seen dates per finding
• Output: table with columns: Finding | Severity | First seen | Resolved | Duration open
• Accept this will be slow for large orgs due to N+1 history calls

Phase 2: Optimize with XTDB temporal predicates

• Upgrade XTDB to >= 1.22.1 (current latest v1 is 1.24.4)
• Add a new Octopoes endpoint (e.g. GET /{client}/findings/timeline) that uses get-start-valid-time / get-end-valid-time in a single Datalog query
• This replaces the N+1 entity-history calls with one bulk query
• Update the report to use the new endpoint

Phase 3: Dashboard integration

• Add a timeline widget to the Crisis Room dashboard
• Show trend chart: open findings over time
• Highlight newly appeared and recently resolved findings

Technical notes

• Current XTDB version: Custom image docker.underdark.nl/librekat/xtdb-http-multinode:main — exact version unknown, needs verification
• Latest XTDB v1: 1.24.4 (June 2024) — v1 is in maintenance mode, v2 is the active development line
• XTDB v2 migration: Out of scope for this issue, but worth noting that v2 (latest: 2.1.0) has native SQL support and richer temporal queries. A future migration would further improve this feature.

Related

• Advanced Octopoes queries #1292 — Advanced Octopoes queries (user story 3: temporal audit trail)
• Generation of reports resulting in Server Error (500) #3945 — Report generation 500 errors (N+1 get_history() calls are a contributing factor)
• XTDB 1.22.1 release: https://github.com/xtdb/xtdb/releases/tag/1.22.1

[underdarknl]

related functionality from xtdb's last v1 release.
https://github.com/xtdb/xtdb/releases/tag/1.22.1

[hasecon]

Analysis: Finding timeline report — implementation approach using XTDB 1.24

Current bottleneck

The FindingsReport and VulnerabilityReport both use N+1 get_history() calls — one per finding — to get first_seen. last_seen is hardcoded to "-" (not implemented). For a report with 500 findings, that's 501+ API calls. This is a contributing factor to #3945 (report generation 500 errors).

What XTDB 1.22.1+ temporal predicates offer

As @underdarknl pointed out, XTDB 1.22.1 added get-start-valid-time and get-end-valid-time Datalog predicates. These return temporal bounds per entity within a single query — no N+1:

{:find [?e ?finding_type ?ooi ?start ?end]
 :where [[?e :object_type "Finding"]
         [?e :Finding/finding_type ?finding_type]
         [?e :Finding/ooi ?ooi]
         [(get-start-valid-time ?e) ?start]
         [(get-end-valid-time ?e) ?end]]}

One query → all findings with first-seen (start) and resolved-at (end).

For currently open findings, end = max valid-time (infinity). For resolved findings (deleted from graph), end = the valid-time at which they were removed.

The point-in-time limitation

XTDB v1 Datalog queries always execute at a single valid_time snapshot. You only see entities that exist at that moment. A finding that was created on day 5 and resolved on day 25 won't appear if you query on day 1 or day 30.

The temporal predicates work perfectly for entities visible at the query time — they give you start and end. But they can't discover entities that no longer exist at your query time.

This means the filter Edward suggested:

[(get-start-valid-time ?e) ?start]
[(get-end-valid-time ?e) ?end]
[(>= ?start ?period_start)]
[(<= ?end ?period_end)]

...is correct in concept but can only find findings that exist at the query's valid_time. Resolved findings that disappeared before the query time are invisible.

Three approaches to get the complete timeline

Approach A: Multiple snapshot queries (simplest)

Instead of querying at 2 time points, query at N evenly-spaced points across the report period:

Monthly report: query at day 1, 8, 15, 22, 30
→ 5 Datalog queries with temporal predicates
→ each query returns findings that exist at that snapshot, with their start/end times
→ merge and deduplicate
→ catches every finding that lived at least 7 days

With daily snapshots (30 queries for a monthly report), you catch everything that existed for at least 1 day. Still orders of magnitude better than N+1 (30 cheap Datalog queries vs 500+ entity-history calls).

Pro: uses standard Datalog, easy to implement, parallelizable.
Con: theoretically misses very-short-lived findings (< gap between snapshots). Practically negligible.

Approach B: Transaction log scan (100% complete)

The XTDB transaction log contains every PUT and DELETE operation. Scan the tx-log for Finding operations within the period and reconstruct the complete timeline:

• Finding PUT at valid_time=T1 → first_seen = T1
• Finding DELETE at valid_time=T2 → resolved_at = T2
• No DELETE → still open

One API call to the tx-log endpoint, filter for Finding operations. Zero missed findings.

Pro: complete — no findings missed regardless of their lifecycle duration.
Con: tx-log contains ALL operations (not just findings), so filtering is needed. Heavier for large datasets.

Approach C: Dedicated Octopoes timeline endpoint (recommended)

New endpoint GET /{client}/findings/timeline that internally combines both approaches:

Parameters:
  valid_time_start: datetime
  valid_time_end: datetime
  severities: optional filter
  limit/offset: pagination

Returns per finding:
  finding_reference, finding_type, ooi, severity,
  first_seen, resolved_at (null if open), duration_open_days
  
Summary:
  total_in_period, newly_appeared, resolved, still_open,
  avg_resolution_time_days

Implementation: use the tx-log for completeness, with Datalog temporal queries as an optimization for the common case (most findings are long-lived and visible at the period endpoints).

Proposed implementation phases

Phase 1: Timeline endpoint with snapshot queries

• New Octopoes endpoint using Approach A (multiple Datalog snapshots)
• Weekly snapshots across the report period (4-5 queries per report)
• Returns first_seen and resolved_at per finding
• New FindingTimelineReport in Rocky that calls this endpoint

Phase 2: Migrate existing reports

• Replace N+1 get_history() calls in FindingsReport and VulnerabilityReport with the new timeline endpoint
• Fixes the Generation of reports resulting in Server Error (500) #3945 performance bottleneck

Phase 3: Complete coverage via tx-log

• Add tx-log scan fallback for short-lived findings missed by snapshots
• Or switch to tx-log as the primary mechanism if performance allows

Phase 4: Dashboard integration

• Crisis Room widget: open findings trend over time
• Requires periodic snapshot storage (weekly aggregation in PostgreSQL or as scheduled reports)

Connection to #5108

The RFC on contradictory observations (#5108) proposes using valid-time-end for observation freshness/TTL. If observations get temporal bounds, findings derived from them automatically inherit temporal bounds. This makes the timeline report more accurate for free — a finding based on an expired observation would have a natural resolved_at without needing an explicit delete.

What we cannot do with XTDB v1

Need	v1 capability
First-seen / resolved-at per finding	✅ via temporal predicates
All findings in a period	✅ via multi-snapshot or tx-log
Finding lifecycle filter (start >= X, end <= Y)	⚠️ only for findings existing at query time
Aggregated trend (findings per day/week)	❌ requires scheduled snapshots or v2
Full audit trail (every state change)	❌ per-entity only (entity-history) or v2

XTDB v2 has native SQL and richer temporal range queries that would eliminate these limitations, but a v2 migration is a separate effort.