aq SQL 查询 AWS 上的资源

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

演示视频：

使用：

Usage:aq[options]aq[options]<query>Options:--table-cache-ttl=<seconds>numberofsecondstocachethetablesbeforeweupdatethemfromAWSagain[default:300]-v,--verboseenableverboselogging

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

> 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     |+------------+-----------+---------------------+