Markdownで管理できるBIツール "Evidence" を使ってSecurity Hubレポートを作ってみる

npx degit evidence-dev/template my-project
cd my-project

npm install
# added 1287 packages in 21s
#
# $ npm run sources
#
# > my-evidence-project@0.0.1 sources
# > evidence sources
#
# ✔ Loading plugins & sources
# -----
#   [Processing] needful_things
#   needful_things ⚠ No results returned.
#   orders ✔ Finished, wrote 10000 rows.
# -----
# [INFO]:    Evaluated sources, saving manifest
# [INFO]:    ✅ Done!

npm run source
# > my-evidence-project@0.0.1 sources
# > evidence sources
#
# ✔ Loading plugins & sources
# -----
#   [Processing] needful_things
#   needful_things ⚠ No results returned.
#   orders ✔ Finished, wrote 10000 rows.
# -----
# [INFO]:    Evaluated sources, saving manifest
# [INFO]:    ✅ Done!

npm run dev
# > my-evidence-project@0.0.1 dev
# > evidence dev --open /
#
# Forced re-optimization of dependencies
#
#   VITE v5.4.14  ready in 1697 ms
#
#   ➜  Local:   http://localhost:3000/
#   ➜  Network: use --host to expose
#   ➜  press h + enter to show help

head all_afsbp.csv
# "ControlID","ControlTitle","Severity","AccountName","AccountID","ResourceType","ResourceID","ResourceNameTag","ComplianceStatus","RecordStatus","WorkflowStatus"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","MEDIUM","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","FAILED","ACTIVE","NEW"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","MEDIUM","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","FAILED","ACTIVE","NEW"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","MEDIUM","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:us-east-1:111111111111:certificate/xxxxx","","FAILED","ACTIVE","NEW"
# "ACM.1","ACM.1 Imported and ACM-issued certificates should be renewed after a specified time period","INFORMATIONAL","EXAMPLE_ACCOUNT","111111111111","AwsCertificateManagerCertificate","arn:aws:acm:ap-northeast-1:111111111111:certificate/xxxxx","","PASSED","ACTIVE","RESOLVED"

ls sources/securityhub
# connection.options.yaml
# connection.yaml

cat sources/securityhub/connection.yaml
# # This file was automatically generated
# name: securityhub
# type: csv
# options: {}

cat sources/securityhub/connection.options.yaml
# # This file was automatically generated
# # Do not edit this file manually; instead use npx evidence-sdk sources edit
#
# # It should *not* be source controlled, as it likely contain credentials or other sensitive configuration values.
# # Values in this file are base64 encoded; https://it-tools.tech/base64-string-converter has an excellent encoder / decoder tool.
# # Base64 is NOT encryption, and should not be treated as secure
# {}

ls -1 sources/securityhub
# all_afsbp.csv ← CSVを投下した
# connection.options.yaml
# connection.yaml

## Security Hub のCSV確認

```sql securityhub_10
  select *
  from securityhub.all_afsbp
  limit 10
```

<DataTable data={securityhub_10}/>

## 概要

```sql overview
SELECT
  COUNT(*) as num_all_findings,
  COUNT(CASE WHEN ComplianceStatus = 'PASSED' THEN 1 END) as num_passed_findings,
  COUNT(CASE WHEN ComplianceStatus = 'FAILED' THEN 1 END) as num_failed_findings,
  COUNT(CASE WHEN ComplianceStatus = 'PASSED' THEN 1 END) / COUNT(*) as security_score,
FROM securityhub.all_afsbp;
```

<BigValue
  data={overview}
  value=security_score
  fmt='0%'
  title="セキュリティスコア"
/>

- <Value data={overview} column=num_failed_findings/> / <Value data={overview} column=num_all_findings/> の検出結果が不合格

## 重要度ごとの不合格

※非表示の重要度は項目数0

```sql failed_per_severity
SELECT
  Severity,
  COUNT(*) as failed_count
FROM securityhub.all_afsbp
WHERE ComplianceStatus = 'FAILED'
GROUP BY Severity
ORDER BY
  CASE
    WHEN Severity = 'CRITICAL' THEN 1
    WHEN Severity = 'HIGH' THEN 2
    WHEN Severity = 'MEDIUM' THEN 3
    WHEN Severity = 'LOW' THEN 4
    ELSE 5
  END;
```

<DataTable data={failed_per_severity}/>

## リソースタイプごとの不合格数

```sql failed_per_resourcetype
SELECT
  ResourceType,
  Severity,
  COUNT(*) as failed_count
FROM securityhub.all_afsbp
WHERE ComplianceStatus = 'FAILED'
GROUP BY ResourceType, Severity;
```

<BarChart
  data={failed_per_resourcetype}
  x=ResourceType
  y=failed_count
  series=Severity
  swapXY=true
  seriesOrder={["LOW","MEDIUM","HIGH","CRITICAL"]}
  colorPalette={[
    '#8deeee',
    '#eeb422',
    '#cd5555',
    '#8b0000',
    ]}
/>

## 重要度CRITICAL, HIGHの不合格

```sql failed_titles_critical_high
SELECT DISTINCT ControlTitle, Severity
FROM securityhub.all_afsbp
WHERE
  ComplianceStatus = 'FAILED'
  AND ( Severity = 'CRITICAL' OR Severity = 'HIGH' );
```

```sql failed_resources_critical_high
SELECT
  Severity,
  regexp_replace(ResourceID, '^arn:aws:[^:]*:[^:]*:[^:]*:', '') as Resource,
  ResourceNameTag,
  ComplianceStatus,
  WorkflowStatus,
  ControlTitle,
FROM securityhub.all_afsbp
WHERE
  ComplianceStatus = 'FAILED'
  AND ( Severity = 'CRITICAL' OR Severity = 'HIGH' );
```

{#each failed_titles_critical_high as control}

### ✅️️️ {control.ControlTitle}

- Severity: {control.Severity}

<DataTable data={failed_resources_critical_high.where(`ControlTitle = '${control.ControlTitle}'`)} rows=20 >
  <Column id=Resource title="Resource"/>
  <Column id=ResourceNameTag title="Name"/>
  <Column id=ComplianceStatus title="Compliance Status"/>
  <Column id=WorkflowStatus title="Workflow Status"/>
</DataTable>

{/each}