steampipe plugin install aws

Table: aws_securityhub_finding - Query AWS Security Hub Findings using SQL

The AWS Security Hub Findings is a feature within AWS Security Hub that aggregates, organizes, and prioritizes your security alerts, or findings, from multiple AWS services. It also consolidates findings from different AWS services such as Amazon GuardDuty, Amazon Inspector, and Amazon Macie, as well as from AWS Partner solutions. The findings are presented as potential security issues, such as insecure configurations or malicious activity.

Table Usage Guide

The aws_securityhub_finding table in Steampipe provides you with information about security findings within AWS Security Hub. This table allows you as a security analyst or DevOps engineer to query details about identified security issues, including their severity, status, description, the resources affected, and any recommended remediation steps. You can utilize this table to gather insights on security vulnerabilities, such as open security groups, exposed access keys, and more. The schema outlines the various attributes of the security finding for you, including the finding ARN, ID, title, description, severity, and associated resources.

Examples

Basic info

Explore the critical security findings in your AWS environment to understand their severity and level of confidence. This can help prioritize remediation efforts and improve overall security posture.

select
title,
id,
company_name,
created_at,
criticality,
confidence
from
aws_securityhub_finding;
select
title,
id,
company_name,
created_at,
criticality,
confidence
from
aws_securityhub_finding;

List findings with high severity

Determine the areas in which high severity security findings occur within your AWS Security Hub to prioritize and address significant threats effectively.

select
title,
product_arn,
product_name,
severity ->> 'Original' as severity_original
from
aws_securityhub_finding
where
severity ->> 'Original' = 'HIGH';
select
title,
product_arn,
product_name,
json_extract(severity, '$.Original') as severity_original
from
aws_securityhub_finding
where
json_extract(severity, '$.Original') = 'HIGH';

Count the number of findings by severity

Determine the distribution of security issues based on their severity level, providing a quick overview of the security status and helping prioritize remediation efforts.

select
severity ->> 'Original' as severity_original,
count(severity ->> 'Original')
from
aws_securityhub_finding
group by
severity ->> 'Original'
order by
severity ->> 'Original';
select
json_extract(severity, '$.Original') as severity_original,
count(json_extract(severity, '$.Original'))
from
aws_securityhub_finding
group by
json_extract(severity, '$.Original')
order by
json_extract(severity, '$.Original');

List findings with failed compliance status

Determine the areas in which security findings have failed compliance checks. This is useful for identifying potential vulnerabilities and areas that require immediate attention in your AWS Security Hub.

select
title,
product_arn,
product_name,
compliance ->> 'Status' as compliance_status,
compliance ->> 'StatusReasons' as compliance_status_reasons
from
aws_securityhub_finding
where
compliance ->> 'Status' = 'FAILED';
select
title,
product_arn,
product_name,
json_extract(compliance, '$.Status') as compliance_status,
json_extract(compliance, '$.StatusReasons') as compliance_status_reasons
from
aws_securityhub_finding
where
json_extract(compliance, '$.Status') = 'FAILED';

List findings with malware

Discover the segments that have detected malware within your AWS SecurityHub findings. This allows for quick identification of potential security threats, helping to safeguard your cloud environment.

select
title,
product_arn,
product_name,
malware
from
aws_securityhub_finding
where
malware is not null;
select
title,
product_arn,
product_name,
malware
from
aws_securityhub_finding
where
malware is not null;

List critical findings from the last 10 days

Explore critical security issues from the past 10 days to understand potential threats. This query is beneficial in identifying and addressing major vulnerabilities swiftly to enhance overall security.

select
title,
product_arn,
product_name,
severity ->> 'Original' as severity_original
from
aws_securityhub_finding
where
severity ->> 'Original' = 'CRITICAL'
and created_at >= now() - interval '10' day;
select
title,
product_arn,
product_name,
json_extract(severity, '$.Original') as severity_original
from
aws_securityhub_finding
where
json_extract(severity, '$.Original') = 'CRITICAL'
and created_at >= datetime('now', '-10 day');

List findings ordered by criticality

Explore the security findings from AWS Security Hub, with a focus on identifying the most critical issues first. This can aid in prioritizing security measures and addressing the most severe vulnerabilities or threats promptly.

select
title,
product_arn,
product_name,
criticality
from
aws_securityhub_finding
order by
criticality desc nulls last;
select
title,
product_arn,
product_name,
criticality
from
aws_securityhub_finding
order by
case
when criticality is null then 1
else 0
end,
criticality desc;

List findings for Turbot company

Explore the security findings related to the company 'Turbot' to gain insights into potential vulnerabilities or breaches. This is crucial for maintaining robust security practices and addressing any potential threats promptly.

select
title,
id,
product_arn,
product_name,
company_name
from
aws_securityhub_finding
where
company_name = 'Turbot';
select
title,
id,
product_arn,
product_name,
company_name
from
aws_securityhub_finding
where
company_name = 'Turbot';

List findings updated in the last 30 days

Explore the recent updates made within the last month in your security findings. This can help identify any potential security threats or issues that have surfaced recently, thereby allowing for timely response and mitigation.

select
title,
product_arn,
product_name,
updated_at
from
aws_securityhub_finding
where
updated_at >= now() - interval '30' day;
select
title,
product_arn,
product_name,
updated_at
from
aws_securityhub_finding
where
updated_at >= datetime('now', '-30 day');

List findings with workflow status NOTIFIED

Explore which security findings in your AWS environment have been flagged with a status of 'Notified'. This allows you to identify and focus on issues that have been brought to your attention, facilitating a more efficient security management process.

select
title,
id,
product_arn,
product_name,
workflow_status
from
aws_securityhub_finding
where
workflow_status = 'NOTIFIED';
select
title,
id,
product_arn,
product_name,
workflow_status
from
aws_securityhub_finding
where
workflow_status = 'NOTIFIED';

Get network detail for a particular finding

Explore the network details associated with a specific security finding to gain insights into potential security threats. This can assist in identifying the source and destination of any potential attacks, helping to bolster security measures.

select
title,
id,
network ->> 'DestinationDomain' as network_destination_domain,
network ->> 'DestinationIpV4' as network_destination_ip_v4,
network ->> 'DestinationIpV6' as network_destination_ip_v6,
network ->> 'DestinationPort' as network_destination_port,
network ->> 'Protocol' as network_protocol,
network ->> 'SourceIpV4' as network_source_ip_v4,
network ->> 'SourceIpV6' as network_source_ip_v6,
network ->> 'SourcePort' as network_source_port
from
aws_securityhub_finding
where
title = 'EC2 instance involved in SSH brute force attacks.';
select
title,
id,
json_extract(network, '$.DestinationDomain') as network_destination_domain,
json_extract(network, '$.DestinationIpV4') as network_destination_ip_v4,
json_extract(network, '$.DestinationIpV6') as network_destination_ip_v6,
json_extract(network, '$.DestinationPort') as network_destination_port,
json_extract(network, '$.Protocol') as network_protocol,
json_extract(network, '$.SourceIpV4') as network_source_ip_v4,
json_extract(network, '$.SourceIpV6') as network_source_ip_v6,
json_extract(network, '$.SourcePort') as network_source_port
from
aws_securityhub_finding
where
title = 'EC2 instance involved in SSH brute force attacks.';

Get patch summary details for a particular finding

Determine the status and details of patch installations for a specific security finding, especially useful when assessing the severity and impact of potential security threats.

select
title,
id,
patch_summary ->> 'Id' as patch_id,
patch_summary ->> 'FailedCount' as failed_count,
patch_summary ->> 'InstalledCount' as installed_count,
patch_summary ->> 'InstalledOtherCount' as installed_other_count,
patch_summary ->> 'InstalledPendingReboot' as installed_pending_reboot,
patch_summary ->> 'InstalledRejectedCount' as installed_rejected_count,
patch_summary ->> 'MissingCount' as missing_count,
patch_summary ->> 'Operation' as operation,
patch_summary ->> 'OperationEndTime' as operation_end_time,
patch_summary ->> 'OperationStartTime' as operation_start_time,
patch_summary ->> 'RebootOption' as reboot_option
from
aws_securityhub_finding
where
title = 'EC2 instance involved in SSH brute force attacks.';
select
title,
id,
json_extract(patch_summary, '$.Id') as patch_id,
json_extract(patch_summary, '$.FailedCount') as failed_count,
json_extract(patch_summary, '$.InstalledCount') as installed_count,
json_extract(patch_summary, '$.InstalledOtherCount') as installed_other_count,
json_extract(patch_summary, '$.InstalledPendingReboot') as installed_pending_reboot,
json_extract(patch_summary, '$.InstalledRejectedCount') as installed_rejected_count,
json_extract(patch_summary, '$.MissingCount') as missing_count,
json_extract(patch_summary, '$.Operation') as operation,
json_extract(patch_summary, '$.OperationEndTime') as operation_end_time,
json_extract(patch_summary, '$.OperationStartTime') as operation_start_time,
json_extract(patch_summary, '$.RebootOption') as reboot_option
from
aws_securityhub_finding
where
title = 'EC2 instance involved in SSH brute force attacks.';

Get vulnerabilities for a finding

Discover the details of potential security vulnerabilities linked to a specific finding. This query is useful in understanding the severity, vendor information, and associated vulnerabilities tied to a particular security incident, such as an SSH brute force attack on an EC2 instance.

select
title,
v ->> 'Id' as vulnerabilitie_id,
v -> 'Vendor' ->> 'Name' as vendor_name,
v -> 'Vendor' ->> 'Url' as vendor_url,
v -> 'Vendor' ->> 'VendorCreatedAt' as vendor_created_at,
v -> 'Vendor' ->> 'VendorSeverity' as vendor_severity,
v -> 'Vendor' ->> 'VendorUpdatedAt' as vendor_updated_at,
v ->> 'Cvss' as cvss,
v ->> 'ReferenceUrls' as reference_urls,
v ->> 'RelatedVulnerabilities' as related_vulnerabilities,
v ->> 'VulnerablePackages' as vulnerable_packages
from
aws_securityhub_finding,
jsonb_array_elements(vulnerabilities) as v
where
title = 'EC2 instance involved in SSH brute force attacks.';
select
title,
json_extract(v.value, '$.Id') as vulnerabilitie_id,
json_extract(v.value, '$.Vendor.Name') as vendor_name,
json_extract(v.value, '$.Vendor.Url') as vendor_url,
json_extract(v.value, '$.Vendor.VendorCreatedAt') as vendor_created_at,
json_extract(v.value, '$.Vendor.VendorSeverity') as vendor_severity,
json_extract(v.value, '$.Vendor.VendorUpdatedAt') as vendor_updated_at,
json_extract(v.value, '$.Cvss') as cvss,
json_extract(v.value, '$.ReferenceUrls') as reference_urls,
json_extract(v.value, '$.RelatedVulnerabilities') as related_vulnerabilities,
json_extract(v.value, '$.VulnerablePackages') as vulnerable_packages
from
aws_securityhub_finding,
json_each(vulnerabilities) as v
where
title = 'EC2 instance involved in SSH brute force attacks.';

List EC2 instances with failed compliance status

Determine the areas in which EC2 instances have failed compliance checks. This is useful for identifying potential security risks and rectifying them to maintain the integrity of your AWS environment.

select
distinct i.instance_id,
i.instance_state,
i.instance_type,
f.title,
f.compliance_status,
f.severity ->> 'Original' as severity_original
from
aws_ec2_instance as i,
aws_securityhub_finding as f,
jsonb_array_elements(resources) as r
where
compliance_status = 'FAILED'
and r ->> 'Type' = 'AwsEc2Instance'
and i.arn = r ->> 'Id';
select
distinct i.instance_id,
i.instance_state,
i.instance_type,
f.title,
f.compliance_status,
json_extract(f.severity, '$.Original') as severity_original
from
aws_ec2_instance as i,
aws_securityhub_finding as f,
json_each(f.resources) as r
where
f.compliance_status = 'FAILED'
and json_extract(r.value, '$.Type') = 'AwsEc2Instance'
and i.arn = json_extract(r.value, '$.Id');

Count resources with failed compliance status

Determine areas in your AWS environment where resources have failed compliance checks. This allows you to identify potential security risks and take corrective action.

select
r ->> 'Type' as resource_type,
count(r ->> 'Type')
from
aws_securityhub_finding,
jsonb_array_elements(resources) as r
group by
r ->> 'Type'
order by
count desc;
select
json_extract(r.value, '$.Type') as resource_type,
count(json_extract(r.value, '$.Type'))
from
aws_securityhub_finding,
json_each(resources) as r
group by
json_extract(r.value, '$.Type')
order by
count(json_extract(r.value, '$.Type')) desc;

List findings for CIS AWS foundations benchmark

Explore which findings are associated with the CIS AWS foundations benchmark in your AWS Security Hub. This can assist in identifying potential security risks or non-compliance issues for your company.

select
title,
id,
company_name,
created_at,
criticality,
confidence
from
aws_securityhub_finding
where
standards_control_arn like '%cis-aws-foundations-benchmark%';
select
title,
id,
company_name,
created_at,
criticality,
confidence
from
aws_securityhub_finding
where
standards_control_arn like '%cis-aws-foundations-benchmark%';

List findings for a particular standard control (Config.1)

Identify instances where specific security findings are associated with a particular standard control. This is beneficial in understanding the security posture of your organization by analyzing the criticality and confidence of the findings.

select
f.title,
f.id,
f.company_name,
f.created_at,
f.criticality,
f.confidence
from
aws_securityhub_finding as f,
aws_securityhub_standards_control as c
where
c.arn = f.standards_control_arn
and c.control_id = 'Config.1';
select
f.title,
f.id,
f.company_name,
f.created_at,
f.criticality,
f.confidence
from
aws_securityhub_finding as f,
aws_securityhub_standards_control as c
where
c.arn = f.standards_control_arn
and c.control_id = 'Config.1';

List resources with a failed compliance status for CIS AWS foundations benchmark

Discover the segments that have failed to comply with the CIS AWS foundations benchmark. This allows you to identify and rectify areas in your AWS resources that are not adhering to the recommended security controls, thereby enhancing your overall security posture.

select
distinct r ->> 'Id' as resource_arn,
r ->> 'Type' as resource_type,
f.title,
f.compliance_status,
f.severity ->> 'Original' as severity_original
from
aws_securityhub_finding as f,
jsonb_array_elements(resources) as r
where
f.compliance_status = 'FAILED'
and standards_control_arn like '%cis-aws-foundations-benchmark%';
select
distinct json_extract(r.value, '$.Id') as resource_arn,
json_extract(r.value, '$.Type') as resource_type,
f.title,
f.compliance_status,
json_extract(f.severity, '$.Original') as severity_original
from
aws_securityhub_finding as f,
json_each(f.resources) as r
where
f.compliance_status = 'FAILED'
and f.standards_control_arn like '%cis-aws-foundations-benchmark%';

List findings for production resources

Uncover the details of potential security issues within your production resources. This query aids in identifying non-compliant resources, assessing the severity of the issues, and understanding the areas that require immediate attention to enhance the security posture.

select
distinct r ->> 'Id' as resource_arn,
r ->> 'Type' as resource_type,
f.title,
f.compliance_status,
f.severity ->> 'Original' as severity_original
from
aws_securityhub_finding as f,
jsonb_array_elements(resources) as r
where
r -> 'Tags' ->> 'Environment' = 'PROD';
select
distinct json_extract(r.value, '$.Id') as resource_arn,
json_extract(r.value, '$.Type') as resource_type,
f.title,
f.compliance_status,
json_extract(f.severity, '$.Original') as severity_original
from
aws_securityhub_finding as f,
json_each(f.resources) as r
where
json_extract(json_extract(r.value, '$.Tags'), '$.Environment') = 'PROD';

Count finding resources by environment tag

This query helps identify the number of security findings associated with different environments in your AWS infrastructure. It's useful for understanding the distribution of potential security issues across various operational contexts.

select
r -> 'Tags' ->> 'Environment' as environment,
count(r ->> 'Tags')
from
aws_securityhub_finding as f,
jsonb_array_elements(resources) as r
group by
r -> 'Tags' ->> 'Environment'
order by
count desc;
select
json_extract(r.value, '$.Tags.Environment') as environment,
count(json_extract(r.value, '$.Tags'))
from
aws_securityhub_finding as f,
json_each(f.resources) as r
group by
json_extract(r.value, '$.Tags.Environment')
order by
count(*) desc;

List all findings for affected account 0123456789012

Determine the areas in which security issues have been identified for a specific account in AWS Security Hub. This is beneficial for pinpointing and addressing vulnerabilities in a targeted manner.

select
title,
f.severity ->> 'Original' as severity,
r ->> 'Type' as resource_type,
source_account_id
from
aws_securityhub_finding as f,
jsonb_array_elements(resources) r
where
source_account_id = '0123456789012';
select
title,
json_extract(f.severity, '$.Original') as severity,
json_extract(r.value, '$.Type') as resource_type,
source_account_id
from
aws_securityhub_finding as f,
json_each(resources) as r
where
source_account_id = '0123456789012';

Count the number of findings by affected account

Discover the segments that have security findings in your AWS accounts. This query helps you identify which accounts have the most findings, allowing you to prioritize your security efforts.

select
source_account_id,
count(*) as finding_count
from
aws_securityhub_finding
group by
source_account_id
order by
source_account_id;
select
source_account_id,
count(*) as finding_count
from
aws_securityhub_finding
group by
source_account_id
order by
source_account_id;

Schema for aws_securityhub_finding

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
actionjsonbProvides details about an action that affects or that was taken on a resource.
arntextThe Amazon Resource Name (ARN) for the finding.
company_nametext=, !=The name of the company for the product that generated the finding.
compliancejsonbThis data type is exclusive to findings that are generated as the result of a check run against a specific rule in a supported security standard, such as CIS Amazon Web Services Foundations.
compliance_statustext=, !=The result of a compliance standards check.
confidencebigint=, >=, <=A finding's confidence. Confidence is defined as the likelihood that a finding accurately identifies the behavior or issue that it was intended to identify.
created_attimestamp with time zoneIndicates when the security-findings provider created the potential security issue that a finding captured.
criticalitybigint=, >=, <=The level of importance assigned to the resources associated with the finding.
descriptiontextA finding's description.
finding_provider_fieldsjsonbIn a BatchImportFindings request, finding providers use FindingProviderFields to provide and update their own values for confidence, criticality, related findings, severity, and types.
first_observed_attimestamp with time zoneIndicates when the security-findings provider first observed the potential security issue that a finding captured.
generator_detailsjsonbProvides metadata for the Amazon CodeGuru detector associated with a finding.
generator_idtext=, !=The identifier for the solution-specific component (a discrete unit of logic) that generated a finding.
idtext=The security findings provider-specific identifier for a finding.
last_observed_attimestamp with time zoneIndicates when the security-findings provider most recently observed the potential security issue that a finding captured.
malwarejsonbA list of malware related to a finding.
networkjsonbThe details of network-related information about a finding.
network_pathjsonbProvides information about a network path that is relevant to a finding. Each entry under NetworkPath represents a component of that path.
notejsonbA user-defined note added to a finding.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
patch_summaryjsonbProvides an overview of the patch compliance status for an instance against a selected compliance standard.
processjsonbThe details of process-related information about a finding.
processed_attimestamp with time zoneAn ISO8601-formatted timestamp that indicates when Security Hub received a finding and begins to process it.
product_arntext=, !=The ARN generated by Security Hub that uniquely identifies a product that generates findings.
product_fieldsjsonbA data type where security-findings providers can include additional solution-specific details that aren't part of the defined AwsSecurityFinding format.
product_nametext=, !=The name of the product that generated the finding.
record_statetext=, !=The record state of a finding.
regiontextThe AWS Region in which the resource is located.
related_findingsjsonbA list of related findings.
remediationjsonbA data type that describes the remediation options for a finding.
resourcesjsonbA set of resource data types that describe the resources that the finding refers to.
samplebooleanIndicates whether the finding is a sample finding.
schema_versiontextThe schema version that a finding is formatted for.
severityjsonbA finding's severity.
source_account_idtext=, !=The account id where the affected resource lives.
source_urltextA URL that links to a page about the current finding in the security-findings provider's solution.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
standards_control_arntextThe ARN of the security standard control.
threat_intel_indicatorsjsonbThreat intelligence details related to a finding.
threatsjsonbDetails about the threat detected in a security finding and the file paths that were affected by the threat.
titletext=, !=A finding's title.
typesjsonbOne or more finding types in the format of namespace/category/classifier that classify a finding. Valid namespace values are: Software and Configuration Checks | TTPs | Effects | Unusual Behaviors | Sensitive Data Identifications.
updated_attimestamp with time zoneIndicates when the security-findings provider last updated the finding record.
user_defined_fieldsjsonbA list of name/value string pairs associated with the finding.
verification_statetext=, !=Indicates the veracity of a finding.
vulnerabilitiesjsonbProvides a list of vulnerabilities associated with the findings.
workflow_statetext=, !=[DEPRECATED] This column has been deprecated and will be removed in a future release. The workflow state of a finding.
workflow_statustext=, !=The workflow status of a finding. Possible values are NEW, NOTIFIED, SUPPRESSED, RESOLVED.

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_securityhub_finding