steampipe plugin install awssteampipe plugin install aws

Table: aws_inspector2_finding

AWS Inspector Findings refer to the security assessment results generated by the AWS Inspector service. AWS Inspector is a security vulnerability assessment service that helps you discover potential security issues and vulnerabilities within your AWS resources.

When you run an assessment with AWS Inspector, it analyzes the target resources such as EC2 instances, ECS clusters, or RDS databases and generates findings that highlight security vulnerabilities, potential misconfigurations, and other security-related issues. These findings provide detailed information about the identified vulnerabilities, including severity levels, affected resources, and recommended remediation steps.

Examples

Basic info

select
  arn,
  description,
  fix_available,
  inspector_score,
  severity,
  finding_account_id
from
  aws_inspector2_finding;

List findings with high severity

select
  arn,
  source,
  vendor_severity,
  status,
  severity
from
  aws_inspector2_finding
where
  severity = 'HIGH';

Count the number of findings by severity

select
  severity,
  count(severity)
from
  aws_inspector2_finding
group by
  severity
order by
  severity;

List findings in last 10 days

select
  title,
  arn,
  severity
from
  aws_inspector2_finding
where
  last_observed_at >= now() - interval '10' day;

List suppressed findings

select
  arn,
  status,
  type,
  resources,
  vulnerable_packages
from
  aws_inspector2_finding
where
  status = 'SUPPRESSED';

List package vulnerability findings

select
  arn,
  status,
  type,
  resources,
  vulnerable_packages
from
  aws_inspector2_finding
where
  type = 'PACKAGE_VULNERABILITY';

Get resource details of findings

select
  f.arn as finding_arn,
  r ->> 'Id' as resource_id,
  r ->> 'Type' as resource_type,
  r ->> 'Details' as resource_details,
  r ->> 'Partition' as partition,
  r ->> 'Tags' as resource_tags
from
  aws_inspector2_finding as f,
  jsonb_array_elements(resources) as r;

Get vulnerable package details of findings

select
  f.arn,
  f.vulnerability_id,
  v ->> 'Name' as vulnerability_package_name,
  v ->> 'Version' as vulnerability_package_version,
  v ->> 'Arch' as vulnerability_package_arch,
  v ->> 'Epoch' as vulnerability_package_epoch,
  v ->> 'FilePath' as vulnerability_package_file_path,
  v ->> 'FixedInVersion' as vulnerability_package_fixed_in_version,
  v ->> 'PackageManager' as vulnerability_package_package_manager,
  v ->> 'Release' as vulnerability_package_release,
  v ->> 'Remediation' as vulnerability_package_remediation,
  v ->> 'SourceLambdaLayerArn' as source_lambda_layer_arn,
  v ->> 'Name' as source_layer_hash
from
  aws_inspector2_finding as f,
  jsonb_array_elements(vulnerable_packages) as v;

List exploit available findings

select
  arn,
  finding_account_id,
  first_observed_at,
  fix_available,
  exploit_available
from
  aws_inspector2_finding
where
  exploit_available = 'YES';

List findings that have fixes available through a version update

select
  arn,
  finding_account_id,
  first_observed_at,
  fix_available,
  exploit_available
from
  aws_inspector2_finding
where
  fix_available = 'YES';

List top 5 findings by inspector score

select
  arn,
  inspector_score,
  first_observed_at,
  last_observed_at inspector_score_details
from
  aws_inspector2_finding
order by
  inspector_score desc;

Get inspector score details of findings

select
  arn,
  inspector_score_details -> 'AdjustedCvss' ->> 'Score' as adjusted_cvss_score,
  inspector_score_details -> 'AdjustedCvss' ->> 'ScScoreSourceore' as adjusted_cvss_source_score,
  inspector_score_details -> 'AdjustedCvss' ->> 'ScoScoringVectorre' as adjusted_cvss_scoring_vector,
  inspector_score_details -> 'AdjustedCvss' ->> 'Version' as adjusted_cvss_version,
  inspector_score_details -> 'AdjustedCvss' -> 'Adjustments' as adjusted_cvss_adjustments,
  inspector_score_details -> 'AdjustedCvss' ->> 'CvssSource' as adjusted_cvss_cvss_source
from
  aws_inspector2_finding;

Get network reachability details of findings

select
  arn,
  network_reachability_details -> 'NetworkPath' -> 'Steps' as network_pathsteps,
  network_reachability_details -> 'OpenPortRange' ->> 'Begin' as open_port_range_begin,
  network_reachability_details -> 'OpenPortRange' ->> 'End' as open_port_range_end,
  network_reachability_details -> 'Protocol' as protocol
from
  aws_inspector2_finding;

List findings by resource tags

select
  arn,
  finding_account_id,
  first_observed_at,
  fix_available,
  exploit_available,
  resource_tags
from
  aws_inspector2_finding
where
  resource_tags = '[{"key": "Name", "value": "Dev"}, {"key": "Name", "value": "Prod"}]';

List findings by vulnerable packages

select
  arn,
  finding_account_id,
  first_observed_at,
  fix_available,
  exploit_available,
  vulnerable_package
from
  aws_inspector2_finding
where
  vulnerable_package = '[{"architecture": "arc", "epoch": "231321", "name": "myVulere", "release": "v0.2.0", "sourceLambdaLayerArn": "arn:aws:lambda:us-west-2:123456789012:layer:my-layer:1", "sourceLayerHash": "dbasjkhda872", "version": "v0.1.0"}]';

.inspect aws_inspector2_finding

AWS Inspector2 Finding

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	The AWS Account ID in which the resource is located.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
arn	text	The Amazon Resource Number (ARN) of the finding.
component_id	text	The component ID of the resource.
component_type	text	The component type.
cvss	jsonb	An object that contains details about the CVSS score of a finding.
description	text	The description of the finding.
ec2_instance_image_id	text	The Amazon EC2 instance image ID.
ec2_instance_subnet_id	text	The Amazon EC2 instance subnet ID.
ec2_instance_vpc_id	text	The Amazon EC2 instance VPC ID.
ecr_image_architecture	text	The Amazon ECR image architecture.
ecr_image_hash	text	The Amazon ECR image hash.
ecr_image_pushed_at	timestamp with time zone	The Amazon ECR image push date and time.
ecr_image_registry	text	The Amazon ECR registry.
ecr_image_repository_name	text	The name of the Amazon ECR repository.
ecr_image_tags	text	The tags attached to the Amazon ECR container image.
exploit_available	text	If a finding discovered in your environment has an exploit available. Valid values are: YES \| NO.
exploitability_details	jsonb	The details of an exploit available for a finding discovered in your environment.
finding_account_id	text	The Amazon Web Services account ID associated with the finding.
first_observed_at	timestamp with time zone	The date and time that the finding was first observed.
fix_available	text	Details on whether a fix is available through a version update. Valid values are: YES \| NO \| PARTIAL.
inspector_score	double precision	The Amazon Inspector score given to the finding.
inspector_score_details	jsonb	An object that contains details of the Amazon Inspector score.
lambda_function_execution_role_arn	text	The AWS Lambda function execution role ARN.
lambda_function_last_modified_at	timestamp with time zone	The AWS Lambda functions the date and time that a user last updated the configuration.
lambda_function_layers	text	The AWS Lambda function layer.
lambda_function_name	text	The AWS Lambda function name.
lambda_function_runtime	text	The AWS Lambda function runtime environment.
last_observed_at	timestamp with time zone	The date and time that the finding was last observed.
network_protocol	text	The ingress source addresse.
network_reachability_details	jsonb	An object that contains the details of a network reachability finding.
package_vulnerability_details	jsonb	An object that contains the details of a package vulnerability finding.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
reference_urls	jsonb	One or more URLs that contain details about this vulnerability type.
region	text	The AWS Region in which the resource is located.
related_vulnerabilitie	text	The related vulnerabilitie.
related_vulnerabilities	jsonb	One or more vulnerabilities related to the one identified in this finding.
remediation_recommendation_text	text	The recommended course of action to remediate the finding.
remediation_recommendation_url	text	The URL address to the CVE remediation recommendations.
resource_id	text	The ID of the resource.
resource_tags	jsonb	Details on the resource tags used to filter findings.
resource_type	text	The resource type supported by AWS.
resources	jsonb	Contains information on the resources involved in a finding.
severity	text	The severity of the finding. Valid values are: INFORMATIONAL \| LOW \| MEDIUM \| HIGH \| CRITICAL \| UNTRIAGED.
source	text	The source of the vulnerability information.
source_url	text	A URL to the source of the vulnerability information.
status	text	The status of the finding. Valid values are: ACTIVE \| SUPPRESSED \| CLOSED.
title	text	The title of the finding.
type	text	The type of the finding. Valid values are: NETWORK_REACHABILITY \| PACKAGE_VULNERABILITY.
updated_at	timestamp with time zone	The date and time the finding was last updated at.
vendor_created_at	timestamp with time zone	The date and time that this vulnerability was first added to the vendor’s database.
vendor_severity	text	The severity the vendor has given to this vulnerability type.
vendor_updated_at	timestamp with time zone	The date and time the vendor last updated this vulnerability in their database.
vulnerability_id	text	The ID given to this vulnerability.
vulnerable_package	jsonb	The package impacted by this vulnerability.
vulnerable_packages	jsonb	The packages impacted by this vulnerability.

turbot/aws