セキュリティあれこれ

セキュリティ関連のメモ

Amazon Inspector の検出結果を Athena で確認

毎回、Amazon Inspector Classic の検出結果を AWS コンソールから CSV 形式でダウンロードし、スプレにコピーし、レポートフォーマットするのが面倒なので、Athena でいい感じのフォーマットに出力できないか検証しました。

Amazon Inspector Classic の検出結果を S3 に格納する設定はこちら

Amazon Inspector Classic の検出結果

下記のような JSON 形式になります。

{
  "version": "0",
  "id": "xxxxxx",
  "detail-type": "Security Hub Findings - Imported",
  "source": "aws.securityhub",
  "account": "xxxxxx",
  "time": "2022-01-17T12:44:34Z",
  "region": "ap-northeast-1",
  "resources": [
    "arn:aws:securityhub:ap-northeast-1::product/aws/inspector/inspector/ap-northeast-1/xxxxxx/xxxxxx"
  ],
  "detail": {
    "findings": [
      {
        "ProductArn": "arn:aws:securityhub:ap-northeast-1::product/aws/inspector",
        "Types": [
          "Software and Configuration Checks/Vulnerabilities/CVE-2021-3712"
        ],
        "Description": "ASN.1 strings are represented internally within OpenSSL as an ASN1_STRING structure which contains a buffer holding the string data and a field holding the buffer length. This contrasts with normal C strings which are repesented as a buffer for the string data which is terminated with a NUL (0) byte. Although not a strict requirement, ASN.1 strings that are parsed using OpenSSL's own \"d2i\" functions (and other similar parsing functions) as well as any string whose value has been set with the ASN1_STRING_set() function will additionally NUL terminate the byte array in the ASN1_STRING structure. However, it is possible for applications to directly construct valid ASN1_STRING structures which do not NUL terminate the byte array by directly setting the \"data\" and \"length\" fields in the ASN1_STRING array. This can also happen by using the ASN1_STRING_set0() function. Numerous OpenSSL functions that print ASN.1 data have been found to assume that the ASN1_STRING byte array will be NUL terminated, even though thi...",
        "ProductName": "Inspector",
        "SchemaVersion": "2018-10-08",
        "GeneratorId": "arn:aws:inspector:ap-northeast-1:xxxxxx:rulespackage/0-gHP9oWNT",
        "CreatedAt": "2022-01-16T13:07:36.361Z",
        "RecordState": "ACTIVE",
        "Title": "Instance i-xxxxxx is vulnerable to CVE-2021-3712",
        "Workflow": {
          "Status": "NEW"
        },
        "Severity": {
          "Normalized": 70,
          "Label": "HIGH",
          "Original": "9.0"
        },
        "Vulnerabilities": [
          {
            "Cvss": [
              {
                "Version": "V2",
                "BaseScore": 5.8,
                "BaseVector": "AV:N/AC:M/Au:N/C:P/I:N/A:P"
              },
              {
                "Version": "V3",
                "BaseScore": 7.4,
                "BaseVector": "CVSS:3.1/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:H"
              }
            ],
            "VulnerablePackages": [
              {
                "Version": "1.0.2k",
                "Epoch": "1",
                "Release": "19.amzn2.0.6",
                "Name": "openssl"
              },
              {
                "Version": "1.0.2k",
                "Epoch": "1",
                "Release": "19.amzn2.0.6",
                "Name": "openssl-libs"
              }
            ],
            "Id": "CVE-2021-3712"
          }
        ],
        "UpdatedAt": "2022-01-17T12:44:28.593Z",
        "CompanyName": "Amazon",
        "FindingProviderFields": {
          "Types": [
            "Software and Configuration Checks/Vulnerabilities/CVE-2021-3712"
          ],
          "Confidence": 10,
          "Severity": {
            "Normalized": 70,
            "Label": "HIGH",
            "Original": "9.0"
          }
        },
        "Confidence": 10,
        "WorkflowState": "NEW",
        "ProductFields": {
          "attributes/CVSS2_SCORE": "5.8",
          "aws/inspector/id": "CVE-2021-3712",
          "serviceAttributes/schemaVersion": "1",
          "aws/inspector/arn": "arn:aws:inspector:ap-northeast-1:xxxxxx:target/0-96hFji9U/template/0-POM9r8Qa/run/0-NOeUL3CE/finding/0-vMIlP502",
          "aws/inspector/ProductVersion": "1",
          "serviceAttributes/assessmentRunArn": "arn:aws:inspector:ap-northeast-1:xxxxxx:target/0-96hFji9U/template/0-POM9r8Qa/run/0-NOeUL3CE",
          "aws/inspector/RulesPackageName": "Common Vulnerabilities and Exposures",
          "attributes/package_name": "openssl-1:1.0.2k-19.amzn2.0.6, openssl-libs-1:1.0.2k-19.amzn2.0.6",
          "attributes/INSTANCE_ID": "i-02ced1f6739884076",
          "attributes/CVE_ID": "CVE-2021-3712",
          "serviceAttributes/rulesPackageArn": "arn:aws:inspector:ap-northeast-1:xxxxxx:rulespackage/0-gHP9oWNT",
          "attributes/CVSS3_SCORE": "7.4",
          "attributes/CVSS3_VECTOR": "CVSS:3.1/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:H",
          "attributes/CVSS2_VECTOR": "AV:N/AC:M/Au:N/C:P/I:N/A:P",
          "aws/securityhub/FindingId": "arn:aws:securityhub:ap-northeast-1::product/aws/inspector/inspector/ap-northeast-1/xxxxxx/xxxxxx",
          "aws/securityhub/ProductName": "Inspector",
          "aws/securityhub/CompanyName": "Amazon"
        },
        "AwsAccountId": "xxxxxx",
        "Region": "ap-northeast-1",
        "Remediation": {
          "Recommendation": {
            "Text": "Use your Operating System's update feature to update package openssl-1:1.0.2k-19.amzn2.0.6, openssl-libs-1:1.0.2k-19.amzn2.0.6. For more information see [https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2021-3712](https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2021-3712)"
          }
        },
        "Id": "inspector/ap-northeast-1/xxxxxx/xxxxxx",
        "Resources": [
          {
            "Partition": "aws",
            "Type": "AwsEc2Instance",
            "Details": {
              "AwsEc2Instance": {
                "VpcId": "vpc-xxxxxx",
                "ImageId": "ami-xxxxxx",
                "IpV4Addresses": [
                  "10.0.11.173"
                ],
                "SubnetId": "subnet-xxxxxx"
              }
            },
            "Region": "ap-northeast-1",
            "Id": "arn:aws:ec2:ap-northeast-1:xxxxxx:instance/i-xxxxxx",
            "Tags": {
              "Name": "test-ec2"
            }
          }
        ]
      }
    ]
  }
}

Athena で JSON 形式を出力できるようにする

Athena でテーブルを作成します。

CREATE EXTERNAL TABLE inspector (
`time` string,
`detail` struct<findings:array<struct<
`vulnerabilities`:array<struct<id:string>>,
`remediation`:struct<recommendation:struct<text:string>>,
`severity`:struct<label:string,original:string>,
`productFields`:struct<attributes_instance_id:string,attributes_cve_id:string,attributes_package_name:string>
>>>
)
PARTITIONED BY
(
datehour STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  "mapping.attributes_instance_id"="attributes/INSTANCE_ID",
  "mapping.attributes_cve_id"="attributes/CVE_ID",
  "mapping.attributes_package_name"="attributes/package_name"
  )
LOCATION 's3://[S3 bucket]/inspector/'
TBLPROPERTIES
(
 "projection.enabled" = "true",
 "projection.datehour.type" = "date",
 "projection.datehour.range" = "2022/01/01/00,NOW",
 "projection.datehour.format" = "yyyy/MM/dd/HH",
 "projection.datehour.interval" = "1",
 "projection.datehour.interval.unit" = "HOURS",
 "storage.location.template" = "s3://[S3 bucket]/inspector/${datehour}"
)

Athena でクエリを実行します。

SELECT
time,
detail.findings[1].productFields.attributes_instance_id,
detail.findings[1].severity.label,
detail.findings[1].severity.original,
detail.findings[1].vulnerabilities[1].id,
regexp_extract(detail.findings[1].remediation.recommendation.text, '([a-zA-Z0-9_]+)?-', 1),
detail.findings[1].productFields.attributes_package_name
FROM inspector WHERE datehour >= '2022/01/17'

クエリ結果

#    time    _col1   _col2   _col3   _col4   _col5   _col6
1  2022-01-17T12:44:34Z  i-xxxxxx    HIGH    9.0    CVE-2021-3712  openssl openssl-1:1.0.2k-19.amzn2.0.6, openssl-libs-1:1.0.2k-19.amzn2.0.6
2  2022-01-17T12:44:35Z  i-xxxxxx    MEDIUM  6.0    CVE-2021-37159 kernel  kernel-0:4.14.232-176.381.amzn2
3  2022-01-17T12:44:43Z  i-xxxxxx    MEDIUM  6.0    CVE-2018-16866 systemd systemd-0:219-57.amzn2.0.12, systemd-libs-0:219-57.amzn2.0.12, systemd-sysv-0:219-57.amzn2.0.12
4  2022-01-17T12:44:35Z  i-xxxxxx    HIGH    9.0    CVE-2021-38300 kernel  kernel-0:4.14.232-176.381.amzn2