Kueri yang ditentukan sebelumnya Membuat tabel impor registri port IANA

Menggunakan kueri yang telah ditentukan di Amazon Athena

Bagian ini berisi serangkaian kueri yang telah ditetapkan untuk menjalankan kasus penggunaan umum, seperti analisis TCO dan visualisasi jaringan. Anda dapat menggunakan kueri ini sebagaimana adanya atau mengubahnya sesuai kebutuhan Anda.

Untuk menggunakan kueri yang sudah ditetapkan

Di AWS Migration Hub konsol, pilih Server di panel navigasi.
Untuk membuka konsol Amazon Athena, pilih Jelajahi data di Amazon Athena.
Pada halaman Editor Kueri, di panel navigasi di bawah Basis Data, pastikan bahwa application_discovery_service_database dipilih.
Pilih tanda plus (+) pada Editor Kueri untuk membuat tab untuk kueri baru.
Salin salah satu kueri dari Kueri yang ditentukan sebelumnya.
Tempel kueri ke panel kueri pada tab kueri baru yang baru saja Anda buat.
Pilih Jalankan Kueri.

Kueri yang ditentukan sebelumnya

Pilih judul untuk melihat informasi tentang kueri.

Fungsi pembantu tampilan ini mengambil alamat IP dan nama host untuk server tertentu. Anda dapat menggunakan tampilan ini dalam kueri lain. Untuk informasi tentang cara membuat tampilan, lihat BUAT TAMPILAN dalam Panduan Pengguna 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");

Query ini dapat membantu Anda melakukan validasi data. Jika Anda telah men-deploy agen di sejumlah server di jaringan Anda, Anda dapat menggunakan kueri ini untuk mengetahui apakah ada server lain di jaringan Anda tanpa agen yang di-deploy pada server tersebut. Dalam kueri ini, kita melihat lalu lintas jaringan inbound dan outbound, dan memfilter lalu lintas untuk alamat IP privat saja. Yakni, alamat IP yang diawali dengan 192, 10, atau 172.


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.%'));

Anda dapat menggunakan kueri ini untuk menganalisis performa sistem dan data pola penggunaan untuk server on-premise Anda yang memiliki agen terinstal pada server tersebut. Kueri ini menggabungkan tabel system_performance_agent dengan tabel os_info_agent untuk mengidentifikasi nama host untuk setiap server. Kueri ini menghasilkan data penggunaan deret waktu (dengan interval 15 menit) untuk semua server di mana agen berjalan.


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;

Kueri ini mendapatkan detail lalu lintas outbound untuk setiap layanan, beserta nomor port dan detail prosesnya.

Sebelum menjalankan kueri, jika Anda belum melakukannya, Anda harus membuat tabel iana_service_ports_import yang berisi basis data registri port IANA yang diunduh dari IANA. Untuk informasi tentang cara membuat tabel ini, lihat Membuat tabel impor registri port IANA.

Setelah tabel iana_service_ports_import dibuat, buat dua fungsi pembantu tampilan untuk melacak lalu lintas outbound. Untuk informasi tentang cara membuat tampilan, lihat BUAT TAMPILAN dalam Panduan Pengguna Amazon Athena.

Untuk membuat fungsi pembantu pelacakan outbound

Buka konsol Athena di https://console.aws.amazon.com/athena/.
Buat tampilan valid_outbound_ips_helper, menggunakan fungsi pembantu berikut yang mencantumkan semua alamat IP tujuan outbound.
```
CREATE OR REPLACE VIEW valid_outbound_ips_helper AS 
SELECT DISTINCT "destination_ip"
FROM outbound_connection_agent;
```

Buat tampilan outbound_query_helper, menggunakan fungsi pembantu berikut yang menentukan frekuensi komunikasi untuk lalu lintas outbound.


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

Setelah Anda membuat tabel iana_service_ports_import dan kedua fungsi pembantu tersebut, Anda dapat menjalankan kueri berikut untuk mendapatkan detail tentang lalu lintas outbound untuk setiap layanan, beserta nomor port dan detail prosesnya.


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

Kueri ini mendapatkan informasi lalu lintas inbound untuk setiap layanan, beserta nomor port dan detail prosesnya.

Sebelum menjalankan kueri ini, jika Anda belum melakukannya, Anda harus membuat tabel iana_service_ports_import yang berisi basis data registri port IANA yang diunduh dari IANA. Untuk informasi tentang cara membuat tabel ini, lihat Membuat tabel impor registri port IANA.

Setelah tabel iana_service_ports_import dibuat, buat dua fungsi pembantu tampilan untuk melacak lalu lintas inbound. Untuk informasi tentang cara membuat tampilan, lihat BUAT TAMPILAN dalam Panduan Pengguna Amazon Athena.

Untuk membuat fungsi pembantu pelacakan impor

Buka konsol Athena di https://console.aws.amazon.com/athena/.
Buat tampilan valid_inbound_ips_helper, menggunakan fungsi pembantu berikut yang mencantumkan semua alamat IP sumber inbound.
```
CREATE OR REPLACE VIEW valid_inbound_ips_helper AS 
SELECT DISTINCT "source_ip"
FROM inbound_connection_agent;
```

Buat tampilan inbound_query_helper, menggunakan fungsi pembantu berikut yang menentukan frekuensi komunikasi untuk lalu lintas inbound.


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

Setelah Anda membuat tabel iana_service_ports_import dan kedua fungsi pembantu tersebut, Anda dapat menjalankan kueri berikut untuk mendapatkan detail tentang lalu lintas inbound untuk setiap layanan, beserta nomor port dan detail prosesnya.


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

Kueri ini mengidentifikasi perangkat lunak yang berjalan berdasarkan nomor port.

Jalankan kueri berikut untuk mengidentifikasi perangkat lunak yang berjalan berdasarkan nomor port.


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;

Membuat tabel impor registri port IANA

Beberapa kueri yang telah ditetapkan memerlukan tabel bernama iana_service_ports_import berisi informasi yang diunduh dari Internet Assigned Numbers Authority (IANA).

Untuk membuat tabel iana_service_ports_import

Unduh file CSV basis data registri port IANA dari Registri Nama Layanan dan Nomor Port Protokol Transport pada iana.org.
Unggah file ke Amazon S3. Untuk informasi selengkapnya, lihat Bagaimana Cara Mengunggah File dan Folder ke Bucket S3?.

Buat tabel baru di Athena dengan nama iana_service_ports_import. Untuk instruksi, lihat Buat Tabel dalam Panduan Pengguna Amazon Athena. Pada contoh berikut, Anda perlu mengganti my_bucket_name dengan nama bucket S3 tujuan pengunggahan file CSV pada langkah sebelumnya.


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

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Memvisualisasikan data

Menemukan data dengan konsol Migration Hub