steampipe plugin install aws

Table: aws_ec2_gateway_load_balancer - Query AWS EC2 Gateway Load Balancer using SQL

The AWS EC2 Gateway Load Balancer is a resource that operates at the third layer of the Open Systems Interconnection (OSI) model, the network layer. It is designed to manage, scale, and secure your network traffic in a simple and cost-effective manner. This service provides you with a single point of contact for all network traffic, regardless of the scale, and ensures that it is efficiently distributed across multiple resources.

Table Usage Guide

The aws_ec2_gateway_load_balancer table in Steampipe provides you with information about Gateway Load Balancers within Amazon Elastic Compute Cloud (EC2). This table allows you, as a DevOps engineer, to query load balancer-specific details, including its configuration, state, type, and associated tags. You can utilize this table to gather insights on load balancers, such as their availability zones, subnets, and security groups. The schema outlines the various attributes of the Gateway Load Balancer for you, including the load balancer ARN, creation date, DNS name, scheme, and associated tags.

Examples

Basic gateway load balancer info

Determine the areas in which your AWS EC2 gateway load balancer is deployed and its current operational state. This information can help you assess the elements within your network infrastructure and optimize for better performance.

select
name,
arn,
type,
state_code,
vpc_id,
availability_zones
from
aws_ec2_gateway_load_balancer;
select
name,
arn,
type,
state_code,
vpc_id,
availability_zones
from
aws_ec2_gateway_load_balancer;

Availability zone information of all the gateway load balancers

Determine the areas in which your gateway load balancers are located and gain insights into their specific settings. This can help you assess your load balancing strategy and optimize resource allocation.

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_gateway_load_balancer,
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_gateway_load_balancer,
json_each(availability_zones) as az;

List of gateway load balancers whose availability zone count is less than 2

Determine the areas in which gateway load balancers may be at risk of service disruption due to having less than two availability zones. This can help in proactive infrastructure planning and risk mitigation.

select
name,
count(az ->> 'ZoneName') as zone_count
from
aws_ec2_gateway_load_balancer,
jsonb_array_elements(availability_zones) as az
group by
name
having
count(az ->> 'ZoneName') < 2;
select
name,
count(json_extract(az.value, '$.ZoneName')) as zone_count
from
aws_ec2_gateway_load_balancer,
json_each(availability_zones) as az
group by
name
having
count(json_extract(az.value, '$.ZoneName')) < 2;

List of gateway load balancers whose deletion protection is not enabled

Identify instances where gateway load balancers do not have deletion protection enabled. This can be useful to ensure the security and longevity of your data by avoiding accidental deletion.

select
name,
lb ->> 'Key' as deletion_protection_key,
lb ->> 'Value' as deletion_protection_value
from
aws_ec2_gateway_load_balancer,
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_gateway_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';

List of gateway load balancers whose load balancing cross zone is enabled

Explore which gateway load balancers have the cross-zone load balancing feature enabled. This is useful in understanding the traffic distribution across multiple zones for better load balancing and increased application availability.

select
name,
lb ->> 'Key' as load_balancing_cross_zone_key,
lb ->> 'Value' as load_balancing_cross_zone_value
from
aws_ec2_gateway_load_balancer,
jsonb_array_elements(load_balancer_attributes) as lb
where
lb ->> 'Key' = 'load_balancing.cross_zone.enabled'
and lb ->> 'Value' = 'true';
select
name,
json_extract(lb.value, '$.Key') as load_balancing_cross_zone_key,
json_extract(lb.value, '$.Value') as load_balancing_cross_zone_value
from
aws_ec2_gateway_load_balancer,
json_each(load_balancer_attributes) as lb
where
json_extract(lb.value, '$.Key') = 'load_balancing.cross_zone.enabled'
and json_extract(lb.value, '$.Value') = 'true';

Security group attached to the gateway load balancers

Identify instances where your security groups are linked to your gateway load balancers. This can help you assess your security setup and ensure appropriate measures are in place.

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

List of gateway load balancer with state other than active

Identify instances where gateway load balancers in AWS EC2 are not in an 'active' state. This is useful to pinpoint potential issues or disruptions in network traffic routing.

select
name,
state_code
from
aws_ec2_gateway_load_balancer
where
state_code <> 'active';
select
name,
state_code
from
aws_ec2_gateway_load_balancer
where
state_code != 'active';

Schema for aws_ec2_gateway_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 gateway load balancer.
canonical_hosted_zone_idtextThe ID of the Amazon Route 53 hosted zone associated with the gateway 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 gateway load balancer.
enforce_security_group_inbound_rules_on_private_link_traffictextIndicates whether to evaluate inbound security group rules for traffic sent to a Network Load Balancer through Amazon Web Services PrivateLink.
ip_address_typetextThe type of IP addresses used by the subnets for your load balancer.
load_balancer_attributesjsonbAttributes deletion protection and cross_zone of gateway load balancer.
nametext=The name of the load balancer.
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 gateway load balancer.
security_groupsjsonbThe IDs of the security groups for the gateway load balancer.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
state_codetextThe state of the load balancer.
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 gateway 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_gateway_load_balancer