Skip to main content

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 SELECT method, the terminate DELETE method, and EXEC lifecycle methods such as start, stop, and reboot) 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 tag_set return JSON, which can be unpacked with the JSON functions of the configured SQL backend.