Table: aws_elbv2_load_balancers
This table shows data for Amazon Elastic Load Balancer (ELB) v2 Load Balancers.
The primary key for this table is arn.
Relations
The following tables depend on aws_elbv2_load_balancers:
Columns
| Name | Type |
|---|---|
| _cq_id | uuid |
| _cq_parent_id | uuid |
| account_id | utf8 |
| region | utf8 |
| tags | json |
| arn (PK) | utf8 |
| availability_zones | json |
| canonical_hosted_zone_id | utf8 |
| created_time | timestamp[us, tz=UTC] |
| customer_owned_ipv4_pool | utf8 |
| dns_name | utf8 |
| enforce_security_group_inbound_rules_on_private_link_traffic | utf8 |
| ip_address_type | utf8 |
| load_balancer_arn | utf8 |
| load_balancer_name | utf8 |
| scheme | utf8 |
| security_groups | list<item: utf8, nullable> |
| state | json |
| type | utf8 |
| vpc_id | utf8 |
Example Queries
These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.
Application Load Balancer deletion protection should be enabled
SELECT
'Application Load Balancer deletion protection should be enabled' AS title,
lb.account_id,
lb.arn AS resource_id,
CASE
WHEN lb.type = 'application' AND (a.value)::BOOL IS NOT true THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_elbv2_load_balancers AS lb
INNER JOIN aws_elbv2_load_balancer_attributes AS a ON
a.load_balancer_arn = lb.arn AND a.key = 'deletion_protection.enabled';Application load balancers should be configured to drop HTTP headers
SELECT
'Application load balancers should be configured to drop HTTP headers'
AS title,
lb.account_id,
lb.arn AS resource_id,
CASE
WHEN lb.type = 'application' AND (a.value)::BOOL IS NOT true THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_elbv2_load_balancers AS lb
INNER JOIN aws_elbv2_load_balancer_attributes AS a ON
a.load_balancer_arn = lb.arn
AND a.key = 'routing.http.drop_invalid_header_fields.enabled';Application and Classic Load Balancers logging should be enabled
(
SELECT
'Application and Classic Load Balancers logging should be enabled' AS title,
lb.account_id,
lb.arn AS resource_id,
CASE
WHEN lb.type = 'application' AND (a.value)::BOOL IS NOT true THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_elbv2_load_balancers AS lb
INNER JOIN aws_elbv2_load_balancer_attributes AS a ON
a.load_balancer_arn = lb.arn AND a.key = 'access_logs.s3.enabled'
)
UNION
(
SELECT
'Application and Classic Load Balancers logging should be enabled'
AS title,
account_id,
arn AS resource_id,
CASE
WHEN (attributes->'AccessLog'->>'Enabled')::BOOL IS NOT true THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_elbv1_load_balancers
);Find all ELB V2s that are Internet Facing
SELECT
'Find all ELB V2s that are Internet Facing' AS title,
account_id,
arn AS resource_id,
CASE WHEN scheme = 'internet-facing' THEN 'fail' ELSE 'pass' END AS status
FROM
aws_elbv2_load_balancers;Unused ELB load balancer
WITH
listener AS (SELECT DISTINCT load_balancer_arn FROM aws_elbv2_listeners),
target_group
AS (
SELECT
DISTINCT unnest(load_balancer_arns) AS load_balancer_arn
FROM
aws_elbv2_target_groups
)
SELECT
'Unused ELB load balancer' AS title,
lb.account_id,
lb.arn AS resource_id,
'fail' AS status
FROM
aws_elbv2_load_balancers AS lb
LEFT JOIN listener ON listener.load_balancer_arn = lb.arn
LEFT JOIN target_group ON target_group.load_balancer_arn = lb.arn
WHERE
listener.load_balancer_arn IS NULL OR target_group.load_balancer_arn IS NULL;