aws
solutions
transformations
How to Match Vulnerability Findings in AWS Inspector and ECR Repository Image Scan Findings with Images in ECR
![Ron Shemesh](/images/people/ronsha.jpeg)
Ron Shemesh •
Overview #
In managing AWS environments, it’s crucial to ensure the security of your container images used across all of your services. By joining tables from AWS Inspector, Amazon Elastic Container Registry (ECR) Repository Image Scan Findings, and ECR images, you can achieve several objectives.
- 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.
CloudQuery allows you to access AWS cloud data, enabling you to analyze and manage your environment more effectively. With CloudQuery, you can explore ECR containers to identify vulnerabilities. Additionally, you can easily export your AWS data to a PostgreSQL database, (or any other database), facilitating comprehensive analysis and visualization.
Next, let's explore how to match vulnerability findings from AWS Inspector and AWS ECR Repository Image Scan Findings with your actual images in ECR. So you can understand which vulnerabilities affect your images and whether those images are currently in use can help prioritize security efforts, particularly in live environments like ECS clusters.
Required Tables #
To run this query, you will need to sync the following tables from AWS into your data destination:
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 #
The query below aims to provide a comprehensive view of the ECR images and their security statuses by joining tables from AWS Inspector, ECR Repository Image Scan Findings, and ECR images. It extracts relevant columns, such as image metadata, scan findings, and detailed vulnerability information, to help you understand the security posture of their container images.
Here’s the full query:
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 |
Query table provides a comprehensive view of ECR images, their associated vulnerabilities, and scan findings. This helps users quickly identify security issues, understand their impact, and determine if affected images are currently in use, enabling better prioritization of security efforts.
Customization #
You can modify the columns selected in the query to suit your needs. The query focuses on joining the tables and extracting key information, but you can adjust it to display any other relevant fields based on your requirements.
Summary #
In managing AWS environments, ensuring the security of container images across services is vital. By combining data from AWS Inspector, ECR Repository Image Scan Findings, and ECR images, you can identify vulnerabilities, track current usage, ensure compliance, and optimize resources.
In this post, we showed you how to match vulnerability findings from AWS Inspector and ECR Repository Image Scan Findings with actual ECR images. By understanding which vulnerabilities affect your images and their current usage, you can prioritize security efforts effectively, especially for live environments like ECS clusters. This process involves unnesting findings and joining relevant tables to provide a comprehensive view of your ECR images and their security status.
If you're ready to start getting immediate insights into vulnerabilities and misconfigurations in your AWS environment, try CloudQuery for free.
If you have any questions or want to connect with our engineering team, contact us or join our Discord channel.