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_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_fms_app_listaws_fms_policyaws_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_event_source_mappingaws_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_transfer_serveraws_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_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

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Amazon Resource Number (ARN) of the finding.
component_idtextThe component ID of the resource.
component_typetextThe component type.
cvssjsonbAn object that contains details about the CVSS score of a finding.
descriptiontextThe description of the finding.
ec2_instance_image_idtextThe Amazon EC2 instance image ID.
ec2_instance_subnet_idtextThe Amazon EC2 instance subnet ID.
ec2_instance_vpc_idtextThe Amazon EC2 instance VPC ID.
ecr_image_architecturetextThe Amazon ECR image architecture.
ecr_image_hashtextThe Amazon ECR image hash.
ecr_image_pushed_attimestamp with time zoneThe Amazon ECR image push date and time.
ecr_image_registrytextThe Amazon ECR registry.
ecr_image_repository_nametextThe name of the Amazon ECR repository.
ecr_image_tagstextThe tags attached to the Amazon ECR container image.
exploit_availabletextIf a finding discovered in your environment has an exploit available. Valid values are: YES | NO.
exploitability_detailsjsonbThe details of an exploit available for a finding discovered in your environment.
finding_account_idtextThe Amazon Web Services account ID associated with the finding.
first_observed_attimestamp with time zoneThe date and time that the finding was first observed.
fix_availabletextDetails on whether a fix is available through a version update. Valid values are: YES | NO | PARTIAL.
inspector_scoredouble precisionThe Amazon Inspector score given to the finding.
inspector_score_detailsjsonbAn object that contains details of the Amazon Inspector score.
lambda_function_execution_role_arntextThe AWS Lambda function execution role ARN.
lambda_function_last_modified_attimestamp with time zoneThe AWS Lambda functions the date and time that a user last updated the configuration.
lambda_function_layerstextThe AWS Lambda function layer.
lambda_function_nametextThe AWS Lambda function name.
lambda_function_runtimetextThe AWS Lambda function runtime environment.
last_observed_attimestamp with time zoneThe date and time that the finding was last observed.
network_protocoltextThe ingress source addresse.
network_reachability_detailsjsonbAn object that contains the details of a network reachability finding.
package_vulnerability_detailsjsonbAn object that contains the details of a package vulnerability finding.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
reference_urlsjsonbOne or more URLs that contain details about this vulnerability type.
regiontextThe AWS Region in which the resource is located.
related_vulnerabilitietextThe related vulnerabilitie.
related_vulnerabilitiesjsonbOne or more vulnerabilities related to the one identified in this finding.
remediation_recommendation_texttextThe recommended course of action to remediate the finding.
remediation_recommendation_urltextThe URL address to the CVE remediation recommendations.
resource_idtextThe ID of the resource.
resource_tagsjsonbDetails on the resource tags used to filter findings.
resource_typetextThe resource type supported by AWS.
resourcesjsonbContains information on the resources involved in a finding.
severitytextThe severity of the finding. Valid values are: INFORMATIONAL | LOW | MEDIUM | HIGH | CRITICAL | UNTRIAGED.
sourcetextThe source of the vulnerability information.
source_urltextA URL to the source of the vulnerability information.
statustextThe status of the finding. Valid values are: ACTIVE | SUPPRESSED | CLOSED.
titletextThe title of the finding.
typetextThe type of the finding. Valid values are: NETWORK_REACHABILITY | PACKAGE_VULNERABILITY.
updated_attimestamp with time zoneThe date and time the finding was last updated at.
vendor_created_attimestamp with time zoneThe date and time that this vulnerability was first added to the vendor’s database.
vendor_severitytextThe severity the vendor has given to this vulnerability type.
vendor_updated_attimestamp with time zoneThe date and time the vendor last updated this vulnerability in their database.
vulnerability_idtextThe ID given to this vulnerability.
vulnerable_packagejsonbThe package impacted by this vulnerability.
vulnerable_packagesjsonbThe packages impacted by this vulnerability.