How to List All EC2 Instances Across Multiple Accounts and Regions

•

January 15, 2025

Search for assets and maintaining up-to-date inventory across AWS accounts and regions is a constant challenge that can require either lots of “click-ops” or ad-hoc scripts. In this post, you will learn how to list all your AWS EC2 instances across multiple accounts and regions with CloudQuery Asset Inventory to help you gain visibility and improve your security and compliance posture.

We’ll break it down step-by-step:

Connect AWS accounts or Organizations to CloudQuery to pull in the relevant metadata to CloudQuery.
Query your cloud data with simple SQL commands to list all your AWS EC2 instances across accounts and regions.

By the end, you’ll have a centralized, queryable view of all your AWS EC2 instances and a better understanding of how CloudQuery can be used to build an up-to-date inventory of your cloud infrastructure.

How to Sync Your AWS Cloud Data with CloudQuery #

Follow these simple steps to set up CloudQuery and sync your AWS data for a complete EC2 asset inventory:

Sign Up for CloudQuery Start by creating a CloudQuery account to get started.
Set Up Your Data Sync Configure a new sync using the AWS Source Integration. Make sure you have read-only access to your AWS account to enable data syncing.
To capture all EC2 instances across regions, add the following table to your sync settings: tables: [aws_ec2_instances]. Learn more about the aws_ec2_instances table here
Run Your Data Sync Click the “Run Sync” button to pull your AWS data into CloudQuery. Once synced, you can query and analyze your EC2 inventory.

How to List All EC2 Instances Across All AWS Regions #

Using the built in SQL editor in CloudQuery, you can use the following query to list all EC2 instances across all AWS regions from the aws_ec2_instances table:

SELECT
    instance_id,
    region,
    instance_type,
    public_ip_address,
    private_ip_address,
    JSONExtractString(state, 'Name') AS state,
    launch_time
FROM
    aws_ec2_instances
ORDER BY
    region ASC, instance_id ASC;

How to Find Stopped EC2 Instances Across All AWS Regions #

Use this query to identify stopped instances, helping you reduce costs and improve efficiency:

SELECT
    instance_id,
    region,
    instance_type,
    public_ip_address,
    private_ip_address,
    JSONExtractString(state, 'Name') AS state,
    launch_time
FROM
    aws_ec2_instances
WHERE
    state = 'stopped'
ORDER BY
    region ASC, instance_id ASC;

How to Identify EC2 Instances Without Public IPs Across Regions #

Identifying EC2 instances without public IPs enhances security by reducing internet exposure, ensures proper network configuration, optimizes costs, and supports compliance with access control policies. To locate EC2 instances without public IP addresses across AWS regions, you can use the following:

SELECT
    instance_id,
    region,
    instance_type,
    private_ip_address,
    JSONExtractString(state, 'Name') AS state,
    launch_time
FROM
    aws_ec2_instances
WHERE
    public_ip_address IS NULL
ORDER BY
    region ASC, instance_id ASC;

Wrap Up #

We explored using CloudQuery to list all your EC2 instances across AWS regions and query that data with SQL. CloudQuery is a powerful cloud governance solution that allows you to pull data from virtually any cloud provider, providing smarter cloud audits, better cloud asset inventories, and more targeted security monitoring.

CloudQuery streamlines building asset inventories, ensures compliance, and optimizes cloud operations for any cloud provider. Start managing your cloud assets more effectively today. Contact us to start using CloudQuery today.

Have questions or need support? Join the growing CloudQuery Developer Community to connect with fellow developers, share insights, and access a wealth of searchable knowledge. Whether you’re troubleshooting, exploring new use cases, or brainstorming ideas, our community is here to help.

Finally, let’s keep the conversation going! Share what your team is doing to proactively manage cloud security with us on LinkedIn, X, or in our Community Forum.

FAQs #

Can I use CloudQuery with other cloud providers besides AWS? Yes, CloudQuery supports a wide range of cloud providers, including GCP, Azure, and more.
How do I authenticate CloudQuery to access my AWS resources? You’ll need AWS read-only credentials to authenticate. CloudQuery supports multiple authentication methods, such as environment variables, shared credentials files, and IAM roles.
Can I query other types of AWS resources besides EC2 instances? Absolutely. CloudQuery allows you to query various AWS resources, including S3 buckets, security groups, IAM roles, and more.

Written by Joe Karlsson

Joe Karlsson (He/They) is an Engineer turned Developer Advocate (and massive nerd). Joe empowers developers to think creatively when building applications, through demos, blogs, videos, or whatever else developers need.

On this page