steampipe plugin install awssteampipe plugin install aws

aws_accessanalyzer_analyzer aws_account aws_account_alternate_contact aws_account_contact aws_acm_certificate aws_amplify_app aws_api_gateway_api_authorizer aws_api_gateway_api_key aws_api_gateway_authorizer aws_api_gateway_domain_name aws_api_gateway_rest_api aws_api_gateway_stage aws_api_gateway_usage_plan aws_api_gatewayv2_api aws_api_gatewayv2_domain_name aws_api_gatewayv2_integration aws_api_gatewayv2_route aws_api_gatewayv2_stage aws_appautoscaling_policy aws_appautoscaling_target aws_appconfig_application aws_appstream_fleet aws_appstream_image aws_athena_query_execution aws_athena_workgroup aws_auditmanager_assessment aws_auditmanager_control aws_auditmanager_evidence aws_auditmanager_evidence_folder aws_auditmanager_framework aws_availability_zone aws_backup_framework aws_backup_legal_hold aws_backup_plan aws_backup_protected_resource aws_backup_recovery_point aws_backup_report_plan aws_backup_selection aws_backup_vault aws_cloudcontrol_resource aws_cloudformation_stack aws_cloudformation_stack_resource aws_cloudformation_stack_set aws_cloudfront_cache_policy aws_cloudfront_distribution aws_cloudfront_function aws_cloudfront_origin_access_identity aws_cloudfront_origin_request_policy aws_cloudfront_response_headers_policy aws_cloudsearch_domain aws_cloudtrail_channel aws_cloudtrail_event_data_store aws_cloudtrail_import aws_cloudtrail_query aws_cloudtrail_trail aws_cloudtrail_trail_event aws_cloudwatch_alarm aws_cloudwatch_log_event aws_cloudwatch_log_group aws_cloudwatch_log_metric_filter aws_cloudwatch_log_resource_policy aws_cloudwatch_log_stream aws_cloudwatch_log_subscription_filter aws_cloudwatch_metric aws_cloudwatch_metric_data_point aws_cloudwatch_metric_statistic_data_point aws_codeartifact_domain aws_codeartifact_repository aws_codebuild_build aws_codebuild_project aws_codebuild_source_credential aws_codecommit_repository aws_codedeploy_app aws_codedeploy_deployment_config aws_codedeploy_deployment_group aws_codepipeline_pipeline aws_cognito_identity_pool aws_cognito_identity_provider aws_cognito_user_pool aws_config_aggregate_authorization aws_config_configuration_recorder aws_config_conformance_pack aws_config_retention_configuration aws_config_rule aws_cost_by_account_daily aws_cost_by_account_monthly aws_cost_by_record_type_daily aws_cost_by_record_type_monthly aws_cost_by_service_daily aws_cost_by_service_monthly aws_cost_by_service_usage_type_daily aws_cost_by_service_usage_type_monthly aws_cost_by_tag aws_cost_forecast_daily aws_cost_forecast_monthly aws_cost_usage aws_dax_cluster aws_dax_parameter aws_dax_parameter_group aws_dax_subnet_group aws_directory_service_certificate aws_directory_service_directory aws_directory_service_log_subscription aws_directory_servicelog_subscription aws_dlm_lifecycle_policy aws_dms_replication_instance aws_docdb_cluster aws_docdb_cluster_instance aws_drs_job aws_drs_recovery_instance aws_drs_recovery_snapshot aws_drs_source_server aws_dynamodb_backup aws_dynamodb_global_table aws_dynamodb_metric_account_provisioned_read_capacity_util aws_dynamodb_metric_account_provisioned_write_capacity_util aws_dynamodb_table aws_dynamodb_table_export aws_ebs_snapshot aws_ebs_volume aws_ebs_volume_metric_read_ops aws_ebs_volume_metric_read_ops_daily aws_ebs_volume_metric_read_ops_hourly aws_ebs_volume_metric_write_ops aws_ebs_volume_metric_write_ops_daily aws_ebs_volume_metric_write_ops_hourly aws_ec2_ami aws_ec2_ami_shared aws_ec2_application_load_balancer aws_ec2_application_load_balancer_metric_request_count aws_ec2_application_load_balancer_metric_request_count_daily aws_ec2_autoscaling_group aws_ec2_capacity_reservation aws_ec2_classic_load_balancer aws_ec2_client_vpn_endpoint aws_ec2_gateway_load_balancer aws_ec2_instance aws_ec2_instance_availability aws_ec2_instance_metric_cpu_utilization aws_ec2_instance_metric_cpu_utilization_daily aws_ec2_instance_metric_cpu_utilization_hourly aws_ec2_instance_type aws_ec2_key_pair aws_ec2_launch_configuration aws_ec2_launch_template aws_ec2_launch_template_version aws_ec2_load_balancer_listener aws_ec2_managed_prefix_list aws_ec2_managed_prefix_list_entry aws_ec2_network_interface aws_ec2_network_load_balancer aws_ec2_network_load_balancer_metric_net_flow_count aws_ec2_network_load_balancer_metric_net_flow_count_daily aws_ec2_regional_settings aws_ec2_reserved_instance aws_ec2_spot_price aws_ec2_ssl_policy aws_ec2_target_group aws_ec2_transit_gateway aws_ec2_transit_gateway_route aws_ec2_transit_gateway_route_table aws_ec2_transit_gateway_vpc_attachment aws_ecr_image aws_ecr_image_scan_finding aws_ecr_repository aws_ecrpublic_repository aws_ecs_cluster aws_ecs_cluster_metric_cpu_utilization aws_ecs_cluster_metric_cpu_utilization_daily aws_ecs_cluster_metric_cpu_utilization_hourly aws_ecs_container_instance aws_ecs_service aws_ecs_task aws_ecs_task_definition aws_efs_access_point aws_efs_file_system aws_efs_mount_target aws_eks_addon aws_eks_addon_version aws_eks_cluster aws_eks_fargate_profile aws_eks_identity_provider_config aws_eks_node_group aws_elastic_beanstalk_application aws_elastic_beanstalk_environment aws_elasticache_cluster aws_elasticache_parameter_group aws_elasticache_redis_metric_cache_hits_hourly aws_elasticache_redis_metric_curr_connections_hourly aws_elasticache_redis_metric_engine_cpu_utilization_daily aws_elasticache_redis_metric_engine_cpu_utilization_hourly aws_elasticache_redis_metric_get_type_cmds_hourly aws_elasticache_redis_metric_list_based_cmds_hourly aws_elasticache_redis_metric_new_connections_hourly aws_elasticache_replication_group aws_elasticache_reserved_cache_node aws_elasticache_subnet_group aws_elasticsearch_domain aws_emr_block_public_access_configuration aws_emr_cluster aws_emr_cluster_metric_is_idle aws_emr_instance aws_emr_instance_fleet aws_emr_instance_group aws_eventbridge_bus aws_eventbridge_rule aws_fsx_file_system aws_glacier_vault aws_globalaccelerator_accelerator aws_globalaccelerator_endpoint_group aws_globalaccelerator_listener aws_glue_catalog_database aws_glue_catalog_table aws_glue_connection aws_glue_crawler aws_glue_data_catalog_encryption_settings aws_glue_data_quality_ruleset aws_glue_dev_endpoint aws_glue_job aws_glue_security_configuration aws_guardduty_detector aws_guardduty_filter aws_guardduty_finding aws_guardduty_ipset aws_guardduty_member aws_guardduty_publishing_destination aws_guardduty_threat_intel_set aws_health_affected_entity aws_health_event aws_iam_access_advisor aws_iam_access_key aws_iam_account_password_policy aws_iam_account_summary aws_iam_action aws_iam_credential_report aws_iam_group aws_iam_open_id_connect_provider aws_iam_policy aws_iam_policy_attachment aws_iam_policy_simulator aws_iam_role aws_iam_saml_provider aws_iam_server_certificate aws_iam_service_specific_credential aws_iam_user aws_iam_virtual_mfa_device aws_identitystore_group aws_identitystore_group_membership aws_identitystore_user aws_inspector2_coverage aws_inspector2_coverage_statistics aws_inspector2_finding aws_inspector2_member aws_inspector_assessment_run aws_inspector_assessment_target aws_inspector_assessment_template aws_inspector_exclusion aws_inspector_finding aws_kinesis_consumer aws_kinesis_firehose_delivery_stream aws_kinesis_stream aws_kinesis_video_stream aws_kinesisanalyticsv2_application aws_kms_alias aws_kms_key aws_lambda_alias aws_lambda_function aws_lambda_function_metric_duration_daily aws_lambda_function_metric_errors_daily aws_lambda_function_metric_invocations_daily aws_lambda_layer aws_lambda_layer_version aws_lambda_version aws_lightsail_instance aws_macie2_classification_job aws_media_store_container aws_mgn_application aws_msk_cluster aws_msk_serverless_cluster aws_neptune_db_cluster aws_neptune_db_cluster_snapshot aws_networkfirewall_firewall aws_networkfirewall_firewall_policy aws_networkfirewall_rule_group aws_oam_link aws_oam_sink aws_opensearch_domain aws_organizations_account aws_organizations_policy aws_organizations_policy_target aws_pinpoint_app aws_pipes_pipe aws_pricing_product aws_pricing_service_attribute aws_ram_principal_association aws_ram_resource_association aws_rds_db_cluster aws_rds_db_cluster_parameter_group aws_rds_db_cluster_snapshot aws_rds_db_event_subscription aws_rds_db_instance aws_rds_db_instance_automated_backup aws_rds_db_instance_metric_connections aws_rds_db_instance_metric_connections_daily aws_rds_db_instance_metric_connections_hourly aws_rds_db_instance_metric_cpu_utilization aws_rds_db_instance_metric_cpu_utilization_daily aws_rds_db_instance_metric_cpu_utilization_hourly aws_rds_db_instance_metric_read_iops aws_rds_db_instance_metric_read_iops_daily aws_rds_db_instance_metric_read_iops_hourly aws_rds_db_instance_metric_write_iops aws_rds_db_instance_metric_write_iops_daily aws_rds_db_instance_metric_write_iops_hourly aws_rds_db_option_group aws_rds_db_parameter_group aws_rds_db_proxy aws_rds_db_snapshot aws_rds_db_subnet_group aws_rds_reserved_db_instance aws_redshift_cluster aws_redshift_cluster_metric_cpu_utilization_daily aws_redshift_event_subscription aws_redshift_parameter_group aws_redshift_snapshot aws_redshift_subnet_group aws_redshiftserverless_namespace aws_redshiftserverless_workgroup aws_region aws_resource_explorer_index aws_resource_explorer_search aws_resource_explorer_supported_resource_type aws_route53_domain aws_route53_health_check aws_route53_query_log aws_route53_record aws_route53_resolver_endpoint aws_route53_resolver_query_log_config aws_route53_resolver_rule aws_route53_traffic_policy aws_route53_traffic_policy_instance aws_route53_zone aws_s3_access_point aws_s3_account_settings aws_s3_bucket aws_s3_bucket_intelligent_tiering_configuration aws_s3_multi_region_access_point aws_s3_object aws_sagemaker_app aws_sagemaker_domain aws_sagemaker_endpoint_configuration aws_sagemaker_model aws_sagemaker_notebook_instance aws_sagemaker_training_job aws_secretsmanager_secret aws_securityhub_action_target aws_securityhub_finding aws_securityhub_finding_aggregator aws_securityhub_hub aws_securityhub_insight aws_securityhub_member aws_securityhub_product aws_securityhub_standards_control aws_securityhub_standards_subscription aws_securitylake_data_lake aws_securitylake_subscriber aws_serverlessapplicationrepository_application aws_service_discovery_instance aws_service_discovery_namespace aws_service_discovery_service aws_servicecatalog_portfolio aws_servicecatalog_product aws_servicequotas_default_service_quota aws_servicequotas_service_quota aws_servicequotas_service_quota_change_request aws_ses_domain_identity aws_ses_email_identity aws_sfn_state_machine aws_sfn_state_machine_execution aws_sfn_state_machine_execution_history aws_simspaceweaver_simulation aws_sns_topic aws_sns_topic_subscription aws_sqs_queue aws_ssm_association aws_ssm_document aws_ssm_document_permission aws_ssm_inventory aws_ssm_inventory_entry aws_ssm_maintenance_window aws_ssm_managed_instance aws_ssm_managed_instance_compliance aws_ssm_managed_instance_patch_state aws_ssm_parameter aws_ssm_patch_baseline aws_ssoadmin_account_assignment aws_ssoadmin_instance aws_ssoadmin_managed_policy_attachment aws_ssoadmin_permission_set aws_sts_caller_identity aws_tagging_resource aws_vpc aws_vpc_customer_gateway aws_vpc_dhcp_options aws_vpc_egress_only_internet_gateway aws_vpc_eip aws_vpc_eip_address_transfer aws_vpc_endpoint aws_vpc_endpoint_service aws_vpc_flow_log aws_vpc_flow_log_event aws_vpc_internet_gateway aws_vpc_nat_gateway aws_vpc_nat_gateway_metric_bytes_out_to_destination aws_vpc_network_acl aws_vpc_peering_connection aws_vpc_route aws_vpc_route_table aws_vpc_security_group aws_vpc_security_group_rule aws_vpc_subnet aws_vpc_verified_access_endpoint aws_vpc_verified_access_group aws_vpc_verified_access_instance aws_vpc_verified_access_trust_provider aws_vpc_vpn_connection aws_vpc_vpn_gateway aws_waf_rate_based_rule aws_waf_rule aws_waf_rule_group aws_waf_web_acl aws_wafregional_rule aws_wafregional_rule_group aws_wafregional_web_acl aws_wafv2_ip_set aws_wafv2_regex_pattern_set aws_wafv2_rule_group aws_wafv2_web_acl aws_wellarchitected_answer aws_wellarchitected_check_detail aws_wellarchitected_check_summary aws_wellarchitected_consolidated_report aws_wellarchitected_lens aws_wellarchitected_lens_review aws_wellarchitected_lens_review_improvement aws_wellarchitected_lens_review_report aws_wellarchitected_lens_share aws_wellarchitected_milestone aws_wellarchitected_notification aws_wellarchitected_share_invitation aws_wellarchitected_workload aws_wellarchitected_workload_share aws_workspaces_directory aws_workspaces_workspace

Table: aws_cloudtrail_query

AWS CloudTrail Lake lets you run SQL-based queries on your events. CloudTrail Lake converts existing events in row-based JSON format to Apache ORC format. ORC is a columnar storage format that is optimized for fast retrieval of data.

Only queries from the past seven days are returned from the API. If you'd like to shorten the list of results, you can specify a specific creation_time and/or query_status in the query.

Examples

Basic info

select
  query_id,
  event_data_store_arn,
  query_status,
  query_status,
  creation_time,
  events_matched,
  events_scanned
from
  aws_cloudtrail_query;

List queries that are failed

select
  query_id,
  event_data_store_arn,
  query_status,
  creation_time,
  query_string,
  execution_time_in_millis
from
  aws_cloudtrail_query
where
  query_status = 'FAILED';

Get event data store details for the queries

select
  q.query_id as query_id,
  q.event_data_store_arn as event_data_store_arn,
  s.name as event_data_store_name,
  s.status as event_data_store_status,
  s.multi_region_enabled as multi_region_enabled,
  s.termination_protection_enabled as termination_protection_enabled,
  s.updated_timestamp as event_data_store_updated_timestamp
from
  aws_cloudtrail_query as q,
  aws_cloudtrail_event_data_store as s
where
  s.arn = q.event_data_store_arn;

List queries created within the last 3 days

select
  query_id,
  event_data_store_arn,
  query_status,
  creation_time,
  query_string,
  execution_time_in_millis
from
  aws_cloudtrail_query
where
  creation_time <= now() - interval '3' day;

.inspect aws_cloudtrail_query

AWS CloudTrail Query

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.
bytes_scanned	bigint	Gets metadata about a query, including the number of events that were matched, the total number of events scanned, the query run time in milliseconds, and the query's creation time.
creation_time	timestamp with time zone	The creation time of the query.
delivery_s3_uri	text	The URI for the S3 bucket where CloudTrail delivered query results, if applicable.
delivery_status	text	The delivery status.
error_message	text	The error message returned if a query failed.
event_data_store_arn	text	The ID of the event data store.
events_matched	bigint	The number of events that matched a query.
events_scanned	bigint	The number of events that the query scanned in the event data store.
execution_time_in_millis	bigint	The query's run time, in milliseconds.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
query_id	text	The ID of the query.
query_status	text	The status of a query. Values for QueryStatus include QUEUED, RUNNING, FINISHED, FAILED, TIMED_OUT, or CANCELLED.
query_string	text	The SQL code of a query.
region	text	The AWS Region in which the resource is located.
title	text	Title of the resource.