Amazon Athena での事前定義されたクエリの使用

このビューヘルパー関数では、特定のサーバーの 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 テーブルが作成されたら、アウトバウンドトラフィックを追跡する 2 つのビューヘルパー関数を作成します。ビューを作成する方法については、Amazon Athena ユーザーガイドの「CREATE VIEW」を参照してください。

このクエリでは、ポート番号とプロセスの詳細と共に、各サービスのインバウンドトラフィックに関する情報が返されます。

このクエリを実行する前に、まだ行っていない場合は、IANA からダウンロードした IANA ポートレジストリデータベースを含む iana_service_ports_import テーブルを作成する必要があります。このテーブルを作成する方法については、「IANA ポートレジストリのインポートテーブルの作成」を参照してください。

iana_service_ports_import テーブルが作成されたら、インバウンドトラフィックを追跡する 2 つのビューヘルパー関数を作成します。ビューを作成する方法については、Amazon Athena ユーザーガイドの「CREATE VIEW」を参照してください。

このクエリでは、ポート番号に基づいて実行中のソフトウェアが識別されます。

このクエリを実行する前に、まだ行っていない場合は、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;