New in v0.2.0: AWS multi-region queries →

Learn Steampipe

Steampipe exposes APIs and services as a high-performance relational database, giving you the ability to write SQL-based queries to explore, assess and report on dynamic data. With Steampipe, the cloud is your database!

Let's dive in...

Pre-requisites

This tutorial uses the AWS plugin. To gets started, download and install Steampipe, and then install the plugin:

$ steampipe plugin install aws
Installing plugin aws...

Steampipe will download and install additional components the first time you run steampipe query so it may take a few seconds to load initially.

Steampipe will use your default AWS credentials from your credential file and/or environment variables, so you'll need to make sure those are set up as well. If you can run aws ec2 describe-vpcs, you're good to go...

Question

The goal of Steampipe is to simplify the discovery and querying of cloud based configuration information. Steampipe allows you to quickly get answers about your cloud configuration. So let's ask a couple of questions:

  1. Which EC2 instances allow traffic from the whole Internet?
  2. Who owns these insecure instances?

Explore

Steampipe provides commands to allow you to discover and explore the tables and data without leaving the query shell. (Of course this information is all available in the hub if online docs are more your speed...)

Let's fire up Steampipe! Run steampipe query to open an interactive query session:

$ steampipe query
Welcome to Steampipe v0.1.0
Type ".inspect" for more information.
>

Now run the .tables meta-command to list the available tables:

> .tables
 ==> aws
+----------------------------------------+--------------------------------+
|                 Table                  |          Description           |
+----------------------------------------+--------------------------------+
 ...
| aws_ebs_volume                         | AWS EBS Volume                 |
| aws_ec2_ami                            | AWS EC2 AMI                    |
| aws_ec2_application_load_balancer      | AWS EC2 Application Load       |
|                                        | Balancer                       |
| aws_ec2_autoscaling_group              | AWS EC2 Autoscaling Group      |
| aws_ec2_classic_load_balancer          | AWS EC2 Classic Load Balancer  |
| aws_ec2_instance                       | AWS EC2 Instance               |
| aws_ec2_instance_availability          | AWS EC2 Instance Availability  |
| aws_ec2_instance_type                  | AWS EC2 Instance Type          |
| aws_ec2_key_pair                       | AWS EC2 Key Pair               |
...
| aws_vpc_eip                            | AWS VPC Elastic IP             |
| aws_vpc_endpoint                       | AWS VPC Endpoint               |
| aws_vpc_endpoint_service               | AWS VPC Endpoint Service       |
| aws_vpc_internet_gateway               | AWS VPC Internet Gateway       |
| aws_vpc_nat_gateway                    | AWS VPC Network Address        |
|                                        | Translation Gateway            |
| aws_vpc_network_acl                    | AWS VPC Network ACL            |
| aws_vpc_route                          | AWS VPC Route                  |
| aws_vpc_route_table                    | AWS VPC Route table            |
| aws_vpc_security_group                 | AWS VPC Security Group         |
| aws_vpc_security_group_rule            | AWS VPC Security Group Rule    |
| aws_vpc_subnet                         | AWS VPC Subnet                 |
| aws_vpc_vpn_gateway                    | AWS VPC VPN Gateway            |
+----------------------------------------+--------------------------------+

It looks like there an aws_ec2_instance table - let's run .inspect to see what's in that table:

> .inspect aws.aws_ec2_instance
+--------------------------------------------+---------+--------------------------------+
|                   Column                   |  Type   |          Description           |
+--------------------------------------------+---------+--------------------------------+
| account_id                                 | text    | The AWS Account ID in which    |
|                                            |         | the resource is located        |
| akas                                       | jsonb   | A list of AKAs (also-known-as) |
...
| security_groups                            | jsonb   | The security groups for the    |
|                                            |         | instance                       |
| state_code                                 | bigint  | The reason code for the state  |
|                                            |         | change                         |
| subnet_id                                  | text    | The ID of the subnet in which  |
|                                            |         | the instance is running        |
| tags                                       | jsonb   | A map of tags for this         |
|                                            |         | resource                       |
| tags_raw                                   | jsonb   | A list of tags assigned to the |
|                                            |         | instance                       |
| title                                      | text    | The display name for this      |
|                                            |         | resource                       |
| user_data                                  | text    | The user data of the instance  |
| virtualization_type                        | text    | The virtualization type of the |
|                                            |         | instance                       |
| vpc_id                                     | text    | The ID of the VPC in which the |
|                                            |         | instance is running            |
+--------------------------------------------+---------+--------------------------------+

There are quite a few columns in this table! We'll need the instance_id of course, and we track the ownership with tags, so we'll need that too. It looks like there's a security_groups column that we can use to look up the rules in the aws_ec2_security_group_rule table too.

Query

Now that we know what columns we need, let's query the aws_ec2_instance table:

select
  instance_id,
  security_groups,
  tags
from
  aws_ec2_instance;
+---------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------
|     instance_id     |                                                          security_groups                                                           |                                                                
+---------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------
| i-070f3d4a32c073e2d | [{"GroupId":"sg-033addd51410796f8","GroupName":"lamp-stack-smyth-delete-for-mantix-testing-WebServerSecurityGroup-1VV8PKJF8BF9Z"}] | {"aws:cloudformation:logical-id":"WebServerInstance","aws:cloud
| i-0f16e4805caddfd44 | [{"GroupId":"sg-09eb1a601306db315","GroupName":"launch-wizard-7"}]                                                                 | {"owner":"kramerica_industries"}                               
| i-00e86570e5c19523b | [{"GroupId":"sg-029cd86da723916fa","GroupName":"launch-wizard-1"}]                                                                 | {"owner":"j_peterman"}                                         
| i-02ca68e16ad60c399 | [{"GroupId":"sg-029cd86da723916fa","GroupName":"launch-wizard-1"}]                                                                 | {"owner":"kramerica_industries"}                               
| i-00f3c1ab8296b21d5 | [{"GroupId":"sg-017eb739a77f9523c","GroupName":"launch-wizard-3"}]                                                                 | {"owner":"vandelay_industries"}                                
| i-0235270e481292992 | [{"GroupId":"sg-029cd86da723916fa","GroupName":"launch-wizard-1"}]                                                                 | {"owner":"vandelay_industries"}                                
+---------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------

That's the data we want, but some of it is json -- We really only want the owner tag, and we will need to flatten that array of security groups and extract the GroupId:

select
  i.instance_id,
  i.tags ->> 'owner' as owner,
  sg ->> 'GroupId' as sg_id
from
  aws_ec2_instance as i,
  jsonb_array_elements(security_groups) as sg
;
+---------------------+----------------------+----------------------+
|     instance_id     |        owner         |        sg_id         |
+---------------------+----------------------+----------------------+
| i-0235270e481292992 | vandelay_industries  | sg-029cd86da723916fa |
| i-00f3c1ab8296b21d5 | vandelay_industries  | sg-017eb739a77f9523c |
| i-0f16e4805caddfd44 | kramerica_industries | sg-09eb1a601306db315 |
| i-00e86570e5c19523b | j_peterman           | sg-029cd86da723916fa |
| i-02ca68e16ad60c399 | kramerica_industries | sg-029cd86da723916fa |
| i-070f3d4a32c073e2d | vandelay_industries  | sg-033addd51410796f8 |
+---------------------+----------------------+----------------------+

That's a lot better.

We'll need some columns from the aws_ec2_security_group_rule table also:

select 
    group_id,
    type,
    cidr_ip,
    ip_protocol,
    from_port,
    to_port
from  
  aws_vpc_security_group_rule;
+----------------------+---------+-------------------+-------------+-----------+---------+
|       group_id       |  type   |      cidr_ip      | ip_protocol | from_port | to_port |
+----------------------+---------+-------------------+-------------+-----------+---------+
| sg-033addd51410796f8 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-05d0eb638fc39f3c5 | ingress | 182.76.37.66/32   | tcp         |      8182 |    8182 |
| sg-033addd51410796f8 | ingress | 75.22.138.91/32   | tcp         |        22 |      22 |
| sg-06f7be979f5eb9017 | ingress | 0.0.0.0/0         | tcp         |       444 |     444 |
| sg-00d1eac81b2a63231 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-09eb1a601306db315 | ingress | 172.58.140.193/32 | tcp         |        22 |      22 |
| sg-06f7be979f5eb9017 | ingress | 0.0.0.0/0         | tcp         |       443 |     443 |
| sg-029cd86da723916fa | ingress | 0.0.0.0/0         | icmp        |        -1 |      -1 |
| sg-017eb739a77f9523c | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-096bf21c77aaaaffc | ingress | 182.76.37.66/32   | tcp         |      8182 |    8182 |
| sg-0289d50faf11dc7c5 | ingress | 0.0.0.0/0         | tcp         |        22 |      22 |
| sg-033addd51410796f8 | ingress | 0.0.0.0/0         | tcp         |        80 |      80 |
| sg-08134bf862f43aefb | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-06f7be979f5eb9017 | ingress | 0.0.0.0/0         | tcp         |        80 |      80 |
| sg-09eb1a601306db315 | ingress | 75.22.138.91/32   | tcp         |        22 |      22 |
| sg-017eb739a77f9523c | ingress | 0.0.0.0/0         | tcp         |        22 |      22 |
| sg-0ac4ff4ef42e245a0 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-08134bf862f43aefb | ingress |                   |          -1 |           |         |
| sg-06f7be979f5eb9017 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-029cd86da723916fa | ingress |                   | icmp        |        -1 |      -1 |
| sg-096bf21c77aaaaffc | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0289d50faf11dc7c5 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0a132efee839649b0 | ingress | 192.168.0.0/24    | tcp         |        20 |      21 |
| sg-029cd86da723916fa | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-05d0eb638fc39f3c5 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0a132efee839649b0 | ingress | 10.0.0.0/8        | tcp         |       443 |     443 |
| sg-0b475b75515049e16 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |        88 |      88 |
| sg-08c6c727399c99579 | ingress |                   |          -1 |           |         |
| sg-08c6c727399c99579 | ingress | 0.0.0.0/0         | icmpv6      |        -1 |      -1 |
| sg-db104c83          | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | icmp        |        -1 |      -1 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |       389 |     389 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |      1024 |   65535 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |       123 |     123 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |        53 |      53 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |      3268 |    3269 |
| sg-029cd86da723916fa | ingress | 75.22.138.91/32   | tcp         |        22 |      22 |
| sg-08c6c727399c99579 | ingress | 0.0.0.0/0         | icmp        |        -1 |      -1 |
| sg-db104c83          | ingress |                   |          -1 |           |         |
| sg-0a132efee839649b0 | ingress | 172.16.0.0/16     | tcp         |       443 |     443 |
| sg-08c6c727399c99579 | ingress |                   | icmp        |        -1 |      -1 |
| sg-0a132efee839649b0 | ingress |                   | tcp         |        22 |      22 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |       389 |     389 |
| sg-08c6c727399c99579 | ingress | 0.0.0.0/0         | udp         |         0 |   65535 |
| sg-0e2a05296f27460d5 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0e41f987bee809892 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |       445 |     445 |
| sg-0d92bde3dd654d88e | egress  | 10.0.0.0/8        | tcp         |      5431 |    5431 |
| sg-08c6c727399c99579 | ingress |                   | udp         |         0 |   65535 |
| sg-0a132efee839649b0 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |       464 |     464 |
| sg-08c6c727399c99579 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress |                   |          -1 |           |         |
| sg-0c394901c33d48f60 | egress  |                   |          -1 |           |         |
| sg-0a132efee839649b0 | ingress | 0.0.0.0/0         | icmp        |         0 |      -1 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |       135 |     135 |
| sg-0a132efee839649b0 | ingress | 0.0.0.0/0         | tcp         |       443 |     443 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |        53 |      53 |
| sg-0d7391c30d8d641ec | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-09eb1a601306db315 | ingress | 75.22.138.91/32   | icmp        |        -1 |      -1 |
| sg-08c6c727399c99579 | ingress | 0.0.0.0/0         | tcp         |         0 |   65535 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |        88 |      88 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |       636 |     636 |
| sg-08c6c727399c99579 | ingress |                   | tcp         |         0 |   65535 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |       464 |     464 |
| sg-0e2a05296f27460d5 | ingress |                   |          -1 |           |         |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | udp         |       138 |     138 |
| sg-09eb1a601306db315 | egress  | 0.0.0.0/0         |          -1 |           |         |
| sg-0e41f987bee809892 | ingress | 0.0.0.0/0         | tcp         |        22 |      22 |
| sg-0c394901c33d48f60 | ingress | 0.0.0.0/0         | tcp         |       445 |     445 |
| sg-08c6c727399c99579 | ingress | 0.0.0.0/0         |          -1 |           |         |
| sg-08c6c727399c99579 | ingress |                   | icmpv6      |        -1 |      -1 |
+----------------------+---------+-------------------+-------------+-----------+---------+

Join/Filter

Now we have the columns we want, let's join them together, and filter out the info we want with a where clause:

select 
    i.instance_id,
    i.tags ->> 'owner' as owner,
    sg ->> 'GroupId' as sg_ig,
    r.type,
    r.cidr_ip,
    r.ip_protocol,
    r.from_port,
    r.to_port
from  
  aws_ec2_instance as i,
  jsonb_array_elements(security_groups) as sg,
  aws_vpc_security_group_rule as r
where 
  sg ->> 'GroupId' = r.group_id
  and type = 'ingress'
  and cidr_ip = '0.0.0.0/0';
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
|     instance_id     |        owner         |        sg_ig         |  type   |  cidr_ip  | ip_protocol | from_port | to_port |
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
| i-070f3d4a32c073e2d | vandelay_industries  | sg-033addd51410796f8 | ingress | 0.0.0.0/0 | tcp         |        80 |      80 |
| i-02ca68e16ad60c399 | kramerica_industries | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-00e86570e5c19523b | j_peterman           | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-0235270e481292992 | vandelay_industries  | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-00f3c1ab8296b21d5 | vandelay_industries  | sg-017eb739a77f9523c | ingress | 0.0.0.0/0 | tcp         |        22 |      22 |
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+

Great! We've generated a list of all the security group rules that are open to the Internet that are attached to our EC2 instances, along with the name of the owner!

Aggregate

We can take it a step further, and count the number of non-compliant EC2 instances by each owner:

select 
    i.tags ->> 'owner' as owner,
    count(distinct i.instance_id)
from  
  aws_ec2_instance as i,
  jsonb_array_elements(security_groups) as sg,
  aws_vpc_security_group_rule as r
where 
  sg ->> 'GroupId' = r.group_id
  and type = 'ingress'
  and cidr_ip = '0.0.0.0/0'
group by owner
order by count desc;
+----------------------+-------+
|        owner         | count |
+----------------------+-------+
| vandelay_industries  |     3 |
| j_peterman           |     1 |
| kramerica_industries |     1 |
+----------------------+-------+

Answer

We've answered both of our questions!

  1. Which EC2 instances allow traffic from the whole Internet?
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
|     instance_id     |        owner         |        sg_ig         |  type   |  cidr_ip  | ip_protocol | from_port | to_port |
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
| i-070f3d4a32c073e2d | vandelay_industries  | sg-033addd51410796f8 | ingress | 0.0.0.0/0 | tcp         |        80 |      80 |
| i-02ca68e16ad60c399 | kramerica_industries | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-00e86570e5c19523b | j_peterman           | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-0235270e481292992 | vandelay_industries  | sg-029cd86da723916fa | ingress | 0.0.0.0/0 | icmp        |        -1 |      -1 |
| i-00f3c1ab8296b21d5 | vandelay_industries  | sg-017eb739a77f9523c | ingress | 0.0.0.0/0 | tcp         |        22 |      22 |
+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
  1. Who owns these insecure instances?
+----------------------+-------+
|        owner         | count |
+----------------------+-------+
| vandelay_industries  |     3 |
| j_peterman           |     1 |
| kramerica_industries |     1 |
+----------------------+-------+