# configuration.yml
kind: source
spec:
  name: aws
  path: cloudquery/aws
  destinations: [postgresql]
  tables:
    - aws_ec2_instances
    - aws_rds_instances
    - aws_s3_buckets

-- Proprietary query language
SELECT name, ci_type, status
FROM cmdb_ci
WHERE ci_type = 'Server'

-- Standard PostgreSQL
SELECT
  instance_id,
  instance_type,
  vpc_id,
  public_ip_address,
  tags
FROM aws_ec2_instances
WHERE state_name = 'running'
  AND tags->>'Environment' = 'production'
  AND public_ip_address IS NOT NULL;

SELECT
  i.instance_id,
  i.public_ip_address,
  sg.group_id,
  sg.ip_permissions
FROM aws_ec2_instances i
JOIN aws_ec2_security_groups sg
  ON sg.group_id = ANY(i.security_groups)
WHERE i.public_ip_address IS NOT NULL
  AND sg.ip_permissions @> '[{"IpProtocol": "-1", "IpRanges": [{"CidrIp": "0.0.0.0/0"}]}]'::jsonb;

SELECT account_id, bucket_name, region, encryption_configuration
FROM aws_s3_buckets
WHERE encryption_configuration IS NULL;

SELECT volume_id, size, volume_type, attachments, create_time
FROM aws_ebs_volumes
WHERE attachments = '[]'::jsonb
  AND create_time < NOW() - INTERVAL '30 days';

SELECT instance_id, tags, security_groups
FROM aws_ec2_instances
WHERE NOT tags ? 'Owner' OR NOT tags ? 'Environment' OR NOT tags ? 'CostCenter';

WITH terraform_instances AS (
  SELECT unnest(ARRAY['i-abc123', 'i-def456', 'i-ghi789']) AS instance_id
)
SELECT t.instance_id
FROM terraform_instances t
LEFT JOIN aws_ec2_instances a ON t.instance_id = a.instance_id
WHERE a.instance_id IS NULL;

SELECT table_name, action, changed_at, resource_id
FROM cloudquery_sync_log
WHERE changed_at > NOW() - INTERVAL '1 hour'
ORDER BY changed_at DESC;