aws
solutions
transformations

How to Match Vulnerability Findings in AWS Inspector and ECR Repository Image Scan Findings with Images in ECR

Ron Shemesh

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.
  1. Identify Vulnerabilities: Knowing which vulnerabilities affect your ECR images helps you prioritize security efforts, especially for images in use.
  2. 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.
  3. Compliance and Audit: Maintain a compliant and auditable environment by having a clear view of your ECR images, their vulnerabilities, and their usage status.
  4. 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:

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:
  1. 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 the resources 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.
  1. Joining Tables:
    • The main query selects relevant columns from the aws_ecr_repository_images, aws_ecr_repository_image_scan_findings, and unnested_findings tables.
    • The LEFT JOIN clause is used to combine these tables:
      • First Join: The aws_ecr_repository_images table is joined with the aws_ecr_repository_image_scan_findings table using the image_digest column. This join retrieves the scan findings associated with each image.
      • Second Join: The unnested_findings CTE is then joined with the aws_ecr_repository_images table using the image_digest and image_hash columns. This join associates the detailed vulnerability findings with the respective images.

Example Result #

image_account_idimage_digestimage_pushed_atimage_size_in_bytesimage_tagsrepository_nameimage_regionimage_scan_findingsscan_statusfinding_arnfinding_account_idfinding_descriptioninspector_scorefinding_severityfinding_statusfinding_titlepackage_vulnerability_detailsresources
123456789012sha256:6c0c0a26c8fdc70b3a2a8b1c2d72c36f5b9b123de5f1428f0e8c0c9ff6c5bb8c2023-06-01 00:00:00123456789{latest}my-repous-west-2{"findings": [], "imageScanCompletedAt": "2023-06-01T00:00:00Z", "vulnerabilitySourceUpdatedAt": "2023-06-01T00:00:00Z"}"COMPLETE"arn:aws:inspector2:us-west-2:123456789012/abc123123456789012Sample vulnerability finding8.5HIGHACTIVETest vulnerability{"cvss": {"baseScore": 8.5, "vectorString": "CVSS:3.0/AV/AC/PR/UI/S/C/I"}, "source": "NVD"}[{"id": "arn:aws:ecr:us-west-2:123456789012/my-repo@sha256:6c0c0a26c8fdc70b3a2a8b1c2d72c36f5b9b123de5f1428f0e8c0c9ff6c5bb8c", "type": "AWS_ECR_CONTAINER_IMAGE", "details": {"awsEcrContainerImage": {"imageHash": "sha256:6c0c0a26c8fdc70b3a2a8b1c2d72c36f5b9b123de5f1428f0e8c0c9ff6c5bb8c"}}}]
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 Community.
Ron Shemesh

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.

Turn cloud chaos into clarity

Find out how CloudQuery can help you get clarity from a chaotic cloud environment with a personalized conversation and demo.

Join our mailing list

Subscribe to our newsletter to make sure you don't miss any updates.

Legal

© 2024 CloudQuery, Inc. All rights reserved.

We use tracking cookies to understand how you use the product and help us improve it. Please accept cookies to help us improve. You can always opt out later via the link in the footer.