02 ยท Compliance-as-Code for NIST 800-53 with Audit-Ready Evidence

Code Repository / projects / 02-compliance-as-code-nist
Project 02 ยท AWS CloudFormation

Map NIST SP 800-53 Rev. 5 controls to code and hand auditors fresh evidence every morning.

README

Format: AWS CloudFormation (single template) ยท Est. cost: ~$5โ€“25/month (Config rule evaluations, Security Hub checks)

Turns NIST SP 800-53 Rev. 5 controls into code and produces audit-ready evidence every day.

AWS Config conformance pack (24 rules, each named nist-<control>-...)
        โ”‚                                  โ”‚
        โ”‚ NON_COMPLIANT                    โ”‚ daily 06:00 UTC
        โ–ผ                                  โ–ผ
EventBridge โ”€โ”€โ–ถ SNS drift alert     Lambda evidence collector โ”€โ”€โ–ถ S3 (KMS, Object Lock, versioned)
                                                                      โ”‚
Security Hub NIST 800-53 standard                         Glue table (partition projection)
                                                                      โ”‚
                                                         Athena named queries โ”€โ”€โ–ถ QuickSight
Control family Rules
AC-2, AC-6 unused credentials, no admin policies, no root keys
IA-2, IA-5 console MFA, root MFA, strong password policy
AU-2, AU-9, AU-11 multi-region trail, log validation, trail encryption, log retention
SC-7, SC-8 no open SSH, default SG closed, no public S3/RDS, TLS-only S3/ELB
SC-12, SC-28 EBS/RDS/S3 encryption, KMS rotation
SI-4, CP-9, CM-8 GuardDuty, Security Hub, backups, PITR, SSM-managed EC2

Prerequisites

  • AWS Config recorder enabled in the account/region (Control Tower does this)
  • AWS CLI v2

Deploy

aws cloudformation deploy \
  --stack-name nist-compliance \
  --template-file template.yaml \
  --capabilities CAPABILITY_IAM \
  --parameter-overrides NotificationEmail=you@example.com EnableSecurityHub=true

If Security Hub is already on, pass EnableSecurityHub=false.

Generate evidence now

aws lambda invoke --function-name nist-compliance-evidence-collector out.json && cat out.json

Then in Athena pick workgroup nist-800-53-evidence-wg โ†’ Saved queries โ†’ posture-by-control.

Auditor package

open-findings-for-auditor returns every non-compliant resource for the last 30 days with its NIST control ID. Export as CSV, or connect QuickSight to the Athena table for a live posture dashboard.

Clean up

The evidence bucket uses Object Lock (GOVERNANCE, 365 days) on purpose, so the bucket is retained.

aws cloudformation delete-stack --stack-name nist-compliance

To remove locked objects, a privileged user needs s3:BypassGovernanceRetention.

Extend it

  • Deploy org-wide as an organization conformance pack from the delegated admin account
  • Add custom Lambda / Guard rules for controls without a managed rule
  • Feed Audit Manager with the same evidence for a formal assessment

Files (2)

README.md ยท 63 lines
# 02 ยท Compliance-as-Code for NIST 800-53 / FedRAMP

**Format:** AWS CloudFormation (single template) ยท **Est. cost:** ~$5โ€“25/month (Config rule evaluations, Security Hub checks)

Turns NIST SP 800-53 Rev. 5 controls into code and produces **audit-ready evidence every day**.

```
AWS Config conformance pack (24 rules, each named nist-<control>-...)
        โ”‚                                  โ”‚
        โ”‚ NON_COMPLIANT                    โ”‚ daily 06:00 UTC
        โ–ผ                                  โ–ผ
EventBridge โ”€โ”€โ–ถ SNS drift alert     Lambda evidence collector โ”€โ”€โ–ถ S3 (KMS, Object Lock, versioned)
                                                                      โ”‚
Security Hub NIST 800-53 standard                         Glue table (partition projection)
                                                                      โ”‚
                                                         Athena named queries โ”€โ”€โ–ถ QuickSight
```

| Control family | Rules |
|---|---|
| AC-2, AC-6 | unused credentials, no admin policies, no root keys |
| IA-2, IA-5 | console MFA, root MFA, strong password policy |
| AU-2, AU-9, AU-11 | multi-region trail, log validation, trail encryption, log retention |
| SC-7, SC-8 | no open SSH, default SG closed, no public S3/RDS, TLS-only S3/ELB |
| SC-12, SC-28 | EBS/RDS/S3 encryption, KMS rotation |
| SI-4, CP-9, CM-8 | GuardDuty, Security Hub, backups, PITR, SSM-managed EC2 |

## Prerequisites
- AWS Config recorder enabled in the account/region (Control Tower does this)
- AWS CLI v2

## Deploy
```bash
aws cloudformation deploy \
  --stack-name nist-compliance \
  --template-file template.yaml \
  --capabilities CAPABILITY_IAM \
  --parameter-overrides NotificationEmail=you@example.com EnableSecurityHub=true
```
If Security Hub is already on, pass `EnableSecurityHub=false`.

## Generate evidence now
```bash
aws lambda invoke --function-name nist-compliance-evidence-collector out.json && cat out.json
```
Then in **Athena** pick workgroup `nist-800-53-evidence-wg` โ†’ *Saved queries* โ†’ `posture-by-control`.

## Auditor package
`open-findings-for-auditor` returns every non-compliant resource for the last 30 days with its NIST control ID.
Export as CSV, or connect QuickSight to the Athena table for a live posture dashboard.

## Clean up
The evidence bucket uses **Object Lock (GOVERNANCE, 365 days)** on purpose, so the bucket is retained.
```bash
aws cloudformation delete-stack --stack-name nist-compliance
```
To remove locked objects, a privileged user needs `s3:BypassGovernanceRetention`.

## Extend it
- Deploy org-wide as an **organization conformance pack** from the delegated admin account
- Add custom Lambda / Guard rules for controls without a managed rule
- Feed Audit Manager with the same evidence for a formal assessment
template.yaml ยท 546 lines
AWSTemplateFormatVersion: '2010-09-09'
Description: >-
  Compliance-as-code for NIST SP 800-53 Rev. 5. An AWS Config conformance pack maps
  managed rules to NIST controls, Security Hub runs the NIST 800-53 standard, and a
  daily evidence collector writes audit-ready JSON to S3 that Athena/QuickSight can query.

Parameters:
  PackName:
    Type: String
    Default: nist-800-53-evidence
    AllowedPattern: '[a-zA-Z][-a-zA-Z0-9]*'
  NotificationEmail:
    Type: String
    Default: ''
    Description: Optional email for compliance drift alerts (leave blank to skip).
  EnableSecurityHub:
    Type: String
    AllowedValues: ['true', 'false']
    Default: 'true'
    Description: Set to false if Security Hub is already enabled in this account/region.
  EvidenceRetentionDays:
    Type: Number
    Default: 2555
    Description: How long to keep evidence (2555 days ~ 7 years).

Conditions:
  HasEmail: !Not [!Equals [!Ref NotificationEmail, '']]
  CreateHub: !Equals [!Ref EnableSecurityHub, 'true']

Resources:
  # ---------------------------------------------------------------- detection
  NistConformancePack:
    Type: AWS::Config::ConformancePack
    Properties:
      ConformancePackName: !Ref PackName
      TemplateBody: |
        Resources:
          # AC-2 Account management
          NistIamUnusedCredentials:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ac-2-iam-user-unused-credentials
              Source: { Owner: AWS, SourceIdentifier: IAM_USER_UNUSED_CREDENTIALS_CHECK }
              InputParameters: { maxCredentialUsageAge: '90' }
          # AC-6 Least privilege
          NistNoAdminPolicies:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ac-6-iam-policy-no-admin-access
              Source: { Owner: AWS, SourceIdentifier: IAM_POLICY_NO_STATEMENTS_WITH_ADMIN_ACCESS }
          NistRootAccessKey:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ac-6-iam-root-access-key
              Source: { Owner: AWS, SourceIdentifier: IAM_ROOT_ACCESS_KEY_CHECK }
          # IA-2(1) MFA
          NistConsoleMfa:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ia-2-mfa-console-access
              Source: { Owner: AWS, SourceIdentifier: MFA_ENABLED_FOR_IAM_CONSOLE_ACCESS }
          NistRootMfa:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ia-2-root-account-mfa
              Source: { Owner: AWS, SourceIdentifier: ROOT_ACCOUNT_MFA_ENABLED }
          # IA-5(1) Authenticator management
          NistPasswordPolicy:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-ia-5-iam-password-policy
              Source: { Owner: AWS, SourceIdentifier: IAM_PASSWORD_POLICY }
              InputParameters:
                RequireUppercaseCharacters: 'true'
                RequireLowercaseCharacters: 'true'
                RequireSymbols: 'true'
                RequireNumbers: 'true'
                MinimumPasswordLength: '14'
                PasswordReusePrevention: '24'
                MaxPasswordAge: '90'
          # AU-2 / AU-12 Event logging
          NistMultiRegionTrail:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-au-2-multi-region-cloudtrail
              Source: { Owner: AWS, SourceIdentifier: MULTI_REGION_CLOUD_TRAIL_ENABLED }
          # AU-9 Protection of audit information
          NistTrailValidation:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-au-9-cloudtrail-log-validation
              Source: { Owner: AWS, SourceIdentifier: CLOUD_TRAIL_LOG_FILE_VALIDATION_ENABLED }
          NistTrailEncryption:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-au-9-cloudtrail-encryption
              Source: { Owner: AWS, SourceIdentifier: CLOUD_TRAIL_ENCRYPTION_ENABLED }
          # AU-11 Audit record retention
          NistLogRetention:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-au-11-cw-loggroup-retention
              Source: { Owner: AWS, SourceIdentifier: CW_LOGGROUP_RETENTION_PERIOD_CHECK }
              InputParameters: { MinRetentionTime: '365' }
          # SC-7 Boundary protection
          NistRestrictedSsh:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-7-restricted-ssh
              Source: { Owner: AWS, SourceIdentifier: INCOMING_SSH_DISABLED }
          NistDefaultSgClosed:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-7-default-sg-closed
              Source: { Owner: AWS, SourceIdentifier: VPC_DEFAULT_SECURITY_GROUP_CLOSED }
          NistS3PublicRead:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-7-s3-public-read-prohibited
              Source: { Owner: AWS, SourceIdentifier: S3_BUCKET_PUBLIC_READ_PROHIBITED }
          NistRdsPublic:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-7-rds-public-access
              Source: { Owner: AWS, SourceIdentifier: RDS_INSTANCE_PUBLIC_ACCESS_CHECK }
          # SC-8 Transmission confidentiality
          NistS3Tls:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-8-s3-ssl-requests-only
              Source: { Owner: AWS, SourceIdentifier: S3_BUCKET_SSL_REQUESTS_ONLY }
          NistElbTls:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-8-elb-https-listeners-only
              Source: { Owner: AWS, SourceIdentifier: ELB_TLS_HTTPS_LISTENERS_ONLY }
          # SC-12/SC-13/SC-28 Crypto + data at rest
          NistEbsEncrypted:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-28-encrypted-volumes
              Source: { Owner: AWS, SourceIdentifier: ENCRYPTED_VOLUMES }
          NistRdsEncrypted:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-28-rds-storage-encrypted
              Source: { Owner: AWS, SourceIdentifier: RDS_STORAGE_ENCRYPTED }
          NistS3Sse:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-28-s3-sse-enabled
              Source: { Owner: AWS, SourceIdentifier: S3_BUCKET_SERVER_SIDE_ENCRYPTION_ENABLED }
          NistKmsRotation:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-sc-12-kms-key-rotation
              Source: { Owner: AWS, SourceIdentifier: CMK_BACKING_KEY_ROTATION_ENABLED }
          # SI-4 System monitoring
          NistGuardDuty:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-si-4-guardduty-enabled
              Source: { Owner: AWS, SourceIdentifier: GUARDDUTY_ENABLED_CENTRALIZED }
          NistSecurityHub:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-si-4-securityhub-enabled
              Source: { Owner: AWS, SourceIdentifier: SECURITYHUB_ENABLED }
          # CP-9 System backup
          NistRdsBackup:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-cp-9-db-backup-enabled
              Source: { Owner: AWS, SourceIdentifier: DB_INSTANCE_BACKUP_ENABLED }
          NistDynamoPitr:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-cp-9-dynamodb-pitr-enabled
              Source: { Owner: AWS, SourceIdentifier: DYNAMODB_PITR_ENABLED }
          # CM-8 System component inventory
          NistSsmManaged:
            Type: AWS::Config::ConfigRule
            Properties:
              ConfigRuleName: nist-cm-8-ec2-managed-by-ssm
              Source: { Owner: AWS, SourceIdentifier: EC2_INSTANCE_MANAGED_BY_SSM }

  SecurityHub:
    Type: AWS::SecurityHub::Hub
    Condition: CreateHub
    Properties:
      EnableDefaultStandards: false
      ControlFindingGenerator: SECURITY_CONTROL

  # Conditional DependsOn trick: the Standard waits for the Hub only when we create it.
  HubReady:
    Type: AWS::CloudFormation::WaitConditionHandle
    Metadata:
      HubRef: !If [CreateHub, !Ref SecurityHub, '']

  Nist80053Standard:
    Type: AWS::SecurityHub::Standard
    DependsOn: HubReady
    Properties:
      StandardsArn: !Sub arn:${AWS::Partition}:securityhub:${AWS::Region}::standards/nist-800-53/v/5.0.0

  # ---------------------------------------------------------------- evidence store
  EvidenceKey:
    Type: AWS::KMS::Key
    Properties:
      Description: Encrypts NIST compliance evidence
      EnableKeyRotation: true
      KeyPolicy:
        Version: '2012-10-17'
        Statement:
          - Sid: AccountAdmin
            Effect: Allow
            Principal: { AWS: !Sub 'arn:${AWS::Partition}:iam::${AWS::AccountId}:root' }
            Action: 'kms:*'
            Resource: '*'
          - Sid: AllowEventBridgeToPublishEncryptedSns
            Effect: Allow
            Principal: { Service: events.amazonaws.com }
            Action: [kms:Decrypt, kms:GenerateDataKey]
            Resource: '*'

  EvidenceBucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: aws:kms
              KMSMasterKeyID: !GetAtt EvidenceKey.Arn
            BucketKeyEnabled: true
      VersioningConfiguration: { Status: Enabled }
      ObjectLockEnabled: true
      ObjectLockConfiguration:
        ObjectLockEnabled: Enabled
        Rule:
          DefaultRetention: { Mode: GOVERNANCE, Days: 365 }
      PublicAccessBlockConfiguration:
        BlockPublicAcls: true
        BlockPublicPolicy: true
        IgnorePublicAcls: true
        RestrictPublicBuckets: true
      LifecycleConfiguration:
        Rules:
          - Id: retain-evidence
            Status: Enabled
            Prefix: evidence/
            Transitions:
              - { StorageClass: GLACIER_IR, TransitionInDays: 90 }
            ExpirationInDays: !Ref EvidenceRetentionDays
          - Id: athena-results
            Status: Enabled
            Prefix: athena-results/
            ExpirationInDays: 30

  EvidenceBucketPolicy:
    Type: AWS::S3::BucketPolicy
    Properties:
      Bucket: !Ref EvidenceBucket
      PolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Sid: DenyInsecureTransport
            Effect: Deny
            Principal: '*'
            Action: 's3:*'
            Resource:
              - !GetAtt EvidenceBucket.Arn
              - !Sub '${EvidenceBucket.Arn}/*'
            Condition: { Bool: { 'aws:SecureTransport': 'false' } }

  # ---------------------------------------------------------------- evidence collector
  CollectorRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal: { Service: lambda.amazonaws.com }
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - !Sub arn:${AWS::Partition}:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
      Policies:
        - PolicyName: collect-evidence
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - config:DescribeConformancePackCompliance
                  - config:GetConformancePackComplianceDetails
                Resource: '*'
              - Effect: Allow
                Action: s3:PutObject
                Resource: !Sub '${EvidenceBucket.Arn}/evidence/*'
              - Effect: Allow
                Action: [kms:GenerateDataKey, kms:Encrypt]
                Resource: !GetAtt EvidenceKey.Arn

  CollectorLogs:
    Type: AWS::Logs::LogGroup
    Properties:
      LogGroupName: !Sub /aws/lambda/${AWS::StackName}-evidence-collector
      RetentionInDays: 365

  EvidenceCollector:
    Type: AWS::Lambda::Function
    DependsOn: CollectorLogs
    Properties:
      FunctionName: !Sub ${AWS::StackName}-evidence-collector
      Runtime: python3.12
      Handler: index.handler
      Timeout: 300
      MemorySize: 256
      Role: !GetAtt CollectorRole.Arn
      Environment:
        Variables:
          PACK_NAME: !Ref PackName
          BUCKET: !Ref EvidenceBucket
      Code:
        ZipFile: |
          import datetime, json, os, re
          import boto3

          cfg = boto3.client("config")
          s3 = boto3.client("s3")
          PACK = os.environ["PACK_NAME"]
          BUCKET = os.environ["BUCKET"]
          # rule names look like nist-<family>-<n>-<desc>-conformance-pack-<suffix>
          CONTROL_RE = re.compile(r"^nist-([a-z]{2})-(\d+)-")

          def control_for(rule):
              m = CONTROL_RE.match(rule)
              return f"{m.group(1).upper()}-{m.group(2)}" if m else "UNMAPPED"

          def rules_in_pack():
              token, rules = None, []
              while True:
                  kw = {"ConformancePackName": PACK}
                  if token: kw["NextToken"] = token
                  resp = cfg.describe_conformance_pack_compliance(**kw)
                  rules += resp["ConformancePackRuleComplianceList"]
                  token = resp.get("NextToken")
                  if not token: return rules

          def details(rule):
              token = None
              while True:
                  kw = {"ConformancePackName": PACK, "Filters": {"ConfigRuleNames": [rule]}, "Limit": 100}
                  if token: kw["NextToken"] = token
                  resp = cfg.get_conformance_pack_compliance_details(**kw)
                  yield from resp["ConformancePackRuleEvaluationResults"]
                  token = resp.get("NextToken")
                  if not token: return

          def handler(event, context):
              now = datetime.datetime.now(datetime.timezone.utc)
              lines, summary = [], {}
              for r in rules_in_pack():
                  rule = r["ConfigRuleName"]
                  control = control_for(rule)
                  for ev in details(rule):
                      q = ev["EvaluationResultIdentifier"]["EvaluationResultQualifier"]
                      status = ev["ComplianceType"]
                      lines.append(json.dumps({
                          "collected_at": now.isoformat(),
                          "control_id": control,
                          "config_rule": rule,
                          "resource_type": q.get("ResourceType"),
                          "resource_id": q.get("ResourceId"),
                          "status": status,
                          "recorded_at": ev["ConfigRuleInvokedTime"].isoformat() if ev.get("ConfigRuleInvokedTime") else None,
                          "annotation": ev.get("Annotation"),
                      }))
                      summary[status] = summary.get(status, 0) + 1
              key = f"evidence/dt={now:%Y-%m-%d}/evidence-{now:%H%M%S}.json"
              s3.put_object(Bucket=BUCKET, Key=key, Body="\n".join(lines).encode())
              print(json.dumps({"key": key, "summary": summary}))
              return {"key": key, "summary": summary}

  DailySchedule:
    Type: AWS::Events::Rule
    Properties:
      Description: Collect NIST evidence daily
      ScheduleExpression: cron(0 6 * * ? *)
      Targets:
        - Id: collector
          Arn: !GetAtt EvidenceCollector.Arn

  SchedulePermission:
    Type: AWS::Lambda::Permission
    Properties:
      FunctionName: !Ref EvidenceCollector
      Action: lambda:InvokeFunction
      Principal: events.amazonaws.com
      SourceArn: !GetAtt DailySchedule.Arn

  # ---------------------------------------------------------------- drift alerts
  DriftTopic:
    Type: AWS::SNS::Topic
    Properties:
      DisplayName: NIST compliance drift
      KmsMasterKeyId: !Ref EvidenceKey

  DriftTopicPolicy:
    Type: AWS::SNS::TopicPolicy
    Properties:
      Topics: [!Ref DriftTopic]
      PolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal: { Service: events.amazonaws.com }
            Action: sns:Publish
            Resource: !Ref DriftTopic

  DriftEmail:
    Type: AWS::SNS::Subscription
    Condition: HasEmail
    Properties:
      TopicArn: !Ref DriftTopic
      Protocol: email
      Endpoint: !Ref NotificationEmail

  DriftRule:
    Type: AWS::Events::Rule
    Properties:
      Description: Alert when any NIST-mapped rule becomes NON_COMPLIANT
      EventPattern:
        source: [aws.config]
        detail-type: [Config Rules Compliance Change]
        detail:
          configRuleName: [{ prefix: nist- }]
          newEvaluationResult:
            complianceType: [NON_COMPLIANT]
      Targets:
        - Id: sns
          Arn: !Ref DriftTopic
          InputTransformer:
            InputPathsMap:
              rule: $.detail.configRuleName
              rid: $.detail.resourceId
              rtype: $.detail.resourceType
            InputTemplate: '"NIST drift: <rule> is NON_COMPLIANT for <rtype> <rid>"'

  # ---------------------------------------------------------------- query layer
  EvidenceDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: !Sub ${PackName}_db
        Description: NIST 800-53 evidence

  EvidenceTable:
    Type: AWS::Glue::Table
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref EvidenceDatabase
      TableInput:
        Name: evidence
        TableType: EXTERNAL_TABLE
        PartitionKeys:
          - { Name: dt, Type: string }
        Parameters:
          projection.enabled: 'true'
          projection.dt.type: date
          projection.dt.format: yyyy-MM-dd
          projection.dt.range: '2024-01-01,NOW'
          storage.location.template: !Sub 's3://${EvidenceBucket}/evidence/dt=${!dt}/'
        StorageDescriptor:
          Location: !Sub 's3://${EvidenceBucket}/evidence/'
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          SerdeInfo:
            SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
          Columns:
            - { Name: collected_at, Type: string }
            - { Name: control_id, Type: string }
            - { Name: config_rule, Type: string }
            - { Name: resource_type, Type: string }
            - { Name: resource_id, Type: string }
            - { Name: status, Type: string }
            - { Name: recorded_at, Type: string }
            - { Name: annotation, Type: string }

  AthenaWorkgroup:
    Type: AWS::Athena::WorkGroup
    Properties:
      Name: !Sub ${PackName}-wg
      RecursiveDeleteOption: true
      WorkGroupConfiguration:
        EnforceWorkGroupConfiguration: true
        ResultConfiguration:
          OutputLocation: !Sub 's3://${EvidenceBucket}/athena-results/'
          EncryptionConfiguration:
            EncryptionOption: SSE_KMS
            KmsKey: !GetAtt EvidenceKey.Arn

  QueryPostureByControl:
    Type: AWS::Athena::NamedQuery
    Properties:
      Name: posture-by-control
      Description: Latest compliance percentage per NIST control
      Database: !Ref EvidenceDatabase
      WorkGroup: !Ref AthenaWorkgroup
      QueryString: |
        WITH latest AS (
          SELECT * FROM evidence WHERE dt = (SELECT max(dt) FROM evidence)
        )
        SELECT control_id,
               count_if(status = 'COMPLIANT')     AS compliant,
               count_if(status = 'NON_COMPLIANT') AS non_compliant,
               round(100.0 * count_if(status = 'COMPLIANT') / nullif(count(*), 0), 1) AS pct_compliant
        FROM latest
        GROUP BY control_id
        ORDER BY pct_compliant ASC;

  QueryOpenFindings:
    Type: AWS::Athena::NamedQuery
    Properties:
      Name: open-findings-for-auditor
      Description: Every non-compliant resource with its control, for the auditor package
      Database: !Ref EvidenceDatabase
      WorkGroup: !Ref AthenaWorkgroup
      QueryString: |
        SELECT dt, control_id, config_rule, resource_type, resource_id, annotation
        FROM evidence
        WHERE status = 'NON_COMPLIANT'
          AND dt >= date_format(current_date - interval '30' day, '%Y-%m-%d')
        ORDER BY dt DESC, control_id;

Outputs:
  EvidenceBucketName:
    Value: !Ref EvidenceBucket
  CollectorFunction:
    Value: !Ref EvidenceCollector
  AthenaWorkgroupName:
    Value: !Ref AthenaWorkgroup
  DriftTopicArn:
    Value: !Ref DriftTopic

Build it yourself

Download the folder, run npm install (CDK) or aws cloudformation deploy (CloudFormation), and follow the README.