在 Amazon Athena 中使用预定义查询

本节包含一组预定义的查询，这些查询可执行典型的使用案例，如 TCO 分析和网络可视化。您可以按原样使用这些查询，或根据自己的需求对其进行修改。

使用预定义的查询

在 AWS Migration Hub 控制台中，选择导航窗格中的服务器。
要打开亚马逊 Athena 控制台，请选择 “在亚马逊 Athena 中浏览数据”。
在 Query Editor (查询编辑器) 页面上，在 Database (数据库) 下的导航窗格中，确保选择了 application_discovery_service_database。
在查询编辑器中选择加号 (+) 以创建新查询的选项卡。
从预定义查询中复制其中一个查询。
将查询粘贴到刚创建的新查询选项卡的查询窗格中。
选择 Run Query (运行查询)。

预定义查询

选择标题以查看有关此查询的信息。

该视图帮助程序函数检索给定服务器的 IP 地址和主机名。您可以在其他查询中使用此视图。有关如何创建视图的信息，请参阅 Amazon Athena 用户指南中的创建视图。


CREATE OR REPLACE VIEW hostname_ip_helper AS 
SELECT DISTINCT
  "os"."host_name"
, "nic"."agent_id"
, "nic"."ip_address"
FROM
  os_info_agent os
, network_interface_agent nic
WHERE ("os"."agent_id" = "nic"."agent_id");

此查询可以帮助您执行数据验证。如果您在网络中的多台服务器上部署了代理，则可以使用此查询来了解在网络中是否存在其他没有部署代理的服务器。在此查询中，我们检查入站和出站网络流量，并且只筛选私有 IP 地址的流量。即以 192、10 或 172 开头的 IP 地址。


SELECT DISTINCT "destination_ip" "IP Address" ,
         (CASE
    WHEN (
    (SELECT "count"(*)
    FROM network_interface_agent
    WHERE ("ip_address" = "destination_ip") ) = 0) THEN
        'no'
        WHEN (
        (SELECT "count"(*)
        FROM network_interface_agent
        WHERE ("ip_address" = "destination_ip") ) > 0) THEN
            'yes' END) "agent_running"
    FROM outbound_connection_agent
WHERE ((("destination_ip" LIKE '192.%')
        OR ("destination_ip" LIKE '10.%'))
        OR ("destination_ip" LIKE '172.%'))
UNION
SELECT DISTINCT "source_ip" "IP ADDRESS" ,
         (CASE
    WHEN (
    (SELECT "count"(*)
    FROM network_interface_agent
    WHERE ("ip_address" = "source_ip") ) = 0) THEN
        'no'
        WHEN (
        (SELECT "count"(*)
        FROM network_interface_agent
        WHERE ("ip_address" = "source_ip") ) > 0) THEN
            'yes' END) "agent_running"
    FROM inbound_connection_agent
WHERE ((("source_ip" LIKE '192.%')
        OR ("source_ip" LIKE '10.%'))
        OR ("source_ip" LIKE '172.%'));

您可以使用此查询来分析安装了代理的本地服务器的系统性能和使用率模式数据。此查询结合 system_performance_agent 表与 os_info_agent 表来标识每个服务器的主机名。此查询为其上正在运行代理的所有服务器返回时间序列利用率数据（以 15 分钟为间隔）。


SELECT "OS"."os_name" "OS Name" ,
    "OS"."os_version" "OS Version" ,
    "OS"."host_name" "Host Name" ,
     "SP"."agent_id" ,
     "SP"."total_num_cores" "Number of Cores" ,
     "SP"."total_num_cpus" "Number of CPU" ,
     "SP"."total_cpu_usage_pct" "CPU Percentage" ,
     "SP"."total_disk_size_in_gb" "Total Storage (GB)" ,
     "SP"."total_disk_free_size_in_gb" "Free Storage (GB)" ,
     ("SP"."total_disk_size_in_gb" - "SP"."total_disk_free_size_in_gb") "Used Storage" ,
     "SP"."total_ram_in_mb" "Total RAM (MB)" ,
     ("SP"."total_ram_in_mb" - "SP"."free_ram_in_mb") "Used RAM (MB)" ,
     "SP"."free_ram_in_mb" "Free RAM (MB)" ,
     "SP"."total_disk_read_ops_per_sec" "Disk Read IOPS" ,
     "SP"."total_disk_bytes_written_per_sec_in_kbps" "Disk Write IOPS" ,
     "SP"."total_network_bytes_read_per_sec_in_kbps" "Network Reads (kbps)" ,
     "SP"."total_network_bytes_written_per_sec_in_kbps" "Network Write (kbps)"
FROM "sys_performance_agent" "SP" , "OS_INFO_agent" "OS"
WHERE ("SP"."agent_id" = "OS"."agent_id") limit 10;

此查询获取每个服务的出站流量的详细信息，以及端口号和进程详细信息。

在运行查询之前，如果尚未执行此操作，则必须创建 iana_service_ports_import 表，其中包含从 IANA 下载的 IANA 端口注册表数据库。有关如何创建此表的信息，请参阅创建 IANA 港口注册表导入表。

创建 iana_service_ports_import 表后，创建两个用于跟踪出站流量的视图帮助程序函数。有关如何创建视图的信息，请参阅 Amazon Athena 用户指南中的创建视图。

创建出站跟踪帮助程序函数

从 https://console.aws.amazon.com/athena/ 打开 Athena 控制台。

使用以下列出所有不同出站目标 IP 地址的辅助函数创建valid_outbound_ips_helper视图。


CREATE OR REPLACE VIEW valid_outbound_ips_helper AS 
SELECT DISTINCT "destination_ip"
FROM outbound_connection_agent;

使用以下帮助程序函数创建 outbound_query_helper 视图，该函数确定出站流量的通信频率。


CREATE OR REPLACE VIEW outbound_query_helper AS
SELECT "agent_id" ,
         "source_ip" ,
         "destination_ip" ,
         "destination_port" ,
         "agent_assigned_process_id" ,
         "count"(*) "frequency"
FROM outbound_connection_agent
WHERE (("ip_version" = 'IPv4')
        AND ("destination_ip" IN 
    (SELECT *
    FROM valid_outbound_ips_helper )))
GROUP BY  "agent_id", "source_ip", "destination_ip", "destination_port", "agent_assigned_process_id";

在创建 iana_service_ports_import 表和两个帮助程序函数之后，您可以运行以下查询来获取每个服务的出站流量的详细信息，以及端口号和进程详细信息。


SELECT hip1.host_name "Source Host Name",
         outbound_connections_results0.source_ip "Source IP Address",
         hip2.host_name "Destination Host Name",
         outbound_connections_results0.destination_ip "Destination IP Address",
         outbound_connections_results0.frequency "Connection Frequency",
         outbound_connections_results0.destination_port "Destination Communication Port",
         outbound_connections_results0.servicename "Process Service Name",
         outbound_connections_results0.description "Process Service Description"
FROM 
    (SELECT DISTINCT o.source_ip,
         o.destination_ip,
         o.frequency,
         o.destination_port,
         ianap.servicename,
         ianap.description
    FROM outbound_query_helper o, iana_service_ports_import ianap
    WHERE o.destination_port = TRY_CAST(ianap.portnumber AS integer)) AS outbound_connections_results0 LEFT OUTER
JOIN hostname_ip_helper hip1
    ON outbound_connections_results0.source_ip = hip1.ip_address LEFT OUTER
JOIN hostname_ip_helper hip2
    ON outbound_connections_results0.destination_ip = hip2.ip_address

此查询获取有关每个服务的入站流量的信息，以及端口号和进程详细信息。

在运行此查询之前，如果尚未执行此操作，则必须创建 iana_service_ports_import 表，其中包含从 IANA 下载的 IANA 端口注册表数据库。有关如何创建此表的信息，请参阅创建 IANA 港口注册表导入表。

创建 iana_service_ports_import 表后，创建两个用于跟踪入站流量的视图帮助程序函数。有关如何创建视图的信息，请参阅 Amazon Athena 用户指南中的创建视图。

创建导入跟踪帮助程序函数

从 https://console.aws.amazon.com/athena/ 打开 Athena 控制台。
使用以下帮助程序函数创建 valid_inbound_ips_helper 视图，该函数列出了所有不同的入站源 IP 地址。
```
CREATE OR REPLACE VIEW valid_inbound_ips_helper AS 
SELECT DISTINCT "source_ip"
FROM inbound_connection_agent;
```

使用以下帮助程序函数创建 inbound_query_helper 视图，该函数确定入站流量的通信频率。


CREATE OR REPLACE VIEW inbound_query_helper AS 
SELECT "agent_id" ,
         "source_ip" ,
         "destination_ip" ,
         "destination_port" ,
         "agent_assigned_process_id" ,
         "count"(*) "frequency"
FROM inbound_connection_agent
WHERE (("ip_version" = 'IPv4')
        AND ("source_ip" IN 
    (SELECT *
    FROM valid_inbound_ips_helper )))
GROUP BY  "agent_id", "source_ip", "destination_ip", "destination_port", "agent_assigned_process_id";

在创建 iana_service_ports_import 表和两个帮助程序函数之后，您可以运行以下查询来获取每个服务的入站流量的详细信息，以及端口号和进程详细信息。


SELECT hip1.host_name "Source Host Name",
         inbound_connections_results0.source_ip "Source IP Address",
         hip2.host_name "Destination Host Name",
         inbound_connections_results0.destination_ip "Destination IP Address",
         inbound_connections_results0.frequency "Connection Frequency",
         inbound_connections_results0.destination_port "Destination Communication Port",
         inbound_connections_results0.servicename "Process Service Name",
         inbound_connections_results0.description "Process Service Description"
FROM 
    (SELECT DISTINCT i.source_ip,
         i.destination_ip,
         i.frequency,
         i.destination_port,
         ianap.servicename,
         ianap.description
    FROM inbound_query_helper i, iana_service_ports_import ianap
    WHERE i.destination_port = TRY_CAST(ianap.portnumber AS integer)) AS inbound_connections_results0 LEFT OUTER
JOIN hostname_ip_helper hip1
    ON inbound_connections_results0.source_ip = hip1.ip_address LEFT OUTER
JOIN hostname_ip_helper hip2
    ON inbound_connections_results0.destination_ip = hip2.ip_address

此查询基于端口号识别正在运行的软件。

运行以下查询，以基于端口号识别正在运行的软件。


SELECT o.host_name "Host Name",
       ianap.servicename "Service",
       ianap.description "Description",
       con.destination_port,
       con.cnt_dest_port "Destination Port Count"
FROM   (SELECT agent_id,
               destination_ip,
               destination_port,
               Count(destination_port) cnt_dest_port
        FROM   inbound_connection_agent
        GROUP  BY agent_id,
                  destination_ip,
                  destination_port) con,
       (SELECT agent_id,
               host_name,
               Max("timestamp")
        FROM   os_info_agent
        GROUP  BY agent_id,
                  host_name) o,
       iana_service_ports_import ianap
WHERE  ianap.transportprotocol = 'tcp'
       AND con.destination_ip NOT LIKE '172%'
       AND con.destination_port = ianap.portnumber
       AND con.agent_id = o.agent_id
ORDER BY cnt_dest_port DESC;

创建 IANA 港口注册表导入表

某些预定义的查询需要一个名为 iana_service_ports_import 的表，该表包含从 Internet 号码分配机构 (IANA) 下载的信息。

创建 iana_service_ports_import 表

从 iana.org 的服务名称和传输协议端口号注册表下载 I AN A 端口注册表数据库 CSV 文件。
将文件上传到 Amazon S3。有关更多信息，请参阅如何将文件和文件夹上传至 S3 存储桶？

在 Athena 中创建一个名为的新表。iana_service_ports_import有关说明，请参阅 Amazon Athen a 用户指南中的创建表。在以下示例中，您需要将 my_bucket_name 替换为在上一步中将 CSV 文件上传到的 S3 存储桶的名称。


CREATE EXTERNAL TABLE IF NOT EXISTS iana_service_ports_import (
         ServiceName STRING,
         PortNumber INT,
         TransportProtocol STRING,
         Description STRING,
         Assignee STRING,
         Contact STRING,
         RegistrationDate STRING,
         ModificationDate STRING,
         Reference STRING,
         ServiceCode STRING,
         UnauthorizedUseReported STRING,
         AssignmentNotes STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'quoteChar' = '"',
  'field.delim' = ','
) LOCATION 's3://my_bucket_name/'
TBLPROPERTIES ('has_encrypted_data'='false',"skip.header.line.count"="1");

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

可视化数据

使用 Migration Hub 控制台发现数据