In July, we added National Institute of Standards and Technology (NIST) Special Publication 800-53r4 controls mappings to version 2.0.2 of the reporting data model for SQL Query Export reports. NIST 800-53 is a publication that develops a set of security controls standards that are designed to aid organizations in protecting themselves from an array of threats.
What does this mean for you? Well, now you can measure your compliance against these controls by writing SQL queries. For example, say you want to know how many assets fail or comply with a certain control:
SELECT ncm.control_name, SUM(fr.noncompliant_assets) AS noncompliant_assets, SUM(fr.compliant_assets) AS compliant_assets FROM fact_policy_rule fr JOIN dim_policy_rule_cce_platform_nist_control_mapping ncm ON ncm.rule_id = fr.rule_id AND ncm.rule_scope = fr.scope WHERE ncm.control_name LIKE `AC-%` GROUP BY ncm.control_name ORDER BY ncm.control_name ASC
Or this example shows how you can list your least compliant policy rules (most failed assets) and which CCEs and controls they map to:
SELECT p.title AS policy_name, dpr.title AS rule_name, ncm.cce_item_id, ncm.control_name, fr.noncompliant_assets, fr.compliant_assets FROM fact_policy_rule fr JOIN dim_policy_rule dpr USING (rule_id, scope, policy_id) JOIN dim_policy p USING (policy_id, scope) JOIN dim_policy_rule_cce_platform_nist_control_mapping ncm ON ncm.rule_id = fr.rule_id AND ncm.rule_scope = fr.scope ORDER BY fr.noncompliant_assets DESC