Queries · q111 · Chief Information Security Officer · week 1, night 4
“When compliance_events flags something (suspicious access, retention overruns, failed DSAR SLAs, etc.), is it actually getting resolved, or are flagged incidents piling up unresolved — is existing monitoring a real control or just theoretical?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 1
Subject: Where I'd like your help first — account security snapshot
Hi Noah,
Good to have you working with me this week. I'm still building my mental map of the business, so let's start broad and sharpen later. Here's what's on my mind, roughly in priority order:
What I need this for: I'm walking into enterprise deals where security questionnaires will ask us point-blank about authentication strength, access governance, credential hygiene, and auditability. I need an honest, evidence-based picture — good or bad — so I can brief the CEO and sales on what we can credibly claim today versus what needs fixing before the next review cycle.
Given the slot budget, let's pick whichever 3 of these will give us the most defensible starting picture this week, and we can go deeper on the rest next week.
Thanks, Henrik
The analyst · week 1, night 4, request 3 of 3
Night 3 brought the access-governance story to a clean, actionable finding: all 16 dormant Owner/Administrator accounts sit on CHURNED tenants only (9 tenants), with one as stale as 599 days since last login — meaning the real gap isn't "someone's not reviewing active-merchant access," it's that user accounts simply never get deactivated when a tenant churns (status still reads 'active'). That's a crisp, fixable finding for the memo. The API-key report came back almost too clean — zero active keys anywhere show as never-used or unused 90+ days, even though the same report shows oldest-active-key ages running into the thousands of days (one at 2,743 days, ~7.5 years, which predates Tidewick's 2023 warehouse cutover and even stretches toward the 2018 founding). That combination — heavily used, never rotated — is exactly the "long-lived token nobody rotates" risk Henrik named, so I want a platform-wide age/rotation distribution rather than relying on a handful of per-tenant data points. The audit-log report surfaced the most actionable gap of the week: 'delete' actions show only 27 events company-wide across three years, across just 16 of 120 tenants (13.3%) — implausibly low for an e-commerce platform this size, and worth testing directly against actual soft-deletions in the database rather than assuming it's a true absence of deletes.
Tonight (final night) I'm spending all 3 slots closing out the two topics we hadn't quantified yet (API-key rotation hygiene, audit-trail completeness) plus checking whether existing compliance monitoring is a real control or just theoretical, since that's the credibility test for "can we reconstruct who did what" claims in the memo.
The engineer · 1 dry run on the empty schema, then the submission
SELECT severity, COUNT(*) AS total_events, SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_count, SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_count, ROUND(100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*), 1) AS resolution_rate_pct, MAX(CASE WHEN NOT resolved THEN DATE '2026-01-05' - CAST(occurred_at AS date) END) AS oldest_unresolved_age_days, MAX(occurred_at) AS most_recent_occurred_at FROM compliance_events GROUP BY severity ORDER BY unresolved_count DESC, severity ASC
“Query compiles fine. Submitting.”
“Submitted. The report gives one row per severity level with total/resolved/unresolved counts, resolution rate, oldest unresolved event age (in days, relative to 2026-01-05), and the most recent event timestamp — ordered by unresolved count descending so the biggest backlog risks surface first.”
As Noah Fischer saw it, from the small dataset the agents worked on
severity | total_events | resolved_count | unresolved_count | resolution_rate_pct | oldest_unresolved_age_days | most_recent_occurred_at ---------+--------------+----------------+------------------+---------------------+----------------------------+--------------------------- high | 4 | 4 | 0 | 100.0 | NULL | 2025-10-27 09:00:00 medium | 5 | 5 | 0 | 100.0 | NULL | 2025-02-28 09:38:06.883908 (2 rows)
Changed only where the benchmark needs the same result on every system
SELECT severity, COUNT(*) AS total_events, SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_count, SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_count, ROUND(100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*), 1) AS resolution_rate_pct, 100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*) AS resolution_rate_pct, MAX(CASE WHEN NOT resolved THEN DATE '2026-01-05' - CAST(occurred_at AS date) END) AS oldest_unresolved_age_days, MAX(occurred_at) AS most_recent_occurred_atFROM compliance_eventsGROUP BY severityORDER BY unresolved_count DESC, severity ASCORDER BY unresolved_count DESC NULLS FIRST, severity ASC NULLS LAST
SELECT severity, COUNT(*) AS total_events, SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_count, SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_count, 100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*) AS resolution_rate_pct, MAX(CASE WHEN NOT resolved THEN DATE '2026-01-05' - CAST(occurred_at AS date) END) AS oldest_unresolved_age_days, MAX(occurred_at) AS most_recent_occurred_at FROM compliance_events GROUP BY severity ORDER BY unresolved_count DESC NULLS FIRST, severity ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Noah Fischer (analyst for the Chief Information Security Officer): Title: Compliance event resolution health by severity Question: When compliance_events flags something (suspicious access, retention overruns, failed DSAR SLAs, etc.), is it actually getting resolved, or are flagged incidents piling up unresolved — is existing monitoring a real control or just theoretical? Grain: one row per severity level in compliance_events Columns: - severity - total events (all-time, 2023-01-01 through 2025-12-31) - resolved count - unresolved count - resolution rate (%) - oldest unresolved event age in days (2026-01-05 minus occurred_at, unresolved only) - most recent event occurred_at Details: Source: compliance_events (severity, resolved, occurred_at, resolved_at). Group by severity. resolution_rate = resolved count / total events, rounded to 1 decimal percent. 'Oldest unresolved event age in days' computed only over rows where resolved = false, as 2026-01-05 minus occurred_at (fixed reference date, no CURRENT_DATE); null if no unresolved rows in that severity. Order by unresolved count descending, severity as tie-break. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT severity, COUNT(*) AS total_events, SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_count, SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_count, ROUND(100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*), 1) AS resolution_rate_pct, MAX(CASE WHEN NOT resolved THEN DATE '2026-01-05' - CAST(occurred_at AS date) END) AS oldest_unresolved_age_days, MAX(occurred_at) AS most_recent_occurred_at FROM compliance_events GROUP BY severity ORDER BY unresolved_count DESC, severity ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: severity, total_events, resolved_count, unresolved_count, resolution_rate_pct, oldest_unresolved_age_days, most_recent_occurred_at
submitSELECT severity, COUNT(*) AS total_events, SUM(CASE WHEN resolved THEN 1 ELSE 0 END) AS resolved_count, SUM(CASE WHEN NOT resolved THEN 1 ELSE 0 END) AS unresolved_count, ROUND(100.0 * SUM(CASE WHEN resolved THEN 1 ELSE 0 END) / COUNT(*), 1) AS resolution_rate_pct, MAX(CASE WHEN NOT resolved THEN DATE '2026-01-05' - CAST(occurred_at AS date) END) AS oldest_unresolved_age_days, MAX(occurred_at) AS most_recent_occurred_at FROM compliance_events GROUP BY severity ORDER BY unresolved_count DESC, severity ASC
Submitted to tonight's batch. Your work on this request is done.