Opsfolio: Control Crosswalks Across Frameworks

surveilr ingest files --csv-transform-auto -r ingest   # ingest/SCF_2024.2.csv
surveilr shell ./stateless.sql

-- One SCF control, many frameworks: a UNION over the SCF crosswalk columns
CREATE VIEW compliance_regime_control AS
SELECT `SCF Domain` AS scf_domain,
       `SCF Control` AS scf_control,
       `Secure Controls Framework (SCF) Control Description` AS control_description,
       `SCF Control Question` AS control_question,
       "SCF #" AS control_code,
       `US HIPAA` AS control_id,
       'US HIPAA' AS control_type
FROM uniform_resource_scf_2024_2 WHERE `US HIPAA` != ''
UNION
SELECT `SCF Domain`, `SCF Control`,
       `Secure Controls Framework (SCF) Control Description`,
       `SCF Control Question`, "SCF #",
       `NIST 800-171A rev 3`, 'NIST'
FROM uniform_resource_scf_2024_2 WHERE `NIST 800-171A rev 3` != '';

surveilr shell --output table --cmd \
  "SELECT type, name FROM sqlite_master
   WHERE name LIKE 'uniform_resource_scf%' OR name LIKE '%_control'
   ORDER BY type, name;"

-- Synthetic illustration: SCF controls shared across the frameworks in scope.
-- compliance_regime_control is the view from Panel 1 (one row per SCF
-- control per framework); it is a table here so the query runs as written.
CREATE TABLE IF NOT EXISTS compliance_regime_control (
  scf_domain TEXT, scf_control TEXT, control_description TEXT,
  control_question TEXT, control_code TEXT, control_id TEXT, control_type TEXT
);

SELECT control_code,
       scf_control,
       COUNT(DISTINCT control_type)        AS frameworks,
       group_concat(DISTINCT control_type) AS covered_by
FROM compliance_regime_control
GROUP BY control_code, scf_control
HAVING COUNT(DISTINCT control_type) > 1
ORDER BY frameworks DESC, control_code
LIMIT 20;

-- Synthetic illustration: required controls with no evidence on record
-- (Expected ≠ Observed). evidence_link stands in for the mapping from a
-- control to the evidence files in your RSSD.
CREATE TABLE IF NOT EXISTS compliance_regime_control (
  scf_domain TEXT, scf_control TEXT, control_description TEXT,
  control_question TEXT, control_code TEXT, control_id TEXT, control_type TEXT
);
CREATE TABLE IF NOT EXISTS evidence_link (control_code TEXT NOT NULL, uri TEXT NOT NULL);

SELECT c.control_type,
       c.control_id,
       c.control_code,
       c.scf_control
FROM compliance_regime_control c
LEFT JOIN evidence_link e ON e.control_code = c.control_code
WHERE c.control_type = 'US HIPAA'
  AND e.control_code IS NULL
ORDER BY c.control_code;