Queries · q106 · Chief Information Security Officer · week 1, night 3
“Specifically which accounts currently hold the two most powerful roles (Owner, Administrator) but haven't logged in recently — the concrete list of privileged accounts that look stale?”
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 3, request 1 of 3
Night 2 findings: MFA is flat and low — staff MFA has hovered at 54-57% all year (not improving, not declining — a plateau, which itself is a finding: nobody is pushing adoption), and shopper MFA is a literal 0.0% in every single month across ~11,200 shopper accounts, confirming it isn't sampling noise from report 1 — shoppers simply have no MFA option in use at all. The Nov 24 week spike is now explained: it's broad-based across ~110 tenants with the largest tenant (Cragsley Outfitters) naturally taking the biggest share, distinct-IPs tracking distinct-emails roughly 1:1, and failure rates in line with baseline — looks like ordinary Black Friday/Cyber Monday traffic, not a credential-stuffing event, so I'm closing that thread. The role-landscape report revealed something important report 3 (night 1) didn't show: every tenant also has an 'Administrator' system role carrying almost as many permissions as Owner (25 vs 26), with 122 holders platform-wide and a 6.6% dormancy rate — higher than what we saw for Owner — so Administrator, not just Owner, is the second pool of truly powerful access worth naming individuals in.
With two nights left before Friday's memo, I'm using tonight to (1) get the concrete, named list of dormant Owner/Administrator accounts for the access-governance finding, and open the two untouched topics Henrik raised — (2) API key/service account hygiene and (3) audit trail completeness — so night 4 can go deeper on whichever of those looks worst rather than starting cold.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rows FROM user_roles ur JOIN roles r ON r.id = ur.role_id JOIN users u ON u.id = ur.user_id JOIN tenants t ON t.id = u.tenant_id WHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05') ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC, u.email ASC, r.name ASC LIMIT 50
“Query compiles cleanly with the expected columns.”
“Submitted. Summary of what the report delivers:
- Grain: one row per (user, role) — Owner/Administrator, active, dormant (never logged in or last login before 2025-10-05), excluding soft-deleted users.
- Columns: tenant name/status, role name, user email/status, last login (or 'never'), days since last login, account age in days, plus a total-matching-rows count.
- Ordering: never-logged-in users first, then oldest last-login dates first (most stale first), tie-broken by tenant name, email, role.
- Cap: 50 rows printed; total_qualifying_rows shows the full count if more qualify.”
As Noah Fischer saw it, from the small dataset the agents worked on
tenant_name | tenant_status | role_name | user_email | user_status | last_login | days_since_last_login | account_age_days | total_qualifying_rows -------------------+---------------+---------------+-------------------------------------+-------------+----------------------------+-----------------------+------------------+---------------------- Paddock & Pine | churned | Administrator | maja.jensen@paddockandpine.dk | active | 2024-05-16 06:13:03.853485 | 599 | 678 | 16 Paddock & Pine | churned | Owner | carl.christensen@paddockandpine.dk | active | 2024-05-30 05:55:25.699322 | 585 | 743 | 16 Northwire Audio | churned | Owner | logan.schmid@northwireaudio.co.uk | active | 2024-10-04 08:15:43.724015 | 458 | 1024 | 16 Petal & Pestle | churned | Owner | amelia.nguyen@petalandpestle.com | active | 2024-10-09 07:25:29.989527 | 453 | 1040 | 16 Northwire Audio | churned | Administrator | zoe.berg@northwireaudio.co.uk | active | 2024-10-10 07:10:47.282732 | 452 | 1022 | 16 Petal & Pestle | churned | Administrator | emily.lewis@petalandpestle.com | active | 2024-10-11 06:04:30.128982 | 451 | 1008 | 16 Saltmarsh Knitwear | churned | Owner | julia.shah@saltmarshknitwear.nl | active | 2024-11-15 06:13:56.366752 | 416 | 1296 | 16 Saltmarsh Knitwear | churned | Administrator | ruben.goossens@saltmarshknitwear.nl | active | 2024-11-21 09:11:08.290205 | 410 | 1271 | 16 Aurum & Ash | churned | Administrator | ethan.watson@aurumandash.co.uk | active | 2024-12-20 10:45:46.808919 | 381 | 559 | 16 Aurum & Ash | churned | Owner | evelyn.miller@aurumandash.co.uk | active | 2024-12-26 09:20:33.907508 | 375 | 574 | 16 Klara Beauty | churned | Owner | julian.wozniak@klarabeauty.de | active | 2025-04-17 07:06:17.917437 | 263 | 503 | 16 Klara Beauty | churned | Administrator | julia.moser@klarabeauty.de | active | 2025-04-18 05:25:53.814361 | 262 | 413 | 16 Marginalia | churned | Administrator | iris.vandijk@marginalia.nl | active | 2025-06-13 06:55:24.418769 | 206 | 313 | 16 Marginalia | churned | Owner | bas.hendriks@marginalia.nl | active | 2025-06-19 06:26:42.339924 | 200 | 378 | 16 Halden Boots | churned | Administrator | ebba.jansson@haldenboots.se | active | 2025-09-22 06:10:12.824838 | 105 | 388 | 16 Halden Boots | churned | Owner | ida.lindqvist@haldenboots.se | active | 2025-10-03 08:05:13.116482 | 94 | 396 | 16 (16 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rowsFROM user_roles urJOIN roles r ON r.id = ur.role_idJOIN users u ON u.id = ur.user_idJOIN tenants t ON t.id = u.tenant_idWHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05')ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC, u.email ASC, r.name ASCLIMIT 50ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC NULLS LAST, u.email ASC NULLS LAST, r.name ASC NULLS LAST, ur.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rows FROM user_roles ur JOIN roles r ON r.id = ur.role_id JOIN users u ON u.id = ur.user_id JOIN tenants t ON t.id = u.tenant_id WHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05') ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC NULLS LAST, u.email ASC NULLS LAST, r.name ASC NULLS LAST, ur.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rows FROM user_roles ur JOIN roles r ON r.id = ur.role_id JOIN users u ON u.id = ur.user_id JOIN tenants t ON t.id = u.tenant_id WHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05') ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC NULLS LAST, u.email ASC NULLS LAST, r.name ASC NULLS LAST, ur.id ASC NULLS LAST LIMIT 50
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: Dormant users holding Owner or Administrator access
Question: Specifically which accounts currently hold the two most powerful roles (Owner, Administrator) but haven't logged in recently — the concrete list of privileged accounts that look stale?
Grain: one row per user who holds a role named 'Owner' or 'Administrator' and is dormant
Columns:
- tenant name
- tenant status
- role name
- user email
- user status
- last login (timestamp, or 'never')
- days since last login (as of 2026-01-05; treat never-logged-in as the maximum/oldest)
- account age in days (2026-01-05 minus users.created_at)
Details: Source: user_roles joined to roles (name in ('Owner','Administrator')) joined to users (exclude deleted_at is not null) joined to tenants. Dormant = users.status = 'active' and (last_login_at is null or last_login_at < 2025-10-05). Compute days_since_last_login as 2026-01-05 minus last_login_at in days (null last_login_at should sort as the most stale, e.g. treat as a very large number or order nulls first — make this explicit and consistent in the ORDER BY). Compute account_age_days as 2026-01-05 minus users.created_at. Order by days_since_last_login descending (most stale first, nulls/never first), tenant name as tie-break. Cap at 50 rows; note total qualifying rows if more.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rows FROM user_roles ur JOIN roles r ON r.id = ur.role_id JOIN users u ON u.id = ur.user_id JOIN tenants t ON t.id = u.tenant_id WHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05') ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC, u.email ASC, r.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, tenant_status, role_name, user_email, user_status, last_login, days_since_last_login, account_age_days, total_qualifying_rows
submitSELECT t.name AS tenant_name, t.status AS tenant_status, r.name AS role_name, u.email AS user_email, u.status AS user_status, CASE WHEN u.last_login_at IS NULL THEN 'never' ELSE CAST(u.last_login_at AS varchar) END AS last_login, CASE WHEN u.last_login_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(u.last_login_at AS date)) END AS days_since_last_login, (DATE '2026-01-05' - CAST(u.created_at AS date)) AS account_age_days, COUNT(*) OVER() AS total_qualifying_rows FROM user_roles ur JOIN roles r ON r.id = ur.role_id JOIN users u ON u.id = ur.user_id JOIN tenants t ON t.id = u.tenant_id WHERE r.name IN ('Owner','Administrator') AND u.deleted_at IS NULL AND u.status = 'active' AND (u.last_login_at IS NULL OR u.last_login_at < TIMESTAMP '2025-10-05') ORDER BY u.last_login_at ASC NULLS FIRST, t.name ASC, u.email ASC, r.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.