在 Amazon Athena 中使用預先定義的查詢 - AWS 應用程式探索服務
預先定義的查詢建立 IANA 連接埠登錄檔匯入資料表

AWS Application Discovery Service 不再向新客戶開放。或者，使用 AWS Transform 提供類似的功能。如需詳細資訊，請參閱 AWS Application Discovery Service 可用性變更。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

在 Amazon Athena 中使用預先定義的查詢

本節包含一組預先定義的查詢，此查訊可執行典型使用案例，例如 TCO 分析和網路視覺化。您可以直接使用這些查詢，也可以進行修改以滿足您的需求。

使用預先定義的查詢

  1. 在 AWS Migration Hub 主控台的導覽窗格中，選擇伺服器。

  2. 若要開啟 Amazon Athena 主控台，請選擇在 Amazon Athena 中探索資料。

  3. 在 Query Editor (查詢編輯器) 頁面上，Database (資料庫) 下的導覽窗格中，確定 application_discovery_service_database 為已選取的狀態。

  4. 在查詢編輯器中選擇加號 (+)，以建立具有新查詢的標籤。

  5. 從 預先定義的查詢 複製其中一個查詢。

  6. 將查詢貼到您剛建立新查詢頁籤的查詢窗格中。

  7. 選擇 Run Query (執行查詢)。

預先定義的查詢

選擇標題以查看有關查詢的資訊。

此協助程式函數會擷取指定伺服器的 IP 地址和主機名稱。您可以在其他查詢中使用此視圖。如需有關如何建立檢視的資訊，請參閱《Amazon Athena 使用者指南》中的 CREATE VIEW。

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 下載之 IANA 連接埠登錄資料庫的 iana_service_ports_import 資料表。如需如何建立此角色的資訊，請參閱建立 IANA 連接埠登錄檔匯入資料表。

建立 iana_service_ports_import 資料表之後，建立兩個視圖輔助函數來追蹤傳出流量。如需有關如何建立檢視的資訊，請參閱《Amazon Athena 使用者指南》中的 CREATE VIEW。

建立傳出追蹤協助程式函數

  1. 前往 https://console.aws.amazon.com/athena/ 開啟 Athena 主控台。

  2. 使用以下列出所有不同傳出目的地 IP 地址的協助程式函數建立valid_outbound_ips_helper檢視。

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

  3. 使用下列輔助函數，決定傳出流量的通訊頻率以建立 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";

  4. 現在您已有 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 下載之 IANA 連接埠登錄資料庫的 iana_service_ports_import 資料表。如需如何建立此角色的資訊，請參閱建立 IANA 連接埠登錄檔匯入資料表。

建立 iana_service_ports_import 資料表之後，建立兩個視圖輔助函數函數來追蹤傳入流量。如需有關如何建立檢視的資訊，請參閱《Amazon Athena 使用者指南》中的 CREATE VIEW。

建立匯入追蹤協助程式函數

  1. 前往 https://console.aws.amazon.com/athena/ 開啟 Athena 主控台。

  2. 使用列出所有不同的傳入來源 IP 地址的以下輔助函數建立 valid_inbound_ips_helper 檢視。

    CREATE OR REPLACE VIEW valid_inbound_ips_helper AS 
SELECT DISTINCT "source_ip"
FROM inbound_connection_agent;

  3. 使用以下輔助函數，確定輸入流量的通訊頻率以建立 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";

  4. 現在您已有 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

此查詢會根據連接埠號碼識別執行中的軟體。

在執行此查詢之前，如果您尚未執行此操作，則必須建立包含從 IANA 下載之 IANA 連接埠登錄資料庫的 iana_service_ports_import 資料表。如需如何建立此角色的資訊，請參閱建立 IANA 連接埠登錄檔匯入資料表。

執行以下查詢，可根據連接埠號碼識別執行中的軟體。

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 的資料表，其中包含從網際網路指派號碼授權單位 (IANA) 下載的資訊。

建立 iana_service_ports_import 資料表

  1. 從 https：//iana.org 上的服務名稱和傳輸協定連接埠號碼登錄檔下載 IANA 連接埠登錄資料庫 CSV 檔案。

  2. 將檔案上傳至 Amazon S3。如需詳細資訊，請參閱如何將檔案與資料夾上傳到 S3 儲存貯體？

  3. 在名為 的 Athena 中建立新的資料表iana_service_ports_import。如需說明，請參閱《Amazon Athena 使用者指南》中的建立資料表。在下列範例中，您需要使用在先前步驟中將 CSV 檔案上傳到的 S3 儲存貯體的名稱來取代 my_bucket_name。

    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");