How to Match Vulnerability Findings in AWS Inspector and ECR Repository Image Scan Findings with Images in ECR
Overview #
- Identify Vulnerabilities: Knowing which vulnerabilities affect your ECR images helps you prioritize security efforts, especially for images in use.
- Current Usage Tracking: Determine which of your ECR images are currently in use by your ECS clusters or other services, ensuring that any identified vulnerabilities are addressed promptly.
- Compliance and Audit: Maintain a compliant and auditable environment by having a clear view of your ECR images, their vulnerabilities, and their usage status.
- Resource Optimization: Optimize ECR resource management by identifying and possibly removing unused images that may still have vulnerabilities.
Required Tables #
aws_ecr_repository_images
: Contains metadata about ECR repository images.aws_ecr_repository_image_scan_findings
: Stores scan findings of ECR repository images.aws_inspector2_findings
: Holds detailed findings from Amazon Inspector, including vulnerability information.
How to get a comprehensive view of your AWS ECR images and their security statuses #
WITH unnested_findings AS (
SELECT
findings.arn,
findings.aws_account_id,
findings.description,
findings.inspector_score,
findings.severity,
findings.status,
findings.title,
findings.package_vulnerability_details,
findings.resources,
findings.request_region,
resource -> 'details' -> 'awsEcrContainerImage' ->> 'imageHash' AS image_hash
FROM
aws_inspector2_findings AS findings,
JSONB_ARRAY_ELEMENTS(findings.resources) AS resource
WHERE
resource ->> 'type' = 'AWS_ECR_CONTAINER_IMAGE'
)
SELECT
images.account_id AS image_account_id,
images.image_digest,
images.image_pushed_at,
images.image_size_in_bytes,
images.image_tags,
images.repository_name,
images.region AS image_region,
scan_findings.image_scan_findings,
scan_findings.image_scan_status AS scan_status,
findings.arn AS finding_arn,
findings.aws_account_id AS finding_account_id,
findings.description AS finding_description,
findings.inspector_score,
findings.severity AS finding_severity,
findings.status AS finding_status,
findings.title AS finding_title,
findings.package_vulnerability_details,
findings.resources
FROM aws_ecr_repository_images AS images
LEFT JOIN aws_ecr_repository_image_scan_findings AS scan_findings
ON images.image_digest = scan_findings.image_digest
LEFT JOIN unnested_findings AS findings
ON images.image_digest = findings.image_hash
ORDER BY images.image_pushed_at DESC;
Steps:
- Unnesting Findings:
- The
unnested_findings
CTE (Common Table Expression) extracts and unnests the findings from AWS Inspector. - It uses the
JSONB_ARRAY_ELEMENTS
function to handle the JSON array in theresources
column, which includes details about ECR container images. - This step ensures that each finding related to an ECR container image is represented as a separate row.
- Joining Tables:
- The main query selects relevant columns from the
aws_ecr_repository_images
,aws_ecr_repository_image_scan_findings
, andunnested_findings
tables. - The
LEFT JOIN
clause is used to combine these tables:- First Join: The
aws_ecr_repository_images
table is joined with theaws_ecr_repository_image_scan_findings
table using theimage_digest
column. This join retrieves the scan findings associated with each image. - Second Join: The
unnested_findings
CTE is then joined with theaws_ecr_repository_images
table using theimage_digest
andimage_hash
columns. This join associates the detailed vulnerability findings with the respective images.
Example Result #
image_account_id | image_digest | image_pushed_at | image_size_in_bytes | image_tags | repository_name | image_region | image_scan_findings | scan_status | finding_arn | finding_account_id | finding_description | inspector_score | finding_severity | finding_status | finding_title | package_vulnerability_details | resources |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
123456789012 | sha256:6c0c0a26c8fdc70b3a2a8b1c2d72c36f5b9b123de5f1428f0e8c0c9ff6c5bb8c | 2023-06-01 00:00:00 | 123456789 | {latest} | my-repo | us-west-2 | {"findings": [], "imageScanCompletedAt": "2023-06-01T00:00:00Z", "vulnerabilitySourceUpdatedAt": "2023-06-01T00:00:00Z"} | "COMPLETE" | arn:aws:inspector2:us-west-2:123456789012 | 123456789012 | Sample vulnerability finding | 8.5 | HIGH | ACTIVE | Test vulnerability | {"cvss": {"baseScore": 8.5, "vectorString": "CVSS:3.0/AV | [{"id": "arn:aws:ecr:us-west-2:123456789012 |
Customization #
Summary #
Written by Ron Shemesh
Ron is a Senior Software Engineer at CloudQuery and has a background in data science and now works as a senior software engineer at CloudQuery. He has experience working in Python, SQL, React, Java and R. At CloudQuery, Ron has worked on our range of integrations and several projects foundational to platform performance. He loves taking on a challenge and using it to improve his skills.