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_domain_nameaws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_routeaws_api_gatewayv2_stageaws_appautoscaling_policyaws_appautoscaling_targetaws_appconfig_applicationaws_appstream_fleetaws_appstream_imageaws_athena_query_executionaws_athena_workgroupaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_frameworkaws_backup_legal_holdaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_report_planaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudformation_stack_resourceaws_cloudformation_stack_setaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_functionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudfront_response_headers_policyaws_cloudsearch_domainaws_cloudtrail_channelaws_cloudtrail_event_data_storeaws_cloudtrail_importaws_cloudtrail_queryaws_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_cloudwatch_metric_data_pointaws_cloudwatch_metric_statistic_data_pointaws_codeartifact_domainaws_codeartifact_repositoryaws_codebuild_buildaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codedeploy_appaws_codedeploy_deployment_configaws_codedeploy_deployment_groupaws_codepipeline_pipelineaws_cognito_identity_poolaws_cognito_identity_provideraws_cognito_user_poolaws_config_aggregate_authorizationaws_config_configuration_recorderaws_config_conformance_packaws_config_retention_configurationaws_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_by_tagaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dax_parameteraws_dax_parameter_groupaws_dax_subnet_groupaws_directory_service_certificateaws_directory_service_directoryaws_directory_service_log_subscriptionaws_directory_servicelog_subscriptionaws_dlm_lifecycle_policyaws_dms_replication_instanceaws_docdb_clusteraws_docdb_cluster_instanceaws_drs_jobaws_drs_recovery_instanceaws_drs_recovery_snapshotaws_drs_source_serveraws_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_client_vpn_endpointaws_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_launch_templateaws_ec2_launch_template_versionaws_ec2_load_balancer_listeneraws_ec2_managed_prefix_listaws_ec2_managed_prefix_list_entryaws_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_spot_priceaws_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_image_scan_findingaws_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_fargate_profileaws_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_block_public_access_configurationaws_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_data_quality_rulesetaws_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_health_affected_entityaws_health_eventaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_open_id_connect_provideraws_iam_policyaws_iam_policy_attachmentaws_iam_policy_simulatoraws_iam_roleaws_iam_saml_provideraws_iam_server_certificateaws_iam_service_specific_credentialaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_group_membershipaws_identitystore_useraws_inspector2_coverageaws_inspector2_coverage_statisticsaws_inspector2_findingaws_inspector2_memberaws_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_aliasaws_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_lightsail_instanceaws_macie2_classification_jobaws_media_store_containeraws_mgn_applicationaws_msk_clusteraws_msk_serverless_clusteraws_neptune_db_clusteraws_neptune_db_cluster_snapshotaws_networkfirewall_firewallaws_networkfirewall_firewall_policyaws_networkfirewall_rule_groupaws_oam_linkaws_oam_sinkaws_opensearch_domainaws_organizations_accountaws_organizations_policyaws_organizations_policy_targetaws_pinpoint_appaws_pipes_pipeaws_pricing_productaws_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_automated_backupaws_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_proxyaws_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_resource_explorer_indexaws_resource_explorer_searchaws_resource_explorer_supported_resource_typeaws_route53_domainaws_route53_health_checkaws_route53_query_logaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_query_log_configaws_route53_resolver_ruleaws_route53_traffic_policyaws_route53_traffic_policy_instanceaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_s3_bucket_intelligent_tiering_configurationaws_s3_multi_region_access_pointaws_s3_objectaws_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_securitylake_data_lakeaws_securitylake_subscriberaws_serverlessapplicationrepository_applicationaws_service_discovery_instanceaws_service_discovery_namespaceaws_service_discovery_serviceaws_servicecatalog_portfolioaws_servicecatalog_productaws_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_simspaceweaver_simulationaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_document_permissionaws_ssm_inventoryaws_ssm_inventory_entryaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_managed_instance_patch_stateaws_ssm_parameteraws_ssm_patch_baselineaws_ssoadmin_account_assignmentaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_sts_caller_identityaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_eip_address_transferaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_nat_gateway_metric_bytes_out_to_destinationaws_vpc_network_aclaws_vpc_peering_connectionaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_verified_access_endpointaws_vpc_verified_access_groupaws_vpc_verified_access_instanceaws_vpc_verified_access_trust_provideraws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_waf_rule_groupaws_waf_web_aclaws_wafregional_ruleaws_wafregional_rule_groupaws_wafregional_web_aclaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_answeraws_wellarchitected_check_detailaws_wellarchitected_check_summaryaws_wellarchitected_consolidated_reportaws_wellarchitected_lensaws_wellarchitected_lens_reviewaws_wellarchitected_lens_review_improvementaws_wellarchitected_lens_review_reportaws_wellarchitected_lens_shareaws_wellarchitected_milestoneaws_wellarchitected_notificationaws_wellarchitected_share_invitationaws_wellarchitected_workloadaws_wellarchitected_workload_shareaws_workspaces_directoryaws_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, confidencefrom aws_securityhub_finding;
List findings with high severity
select title, product_arn, product_name, severity ->> 'Original' as severity_originalfrom aws_securityhub_findingwhere severity ->> 'Original' = 'HIGH';
Count the number of findings by severity
select severity ->> 'Original' as severity_original, count(severity ->> 'Original')from aws_securityhub_findinggroup 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_reasonsfrom aws_securityhub_findingwhere compliance ->> 'Status' = 'FAILED';
List findings with malware
select title, product_arn, product_name, malwarefrom aws_securityhub_findingwhere malware is not null;
List critical findings from the last 10 days
select title, product_arn, product_name, severity ->> 'Original' as severity_originalfrom aws_securityhub_findingwhere severity ->> 'Original' = 'CRITICAL' and created_at >= now() - interval '10' day;
List findings ordered by criticality
select title, product_arn, product_name, criticalityfrom aws_securityhub_findingorder by criticality desc nulls last;
List findings for Turbot company
select title, id, product_arn, product_name, company_namefrom aws_securityhub_findingwhere company_name = 'Turbot';
List findings updated in the last 30 days
select title, product_arn, product_name, updated_atfrom aws_securityhub_findingwhere updated_at >= now() - interval '30' day;
List findings with workflow status NOTIFIED
select title, id, product_arn, product_name, workflow_statusfrom aws_securityhub_findingwhere 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_portfrom aws_securityhub_findingwhere 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_optionfrom aws_securityhub_findingwhere 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_packagesfrom aws_securityhub_finding, jsonb_array_elements(vulnerabilities) as vwhere 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_originalfrom aws_ec2_instance as i, aws_securityhub_finding as f, jsonb_array_elements(resources) as rwhere 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 rgroup by r ->> 'Type'order by count desc;
List findings for CIS AWS foundations benchmark
select title, id, company_name, created_at, criticality, confidencefrom aws_securityhub_findingwhere 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.confidencefrom aws_securityhub_finding as f, aws_securityhub_standards_control as cwhere 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_originalfrom aws_securityhub_finding as f, jsonb_array_elements(resources) as rwhere 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_originalfrom aws_securityhub_finding as f, jsonb_array_elements(resources) as rwhere 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 rgroup by r -> 'Tags' ->> 'Environment'order by count desc;
List all findings for affected account 0123456789012
selectSELECT title, f.severity ->> 'Original' as severity, r ->> 'Type' as resource_type, source_account_idFROM aws_securityhub_finding, jsonb_array_elements(resources) rWHERE source_account_id = '0123456789012';
Count the number of findings by affected account
select source_account_id, count(*) as finding_countfrom aws_securityhub_findinggroup by source_account_idorder 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. |