steampipe plugin install awssteampipe plugin install aws

Table: aws_securityhub_finding

AWS Security Hub eliminates the complexity of addressing large volumes of findings from multiple providers. It reduces the effort required to manage and improve the security of all of your AWS accounts, resources, and workloads.

Examples

Basic info

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

List findings with high severity

select
  title,
  product_arn,
  product_name,
  severity ->> 'Original' as severity_original
from
  aws_securityhub_finding
where
  severity ->> 'Original' = 'HIGH';

Count the number of findings by severity

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

List findings with failed compliance status

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';

List findings with malware

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

List critical findings from the last 10 days

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;

List findings ordered by criticality

select
  title,
  product_arn,
  product_name,
  criticality
from
  aws_securityhub_finding
order by
  criticality desc nulls last;

List findings for Turbot company

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

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

List findings with 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

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.';

Get patch summary details for a particular finding

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.';

Get vulnerabilities for a finding

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.';

List EC2 instances with failed compliance status

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';

Count resources with failed compliance status

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;

List findings for 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)

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

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%';

List findings for production resources

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';

Count finding resources by environment tag

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;

List all findings for affected account 0123456789012

select
SELECT
  title,
  f.severity ->> 'Original' as severity,
  r ->> 'Type' as resource_type,
  source_account_id
FROM
  aws_securityhub_finding,
  jsonb_array_elements(resources) r
WHERE
  source_account_id = '0123456789012';

Count the number of findings by affected account

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

.inspect aws_securityhub_finding

AWS Security Hub 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.
action	jsonb	Provides details about an action that affects or that was taken on a resource.
arn	text	The Amazon Resource Name (ARN) for the finding.
company_name	text	The name of the company for the product that generated the finding.
compliance	jsonb	This 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_status	text	The result of a compliance standards check.
confidence	bigint	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_at	timestamp with time zone	Indicates when the security-findings provider created the potential security issue that a finding captured.
criticality	bigint	The level of importance assigned to the resources associated with the finding.
description	text	A finding's description.
finding_provider_fields	jsonb	In a BatchImportFindings request, finding providers use FindingProviderFields to provide and update their own values for confidence, criticality, related findings, severity, and types.
first_observed_at	timestamp with time zone	Indicates when the security-findings provider first observed the potential security issue that a finding captured.
generator_id	text	The identifier for the solution-specific component (a discrete unit of logic) that generated a finding.
id	text	The security findings provider-specific identifier for a finding.
last_observed_at	timestamp with time zone	Indicates when the security-findings provider most recently observed the potential security issue that a finding captured.
malware	jsonb	A list of malware related to a finding.
network	jsonb	The details of network-related information about a finding.
network_path	jsonb	Provides information about a network path that is relevant to a finding. Each entry under NetworkPath represents a component of that path.
note	jsonb	A user-defined note added to a finding.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
patch_summary	jsonb	Provides an overview of the patch compliance status for an instance against a selected compliance standard.
process	jsonb	The details of process-related information about a finding.
product_arn	text	The ARN generated by Security Hub that uniquely identifies a product that generates findings.
product_fields	jsonb	A data type where security-findings providers can include additional solution-specific details that aren't part of the defined AwsSecurityFinding format.
product_name	text	The name of the product that generated the finding.
record_state	text	The record state of a finding.
region	text	The AWS Region in which the resource is located.
related_findings	jsonb	A list of related findings.
remediation	jsonb	A data type that describes the remediation options for a finding.
resources	jsonb	A set of resource data types that describe the resources that the finding refers to.
schema_version	text	The schema version that a finding is formatted for.
severity	jsonb	A finding's severity.
source_account_id	text	The account id where the affected resource lives.
source_url	text	A URL that links to a page about the current finding in the security-findings provider's solution.
standards_control_arn	text	The ARN of the security standard control.
threat_intel_indicators	jsonb	Threat intelligence details related to a finding.
title	text	A finding's title.
updated_at	timestamp with time zone	Indicates when the security-findings provider last updated the finding record.
user_defined_fields	jsonb	A list of name/value string pairs associated with the finding.
verification_state	text	Indicates the veracity of a finding.
vulnerabilities	jsonb	Provides a list of vulnerabilities associated with the findings.
workflow_state	text	[DEPRECATED] This column has been deprecated and will be removed in a future release. The workflow state of a finding.
workflow_status	text	The workflow status of a finding. Possible values are NEW, NOTIFIED, SUPPRESSED, RESOLVED.

turbot/aws