steampipe plugin install aws

Table: aws_ecr_image - Query Amazon ECR Images using SQL

The Amazon Elastic Container Registry (ECR) Images are Docker images that are stored within AWS's managed and highly available registry. ECR Images allow you to easily store, manage, and deploy Docker container images in a secure environment. They are integrated with AWS Identity and Access Management (IAM) for resource-level control and support for private Docker repositories.

Table Usage Guide

The aws_ecr_image table in Steampipe provides you with information about Images within Amazon Elastic Container Registry (ECR). This table allows you, as a DevOps engineer, to query image-specific details, including image tags, push timestamps, image sizes, and associated metadata. You can utilize this table to gather insights on images, such as image scan findings, image vulnerability details, verification of image tags, and more. The schema outlines the various attributes of the ECR image for you, including the image digest, image tags, image scan status, and associated tags.

Examples

Basic info

Explore the details of your AWS Elastic Container Registry (ECR) images, like when they were last updated and their size, to better manage your resources. This can help in identifying outdated or oversized images, thus optimizing your ECR utilization.

select
repository_name,
image_digest,
image_pushed_at,
image_size_in_bytes,
registry_id,
image_scan_status,
image_tags
from
aws_ecr_image;
select
repository_name,
image_digest,
image_pushed_at,
image_size_in_bytes,
registry_id,
image_scan_status,
image_tags
from
aws_ecr_image;

List image scan findings

Identify instances where your repository images might have vulnerabilities by examining the severity of scan findings. This allows you to assess the security of your images and take necessary actions based on the severity of the findings.

select
repository_name,
image_scan_findings_summary ->> 'FindingSeverityCounts' as finding_severity_counts,
image_scan_findings_summary ->> 'ImageScanCompletedAt' as image_scan_completed_at,
image_scan_findings_summary ->> 'VulnerabilitySourceUpdatedAt' as vulnerability_source_updated_at
from
aws_ecr_image;
select
repository_name,
json_extract(
image_scan_findings_summary,
'$.FindingSeverityCounts'
) as finding_severity_counts,
json_extract(
image_scan_findings_summary,
'$.ImageScanCompletedAt'
) as image_scan_completed_at,
json_extract(
image_scan_findings_summary,
'$.VulnerabilitySourceUpdatedAt'
) as vulnerability_source_updated_at
from
aws_ecr_image;

List image tags for the images

Explore which image tags are associated with the images in your AWS ECR repositories. This can help you manage and organize your resources more effectively.

select
repository_name,
registry_id,
image_digest,
image_tags
from
aws_ecr_image;
select
repository_name,
registry_id,
image_digest,
image_tags
from
aws_ecr_image;

List images pushed in last 10 days for a repository

Determine the images that have been uploaded to a specific repository in the last 10 days. This is useful for tracking recent updates or additions to the repository.

select
repository_name,
image_digest,
image_pushed_at,
image_size_in_bytes
from
aws_ecr_image
where
image_pushed_at >= now() - interval '10' day
and repository_name = 'test1';
select
repository_name,
image_digest,
image_pushed_at,
image_size_in_bytes
from
aws_ecr_image
where
image_pushed_at >= datetime('now', '-10 day')
and repository_name = 'test1';

List images for repositories created in the last 20 days

Explore recently created repositories and the images they contain. This query is useful for keeping track of new content and managing resources within a 20-day timeframe.

select
i.repository_name as repository_name,
r.repository_uri as repository_uri,
i.image_digest as image_digest,
i.image_tags as image_tags
from
aws_ecr_image as i,
aws_ecr_repository as r
where
i.repository_name = r.repository_name
and r.created_at >= now() - interval '20' day;
select
i.repository_name as repository_name,
r.repository_uri as repository_uri,
i.image_digest as image_digest,
i.image_tags as image_tags
from
aws_ecr_image as i,
aws_ecr_repository as r
where
i.repository_name = r.repository_name
and r.created_at >= datetime('now', '-20 days');

Get repository policy for each image's repository

Determine the access policies associated with each image's repository in AWS Elastic Container Registry (ECR). This can help to identify potential security risks, such as open access to sensitive images.

select
i.repository_name as repository_name,
r.repository_uri as repository_uri,
i.image_digest as image_digest,
i.image_tags as image_tags,
s ->> 'Effect' as effect,
s ->> 'Action' as action,
s ->> 'Condition' as condition,
s ->> 'Principal' as principal
from
aws_ecr_image as i,
aws_ecr_repository as r,
jsonb_array_elements(r.policy -> 'Statement') as s
where
i.repository_name = r.repository_name;
select
i.repository_name as repository_name,
r.repository_uri as repository_uri,
i.image_digest as image_digest,
i.image_tags as image_tags,
json_extract(s.value, '$.Effect') as effect,
json_extract(s.value, '$.Action') as action,
json_extract(s.value, '$.Condition') as condition,
json_extract(s.value, '$.Principal') as principal
from
aws_ecr_image as i,
aws_ecr_repository as r,
json_each(r.policy, '$.Statement') as s
where
i.repository_name = r.repository_name;

Scan images with trivy for a particular repository

This example is used to analyze the security vulnerabilities of images in a specific repository. It helps in proactively identifying and addressing potential security issues, thereby enhancing the overall safety of your applications.

select
artifact_name,
artifact_type,
metadata,
results
from
trivy_scan_artifact as a,
aws_ecr_image as i
where
artifact_name = image_uri
and repository_name = 'hello';
select
artifact_name,
artifact_type,
metadata,
results
from
trivy_scan_artifact as a,
aws_ecr_image as i
where
artifact_name = image_uri
and repository_name = 'hello';

Schema for aws_ecr_image

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
artifact_media_typetextThe artifact media type of the image.
image_digesttextThe sha256 digest of the image manifest.
image_manifest_media_typetextThe media type of the image manifest.
image_pushed_attimestamp with time zoneThe date and time, expressed in standard JavaScript date format, at which the current image was pushed to the repository.
image_scan_findings_summaryjsonbA summary of the last completed image scan.
image_scan_statusjsonbThe current state of the scan.
image_size_in_bytesbigintThe size, in bytes, of the image in the repository.
image_tagsjsonbThe list of tags associated with this image.
image_uritextThe URI for the image.
last_recorded_pull_timetimestamp with time zoneThe date and time, expressed in standard JavaScript date format, when Amazon ECR recorded the last image pull.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
registry_idtext=The Amazon Web Services account ID associated with the registry to which this image belongs.
repository_nametext=The name of the repository.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_ecr_image