steampipe plugin install aws

Table: aws_ec2_application_load_balancer - Query AWS EC2 Application Load Balancer using SQL

The AWS EC2 Application Load Balancer is a resource within Amazon's Elastic Compute Cloud (EC2) service that automatically distributes incoming application traffic across multiple targets, such as EC2 instances, in multiple Availability Zones. This enhances the fault tolerance of your applications. The load balancer serves as a single point of contact for clients, which increases the availability of your application.

Table Usage Guide

The aws_ec2_application_load_balancer table in Steampipe allows you to gain insights into the Application Load Balancers within your AWS EC2 service. The table provides detailed information about each Application Load Balancer, including its current state, associated security groups, availability zones, type, scheme, and other important attributes. You can use this table to query load balancer-specific details, monitor the health of the load balancers, assess load balancing configurations, and much more. The schema outlines various attributes of the Application Load Balancer, such as the ARN, DNS name, canonical hosted zone ID, and creation date, among others.

Examples

Security group attached to the application load balancers

Explore which security groups are linked to your application load balancers, enabling you to assess potential vulnerabilities and ensure optimal security configurations. This can be particularly useful for identifying security loopholes and reinforcing your system's defenses.

select
name,
jsonb_array_elements_text(security_groups) as attached_security_group
from
aws_ec2_application_load_balancer;
select
name,
json_extract(json_each.value, '$') as attached_security_group
from
aws_ec2_application_load_balancer,
json_each(security_groups);

Availability zone information

Discover the segments that provide insights into the availability zones of your AWS EC2 application load balancer. This can be particularly useful for understanding your load balancer's distribution and identifying potential areas for improvement or troubleshooting.

select
name,
az ->> 'LoadBalancerAddresses' as load_balancer_addresses,
az ->> 'OutpostId' as outpost_id,
az ->> 'SubnetId' as subnet_id,
az ->> 'ZoneName' as zone_name
from
aws_ec2_application_load_balancer
cross join jsonb_array_elements(availability_zones) as az;
select
name,
json_extract(az.value, '$.LoadBalancerAddresses') as load_balancer_addresses,
json_extract(az.value, '$.OutpostId') as outpost_id,
json_extract(az.value, '$.SubnetId') as subnet_id,
json_extract(az.value, '$.ZoneName') as zone_name
from
aws_ec2_application_load_balancer,
json_each(availability_zones) as az;

List of application load balancers whose availability zone count is less than 1

Explore which application load balancers are potentially at risk due to being located in less than two availability zones. This is useful for identifying weak points in your infrastructure and improving system resilience.

select
name,
count(az ->> 'ZoneName') < 2 as zone_count_1
from
aws_ec2_application_load_balancer
cross join jsonb_array_elements(availability_zones) as az
group by
name;
select
name,
count(json_extract(az.value, '$.ZoneName')) < 2 as zone_count_1
from
aws_ec2_application_load_balancer,
json_each(availability_zones) as az
group by
name;

List of application load balancers whose logging is not enabled

Identify instances where application load balancers in your AWS EC2 environment have their logging feature disabled. This is useful for maintaining security and compliance by ensuring all load balancers are properly recording activity.

select
name,
lb ->> 'Key' as logging_key,
lb ->> 'Value' as logging_value
from
aws_ec2_application_load_balancer
cross join jsonb_array_elements(load_balancer_attributes) as lb
where
lb ->> 'Key' = 'access_logs.s3.enabled'
and lb ->> 'Value' = 'false';
select
name,
json_extract(lb.value, '$.Key') as logging_key,
json_extract(lb.value, '$.Value') as logging_value
from
aws_ec2_application_load_balancer,
json_each(load_balancer_attributes) as lb
where
json_extract(lb.value, '$.Key') = 'access_logs.s3.enabled'
and json_extract(lb.value, '$.Value') = 'false';

List of application load balancers whose deletion protection is not enabled

Identify instances where application load balancers are not safeguarded against unintended deletion. This information can be useful in ensuring system resilience and minimizing service disruptions.

select
name,
lb ->> 'Key' as deletion_protection_key,
lb ->> 'Value' as deletion_protection_value
from
aws_ec2_application_load_balancer
cross join jsonb_array_elements(load_balancer_attributes) as lb
where
lb ->> 'Key' = 'deletion_protection.enabled'
and lb ->> 'Value' = 'false';
select
name,
json_extract(lb.value, '$.Key') as deletion_protection_key,
json_extract(lb.value, '$.Value') as deletion_protection_value
from
aws_ec2_application_load_balancer,
json_each(load_balancer_attributes) as lb
where
json_extract(lb.value, '$.Key') = 'deletion_protection.enabled'
and json_extract(lb.value, '$.Value') = 'false';

Control examples

Schema for aws_ec2_application_load_balancer

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntext=The Amazon Resource Name (ARN) of the load balancer.
availability_zonesjsonbThe subnets for the load balancer.
canonical_hosted_zone_idtextThe ID of the Amazon Route 53 hosted zone associated with the load balancer.
created_timetimestamp with time zoneThe date and time the load balancer was created.
customer_owned_ipv4_pooltextThe ID of the customer-owned address pool.
dns_nametextThe public DNS name of the load balancer.
ip_address_typetextThe type of IP addresses used by the subnets for your load balancer.
load_balancer_attributesjsonbThe AWS account ID of the image owner.
nametext=The friendly name of the Load Balancer that was provided during resource creation.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
schemetextThe load balancing scheme of load balancer.
security_groupsjsonbThe IDs of the security groups for the load balancer.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
state_codetextCurrent state of the load balancer.
state_reasontextA description of the state.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags attached to the load balancer.
titletextTitle of the resource.
typetextThe type of load balancer.
vpc_idtextThe ID of the VPC for the load balancer.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_ec2_application_load_balancer