Continuous Compliance as a Service: Expert-Led Programs

# 1. Your team: install the collector and confirm the version
brew tap surveilr/tap && brew install surveilr
surveilr --version

# 2. Your team: read each bundle (plain JSONL, read-only queries), then run it
cat cloud-steampipe-surveilr.jsonl | surveilr ingest tasks
cat server-evidence-surveilr.jsonl  | surveilr ingest tasks

# 3. Your team: dump and encrypt the evidence for transfer (see Panel 2)
sqlite3 resource-surveillance.sqlite.db .dump | gzip -c > evidence.db.txt.gz
age -r <analyst-public-key> -o evidence.db.txt.gz.age evidence.db.txt.gz

# 4. Netspective analyst (human review): inspect what was captured
surveilr shell -d resource-surveillance.sqlite.db --output table \
  --cmd "SELECT nature, COUNT(*) AS items FROM uniform_resource GROUP BY nature;"

# Compact text dump for transfer
sqlite3 resource-surveillance.sqlite.db .dump | gzip -c > evidence.db.txt.gz

# Encrypt to the analyst's public key (age); only the .age file is sent
age -r <analyst-public-key> -o evidence.db.txt.gz.age evidence.db.txt.gz

# Fingerprint the encrypted file; the analyst checks it on receipt
sha256sum evidence.db.txt.gz.age > evidence.db.txt.gz.age.sha256
sha256sum --check evidence.db.txt.gz.age.sha256

-- Synthetic illustration: evidence the engagement asked for that is not in
-- the database yet. evidence_request is the analyst's checklist; the capture
-- is matched on the uri the evidence engine records for each file or task output.
CREATE TEMP TABLE IF NOT EXISTS uniform_resource (uri TEXT, nature TEXT, content TEXT);
CREATE TEMP TABLE IF NOT EXISTS evidence_request (
  control_code TEXT NOT NULL,
  evidence     TEXT NOT NULL,
  uri_pattern  TEXT NOT NULL      -- e.g. '%iam-password-policy%'
);

SELECT r.control_code,
       r.evidence,
       COUNT(u.uri) AS captured_items
FROM evidence_request r
LEFT JOIN uniform_resource u ON u.uri LIKE r.uri_pattern
GROUP BY r.control_code, r.evidence
HAVING COUNT(u.uri) = 0
ORDER BY r.control_code;

-- Synthetic illustration: policies past their annual review, and policies
-- no control points at. The two tables stand in for your policy register.
CREATE TEMP TABLE IF NOT EXISTS policy (
  policy_id     TEXT PRIMARY KEY,
  title         TEXT NOT NULL,
  owner_role    TEXT NOT NULL,
  last_reviewed TEXT NOT NULL      -- ISO date, e.g. '2025-09-30'
);
CREATE TEMP TABLE IF NOT EXISTS policy_control (policy_id TEXT NOT NULL, control_code TEXT NOT NULL);

SELECT p.policy_id,
       p.title,
       p.owner_role,
       p.last_reviewed,
       CASE WHEN p.last_reviewed < date('now', '-365 days') THEN 'review overdue' ELSE 'current' END AS review,
       COUNT(pc.control_code) AS mapped_controls
FROM policy p
LEFT JOIN policy_control pc ON pc.policy_id = p.policy_id
GROUP BY p.policy_id
HAVING review = 'review overdue' OR mapped_controls = 0
ORDER BY p.last_reviewed;