---

name: steampipe-aws description: "Query AWS infrastructure using steampipe SQL. Use on 'what resources', 'find instances', 'list buckets', 'show IAM', 'audit AWS', 'security groups', or any AWS resource discovery task."

Steampipe AWS Skill

Query AWS infrastructure using SQL via steampipe CLI.

When to Use

• Discover AWS resources across accounts
• Audit security configurations
• Find resources by tags, regions, or properties
• Investigate IAM policies and permissions
• Check compliance and security posture
• Troubleshoot infrastructure issues

How to Query

Copysteampipe query "SELECT * FROM aws_s3_bucket LIMIT 5"
steampipe query --output json "SELECT name, region FROM aws_ec2_instance"
steampipe query --output csv "SELECT * FROM aws_iam_role"

Common Tables

Compute

Storage

Networking

IAM & Security

Monitoring

Query Patterns

Find Public Resources

Copy-- Public S3 buckets
SELECT name, region, bucket_policy_is_public
FROM aws_s3_bucket
WHERE bucket_policy_is_public = true;

-- Public security group rules
SELECT group_id, type, ip_protocol, from_port, to_port, cidr_ipv4
FROM aws_vpc_security_group_rule
WHERE cidr_ipv4 = '0.0.0.0/0';

Find by Tags

Copy-- EC2 instances by tag
SELECT instance_id, tags ->> 'Name' as name, instance_state
FROM aws_ec2_instance
WHERE tags ->> 'Environment' = 'production';

-- Resources missing required tags
SELECT instance_id, tags
FROM aws_ec2_instance
WHERE tags ->> 'Owner' IS NULL;

IAM Analysis

Copy-- Roles with admin access
SELECT name, assume_role_policy_std
FROM aws_iam_role
WHERE attached_policy_arns @> '["arn:aws:iam::aws:policy/AdministratorAccess"]';

-- Old access keys
SELECT user_name, access_key_id, create_date, status
FROM aws_iam_access_key
WHERE create_date < now() - interval '90 days';

Cross-Resource Joins

Copy-- EC2 instances with their security groups
SELECT 
  i.instance_id,
  i.tags ->> 'Name' as name,
  sg.group_name
FROM aws_ec2_instance i
JOIN aws_vpc_security_group sg 
  ON sg.group_id = ANY(i.security_groups);

Cost-Related

Copy-- Large EC2 instances
SELECT instance_id, instance_type, tags ->> 'Name' as name
FROM aws_ec2_instance
WHERE instance_type LIKE '%.xlarge%' OR instance_type LIKE '%.2xlarge%';

-- Unattached EBS volumes
SELECT volume_id, size, create_time
FROM aws_ebs_volume
WHERE jsonb_array_length(attachments) = 0;

Output Formats

Copy# Table (default, human-readable)
steampipe query "SELECT name FROM aws_s3_bucket"

# JSON (for parsing)
steampipe query --output json "SELECT name FROM aws_s3_bucket"

# CSV (for export)
steampipe query --output csv "SELECT name FROM aws_s3_bucket"

# Line (key=value)
steampipe query --output line "SELECT name FROM aws_s3_bucket"

Tips

• Use LIMIT to avoid huge result sets
• Use --output json when you need to parse results
• Filter by region if you only care about specific regions
• Use ->> for JSONB tag access
• Tables are named aws_{service}_{resource}
• Run steampipe plugin install aws if plugin not installed
• Check connection with steampipe query "SELECT 1"