aq

在 AWS 上可以采用 SQL 语句查询服务器数量，以及各种实例类型的 EC2 实例分别有多少个。

演示视频：

使用：

Usage:
    aq [options]
    aq [options] <query>

Options:
    --table-cache-ttl=<seconds>  number of seconds to cache the tables
                                 before we update them from AWS again [default: 300]
    -v, --verbose  enable verbose logging

计算每个实例类型有多少个正在运行的实例：

> SELECT instance_type, count(*) count
  FROM ec2_instances
  WHERE state->'Name' = 'running'
  GROUP BY instance_type
  ORDER BY count DESC
+-----------------+---------+
| instance_type   |   count |
|-----------------+---------|
| m4.2xlarge      |      15 |
| m4.xlarge       |       6 |
| r3.8xlarge      |       6 |
+-----------------+---------+

查找具有最大附加 EBS 卷大小的实例：

> SELECT i.id, i.tags->'Name' name, count(v.id) vols, sum(v.size) size, sum(v.iops) iops
  FROM ec2_instances i
  JOIN ec2_volumes v ON v.attachments -> 0 -> 'InstanceId' = i.id
  GROUP BY i.id
  ORDER BY size DESC
  LIMIT 3
+------------+-----------+--------+--------+--------+
| id         | name      |   vols |   size |   iops |
|------------+-----------+--------+--------+--------|
| i-12345678 | foo       |      4 |   2000 |   4500 |
| i-12345679 | bar       |      2 |    332 |   1000 |
| i-12345687 | blah      |      1 |    320 |    960 |
+------------+-----------+--------+--------+--------+

查找允许访问其安全组中的端口 22 的实例：

> SELECT i.id, i.tags->'Name' name, sg.group_name
  FROM ec2_instances i
  JOIN ec2_security_groups sg ON instr(i.security_groups, sg.id)
  WHERE instr(sg.ip_permissions, '"ToPort": 22,')
+------------+-----------+---------------------+
| id         | name      | group_name          |
|------------+-----------+---------------------|
| i-foobar78 | foobar    | launch-wizard-1     |
| i-foobar87 | blah      | launch-wizard-2     |
+------------+-----------+---------------------+