turbot/aws

GitHub
steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_account_alternate_contactaws_account_contactaws_acm_certificateaws_amplify_appaws_api_gateway_api_authorizeraws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_stageaws_appautoscaling_targetaws_appconfig_applicationaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_frameworkaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_functionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudfront_response_headers_policyaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_cloudwatch_log_subscription_filteraws_cloudwatch_metricaws_codeartifact_domainaws_codeartifact_repositoryaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codedeploy_appaws_codepipeline_pipelineaws_config_aggregate_authorizationaws_config_configuration_recorderaws_config_conformance_packaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_record_type_dailyaws_cost_by_record_type_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dax_subnet_groupaws_directory_service_directoryaws_dlm_lifecycle_policyaws_dms_replication_instanceaws_docdb_clusteraws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_dynamodb_table_exportaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_application_load_balancer_metric_request_countaws_ec2_application_load_balancer_metric_request_count_dailyaws_ec2_autoscaling_groupaws_ec2_capacity_reservationaws_ec2_classic_load_balanceraws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_load_balancer_listeneraws_ec2_managed_prefix_listaws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_network_load_balancer_metric_net_flow_countaws_ec2_network_load_balancer_metric_net_flow_count_dailyaws_ec2_regional_settingsaws_ec2_reserved_instanceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_imageaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_cluster_metric_cpu_utilizationaws_ecs_cluster_metric_cpu_utilization_dailyaws_ecs_cluster_metric_cpu_utilization_hourlyaws_ecs_container_instanceaws_ecs_serviceaws_ecs_taskaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_eks_identity_provider_configaws_eks_node_groupaws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_redis_metric_cache_hits_hourlyaws_elasticache_redis_metric_curr_connections_hourlyaws_elasticache_redis_metric_engine_cpu_utilization_dailyaws_elasticache_redis_metric_engine_cpu_utilization_hourlyaws_elasticache_redis_metric_get_type_cmds_hourlyaws_elasticache_redis_metric_list_based_cmds_hourlyaws_elasticache_redis_metric_new_connections_hourlyaws_elasticache_replication_groupaws_elasticache_reserved_cache_nodeaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instanceaws_emr_instance_fleetaws_emr_instance_groupaws_eventbridge_busaws_eventbridge_ruleaws_fsx_file_systemaws_glacier_vaultaws_globalaccelerator_acceleratoraws_globalaccelerator_endpoint_groupaws_globalaccelerator_listeneraws_glue_catalog_databaseaws_glue_catalog_tableaws_glue_connectionaws_glue_crawleraws_glue_data_catalog_encryption_settingsaws_glue_dev_endpointaws_glue_jobaws_glue_security_configurationaws_guardduty_detectoraws_guardduty_filteraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_memberaws_guardduty_publishing_destinationaws_guardduty_threat_intel_setaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_attachmentaws_iam_policy_simulatoraws_iam_roleaws_iam_saml_provideraws_iam_server_certificateaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_useraws_inspector_assessment_runaws_inspector_assessment_targetaws_inspector_assessment_templateaws_inspector_exclusionaws_inspector_findingaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_keyaws_lambda_aliasaws_lambda_functionaws_lambda_function_metric_duration_dailyaws_lambda_function_metric_errors_dailyaws_lambda_function_metric_invocations_dailyaws_lambda_layeraws_lambda_layer_versionaws_lambda_versionaws_macie2_classification_jobaws_media_store_containeraws_msk_clusteraws_msk_serverless_clusteraws_neptune_db_clusteraws_networkfirewall_firewall_policyaws_networkfirewall_rule_groupaws_opensearch_domainaws_organizations_accountaws_pinpoint_appaws_pricing_service_attributeaws_ram_principal_associationaws_ram_resource_associationaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_rds_db_instanceaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_snapshotaws_rds_db_subnet_groupaws_rds_reserved_db_instanceaws_redshift_clusteraws_redshift_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_redshiftserverless_namespaceaws_redshiftserverless_workgroupaws_regionaws_route53_domainaws_route53_health_checkaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_traffic_policyaws_route53_traffic_policy_instanceaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_sagemaker_appaws_sagemaker_domainaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_action_targetaws_securityhub_findingaws_securityhub_finding_aggregatoraws_securityhub_hubaws_securityhub_insightaws_securityhub_memberaws_securityhub_productaws_securityhub_standards_controlaws_securityhub_standards_subscriptionaws_serverlessapplicationrepository_applicationaws_servicequotas_default_service_quotaaws_servicequotas_service_quotaaws_servicequotas_service_quota_change_requestaws_ses_domain_identityaws_ses_email_identityaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_inventoryaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_parameteraws_ssm_patch_baselineaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_peering_connectionaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_waf_rule_groupaws_waf_web_aclaws_wafregional_ruleaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_workloadaws_workspaces_workspace

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 assigned workflow state

select
title,
id,
product_arn,
product_name,
workflow_state
from
aws_securityhub_finding
where
workflow_state = 'ASSIGNED';

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

.inspect aws_securityhub_finding

AWS Security Hub Finding

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe 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_nametextThe 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_statustextThe result of a compliance standards check.
confidencebigintA 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.
criticalitybigintThe 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_idtextThe identifier for the solution-specific component (a discrete unit of logic) that generated a finding.
idtextThe 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.
product_arntextThe 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_nametextThe name of the product that generated the finding.
record_statetextThe 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.
schema_versiontextThe schema version that a finding is formatted for.
severityjsonbA finding's severity.
source_urltextA URL that links to a page about the current finding in the security-findings provider's solution.
standards_control_arntextThe ARN of the security standard control.
threat_intel_indicatorsjsonbThreat intelligence details related to a finding.
titletextA finding's title.
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_statetextIndicates the veracity of a finding.
vulnerabilitiesjsonbProvides a list of vulnerabilities associated with the findings.
workflow_statetextThe workflow state of a finding.