毎回、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