Zum Hauptinhalt springen

How to query AWS EC2 instances with StackQL

EC2 instances are exposed as the table aws.ec2.instances. Any field returned by the EC2 DescribeInstances API is a queryable column, and region is a required WHERE predicate because the API is regional.

Prerequisites​

Basic query​

List instances in one region with their type and addressing:

SELECT
instance_id,
instance_type,
private_ip_address,
public_ip_address,
launch_time
FROM aws.ec2.instances
WHERE region = 'us-east-1';

Discover the schema first​

Two metadata commands make the resource self-describing - no AWS documentation required:

DESCRIBE aws.ec2.instances;
SHOW METHODS IN aws.ec2.instances;

DESCRIBE lists the available columns. SHOW METHODS lists the operations (the describe_instances SELECT method, the terminate_instances DELETE method, and EXEC lifecycle methods such as start_instances, stop_instances, and reboot_instances) with the required parameters for each. These commands work without credentials.

Multi-region aggregation​

A region IN (...) predicate runs one API call per region in parallel and aggregates the results - a regional service queried like a global table:

SELECT region, instance_type, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region IN ('us-east-1','us-west-2','eu-west-1','ap-southeast-2')
GROUP BY region, instance_type;

This pattern is the basis of account-wide inventory and drift baselines: the result is the live answer, not a snapshot.

Filtering on attributes​

Standard SQL predicates apply to any column. For example, instances exposed to the internet:

SELECT instance_id, instance_type, public_ip_address
FROM aws.ec2.instances
WHERE region = 'us-east-1'
AND public_ip_address IS NOT NULL;

Structured columns such as state and tags return JSON, which can be unpacked with the JSON functions of the configured SQL backend.