How to List All EC2 Instances Across Multiple Accounts and Regions

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:

1. Sign Up for CloudQuery
   Start by creating a CloudQuery account to get started.
2. 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
3. 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. 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

1. Can I use CloudQuery with other cloud providers besides AWS?
   Yes, CloudQuery supports a wide range of cloud providers, including GCP, Azure, and more.
2. 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.
3. 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.