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
- AWS credentials exported as environment variables - see How to authenticate StackQL to AWS
- The AWS provider installed:
REGISTRY PULL aws;
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.
Related concepts
- How to authenticate StackQL to AWS - credential setup
- How to query S3 buckets with StackQL - the S3 pattern, which differs slightly
- What is Infrastructure Drift? - using these queries as baselines
- What is Queryable Infrastructure? - the model behind the table abstraction
- Getting started with StackQL - end-to-end setup