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 awsInstalling 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:
- Which EC2 instances allow traffic from the whole Internet?
- 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 queryWelcome to Steampipe v0.1.0Type ".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:
selectinstance_id,security_groups,tagsfromaws_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
:
selecti.instance_id,i.tags ->> 'owner' as owner,sg ->> 'GroupId' as sg_idfromaws_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:
selectgroup_id,type,cidr_ip,ip_protocol,from_port,to_portfromaws_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:
selecti.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_portfromaws_ec2_instance as i,jsonb_array_elements(security_groups) as sg,aws_vpc_security_group_rule as rwheresg ->> 'GroupId' = r.group_idand 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:
selecti.tags ->> 'owner' as owner,count(distinct i.instance_id)fromaws_ec2_instance as i,jsonb_array_elements(security_groups) as sg,aws_vpc_security_group_rule as rwheresg ->> 'GroupId' = r.group_idand type = 'ingress'and cidr_ip = '0.0.0.0/0'group by ownerorder by count desc;
+----------------------+-------+| owner | count |+----------------------+-------+| vandelay_industries | 3 || j_peterman | 1 || kramerica_industries | 1 |+----------------------+-------+
Answer
We've answered both of our questions!
- 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 |+---------------------+----------------------+----------------------+---------+-----------+-------------+-----------+---------+
- Who owns these insecure instances?
+----------------------+-------+| owner | count |+----------------------+-------+| vandelay_industries | 3 || j_peterman | 1 || kramerica_industries | 1 |+----------------------+-------+