As of the writing of this blog, CloudQuery supports over 155 resources across 61 services in AWS (!) and many more in GCP, Azure, etc. Although this gives users the capabilities to answer many of their questions regarding your security, visibility and infrastructure, it would be great to have a single view of all your AWS resources, right?
So here at CloudQuery we built a simple aws_resources view, to demonstrate the power of using a SQL database to create a single pane for all your fetched AWS resources. The aws_resources view allows us to ask questions on all our resources allowing us to filter by service, region, account and more!
Getting Started
As always, before we create this view, you should check out our quickstart guide, and make sure you execute a sync.
After our AWS provider is set up and we executed our sync, run the following SQL in your database. This statement creates a view that extracts and transform each row in our aws tables with an arn column into a aws_resource form, and unites all of them rows into our singular view.
DROP VIEW IF EXISTS aws_resources;
DO $$ DECLARE tbl TEXT; strSQL TEXT = ''; BEGIN -- iterate over every table in our information_schema that has an `arn` column available FOR tbl IN SELECT DISTINCT table_name FROM information_schema.columns WHERE table_name LIKE 'aws_%s' and COLUMN_NAME IN ('account_id', 'request_account_id') INTERSECT SELECT table_name FROM information_schema.columns WHERE table_name LIKE 'aws_%s' and COLUMN_NAME = 'arn' LOOP -- UNION each table query to create one view IF NOT (strSQL = ''::TEXT) THEN strSQL = strSQL || ' UNION ALL '; END IF; -- create an SQL query to select from table and transform it into our resources view schema strSQL = strSQL || FORMAT(E' SELECT _cq_id, _cq_source_name, _cq_sync_time, %L AS _cq_table, COALESCE(%s, SPLIT_PART(arn, \':\', 5)) AS account_id, COALESCE(%s, %s, SPLIT_PART(arn, \':\', 5)) AS request_account_id, %s AS region, SPLIT_PART(arn, \':\', 2) AS PARTITION, SPLIT_PART(arn, \':\', 3) AS service, CASE WHEN SPLIT_PART(SPLIT_PART(ARN, \':\', 6), \'/\', 2) = \'\' AND SPLIT_PART(arn, \':\', 7) = \'\' THEN NULL ELSE SPLIT_PART(SPLIT_PART(arn, \':\', 6), \'/\', 1) END AS TYPE, arn, %s AS tags FROM %s', tbl, CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='account_id' AND table_name=tbl) THEN 'account_id' ELSE 'NULL' END, CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='request_account_id' AND table_name=tbl) THEN 'request_account_id' ELSE 'NULL' END, CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='account_id' AND table_name=tbl) THEN 'account_id' ELSE 'NULL' END, CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='region' AND table_name=tbl) THEN 'region' ELSE E'\'unavailable\'' END, CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE column_name='tags' AND table_name=tbl) THEN 'tags' ELSE '''{}''::jsonb' END, tbl); END LOOP;
IF strSQL = ''::TEXT THEN RAISE EXCEPTION 'No tables found with ARN and ACCOUNT_ID columns. Run a sync first and try again.'; ELSE EXECUTE FORMAT('CREATE VIEW aws_resources AS (%s)', strSQL); END IF;
END $$;
Up to date version of this query can be found here.
Run the following query to view all your AWS resources
select * from aws_resources limit 100;
Example Queries
What resources don’t have tags?
select * from aws_resources where tags = '{}';
What resources don’t have any of these tags?
select * from aws_resources where not tags ?| array['name', 'version'];
We can easily invert this or make sure that all of these tags exist with the ?& operator instead.
What resources of Type Z in service X exist in Region Y?
SELECT * FROM aws_resources WHERE region LIKE 'us-east%' AND service = 'ec2' AND (type = 'instance' OR type = 'network-interface');
Here we can easily query all resources in the ec2 service from the us-east regions, that they are of type instance and network-interface
Join To existing tables
SELECT instance_type, aws_resources.id, aws_resources.arn, launch_time, public_ip_address, private_ip_address, state_name, vpc_id FROM aws_resources INNER JOIN aws_ec2_instances ON aws_resources.cq_id = aws_ec2_instances.cq_id WHERE aws_resources.region LIKE 'us-east%' AND aws_resources.service = 'ec2' AND aws_resources.type = 'instance' AND aws_resources.tags = '{}'
We can easily create join to our existing tables to get more information, we can join either on the cq_id or event the id column. This allows to get more specific information on the resources,
in this case, launch_time, public_ip_address, vpc_id etc’.
Count total distinct resources by ARN
select count(distinct arn) as distinct_resources, count(*) as total from aws_resources
What's next?
There are many views we can create on top of CloudQuery that make it easier to query our data, some examples can be found in our policies. We are working on more awesome views that will make your life even easier, such as aws_policies
We are always excited to hear use cases or questions around CloudQuery so feel free to hop into our Community and let us know.
Want help getting started? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.
Written by
Ron Eliahu
Ron is a software engineer with a passion for building open source projects and databases. He is proficient in JavaScript, MongoDB and React and has experience in Python, C# and SQL. Additionally, he is familiar with PHP, Angular and Redis.
Ron has contributed to a number of GitHub projects including a real time coding collaboration project for students and an image matching service that helps people locate lost pets.
Try CloudQuery yourself
Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.
Download →
Schedule a demo →
Related posts
AWS
Azure
GCP
Security
How Do You Run CloudQuery Without Static Credentials?
Workload identity federation for GCP and Azure, RDS IAM auth, and Lakebase OAuth let self-hosted CloudQuery syncs run without stored static credentials.
Ben Bernays
September 28, 2026
AWS
Security
Tutorials
Finding AWS Unrestricted Outbound Access in 2026: EC2, Lambda, and Beyond
Find EC2, Lambda, and ECS Fargate resources with unrestricted outbound access in AWS using SQL. Why egress controls still break attack chains in 2026.
Joe Karlsson
May 5, 2026
AWS
Security
Tutorials
AWS IAM Identity Center with Google Workspace as an IdP: 2026 Step-by-Step Guide
Set up AWS IAM Identity Center with Google Workspace: SAML authentication, SCIM user provisioning, and group sync with CLI or ssosync. Updated for 2026.
Joe Karlsson
May 5, 2026
AWS
Best Practices
Security
AWS CIS Benchmark in 2026: Automated Compliance with SQL-Based Policies
AWS CIS Benchmark hit v3.0 in 2024 with 40+ controls. Run automated compliance checks using SQL policies with exception management and multi-account views.
Joe Karlsson
May 5, 2026
AWS
Cloud Infrastructure
Security
Tutorials
Integrate AbuseIPDB Threat Intelligence with CloudQuery
Enrich your cloud asset inventory with AbuseIPDB threat intelligence to identify public cloud IPs with known abuse reports.
Joe Karlsson
April 20, 2026
AWS
Security
Tutorials
AWS IAM Identity Center (Formerly AWS SSO): The Complete 2026 Guide
AWS IAM Identity Center replaced AWS SSO in 2022. Learn how to set it up, configure permission sets, use the AWS CLI, and audit access with CloudQuery.
Joe Karlsson
April 6, 2026
AWS
Security
AWS Delegated Administrator: Setup Guide
Set up AWS Delegated Administrator across 37+ services. Covers CLI and Terraform setup, IAM policies, delegation patterns, and auditing with CloudQuery.
Jason Kao
February 25, 2026
AWS
Security
Tutorials
Set Up AWS CLI with SSO (IAM Identity Center) — Step by Step
Set up AWS CLI v2 with IAM Identity Center in 5 minutes. Covers SSO registration scopes, sso-session config, token refresh, and multi-account profiles.
Yevgeny Pats
February 25, 2026
AWS
Cloud Infrastructure
Security
CodeBreach Vulnerability: How to Detect If Your AWS CodeBuild Projects Are Affected
Detect CodeBreach vulnerability in your AWS CodeBuild webhook filters. SQL queries to find unanchored ACTOR_ACCOUNT_ID patterns and missing security controls.