Requêtes prédéfinies Création de la table d'importation du registre des ports IANA

Utilisation de requêtes prédéfinies dans Amazon Athena

Cette section contient un ensemble de requêtes prédéfinies qui exécutent des cas d'utilisation standard, comme l'analyse du coût total de possession et la visualisation du réseau. Vous pouvez utiliser ces requêtes en l'état ou les modifier en fonction de vos besoins.

Pour utiliser une requête prédéfinie

Dans la AWS Migration Hub console, choisissez Servers dans le volet de navigation.
Pour ouvrir la console Amazon Athena, choisissez Explorer les données dans Amazon Athena.
Dans la page Query Editor (Éditeur de requête), dans le volet de navigation sous Database (Base de données), assurez-vous que application_discovery_service_database est sélectionné.
Choisissez le signe plus (+) dans l'éditeur de requête pour créer un onglet pour une nouvelle requête.
Copiez l'une des requêtes à partir de Requêtes prédéfinies.
Collez la requête dans le volet de requête du nouvel onglet de requête que vous venez de créer.
Choisissez Run Query.

Requêtes prédéfinies

Choisissez un titre pour afficher les informations sur la requête.

Cette fonction d’assistant de vue récupère les adresses IP et les noms d'hôte d'un serveur donné. Vous pouvez utiliser cette vue dans d'autres requêtes. Pour plus d'informations sur la création d'une vue, consultez CREATE VIEW dans le guide de l'utilisateur d'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");

Cette requête peut vous aider à effectuer une validation des données. Si vous avez déployé des agents sur un certain nombre de serveurs dans votre réseau, vous pouvez utiliser cette requête pour trouver s'il existe d'autres serveurs dans votre réseau dans lesquels aucun agent n'a été déployé. Dans cette requête, nous allons examiner le trafic réseau entrant et sortant, et filtrer le trafic pour les adresses IP privées uniquement. Autrement dit, les adresses IP commençant par 192, 10, ou 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.%'));

Vous pouvez utiliser cette requête pour analyser les performances système et le modèle d'utilisation des données de vos serveurs sur site dans lesquels des agents ont été installés. La requête associe la table system_performance_agent avec la table os_info_agent pour identifier le nom d'hôte de chaque serveur. Cette requête renvoie les données d'utilisation des séries chronologiques (à intervalles de 15 minutes) pour tous les serveurs dans lesquels des agents sont en cours d'exécution.


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;

Cette requête permet d’obtenir les détails relatifs au trafic sortant pour chaque service, ainsi que le numéro de port et les détails du processus.

Avant d'exécuter la requête, si vous ne l'avez pas déjà fait, vous devez créer la table iana_service_ports_import qui contient la base de données de registre des ports IANA téléchargée depuis IANA. Pour plus d’informations sur la création de cette table, veuillez consulter Création de la table d'importation du registre des ports IANA.

Une fois la table iana_service_ports_import créée, créez deux fonctions d'assistant de vue pour suivre le trafic sortant. Pour plus d'informations sur la création d'une vue, consultez CREATE VIEW dans le guide de l'utilisateur d'Amazon Athena.

Pour créer des fonctions d'assistant de suivi sortant

Ouvrez la console à l'adresse https://console.aws.amazon.com/athena/.
Créez la valid_outbound_ips_helper vue à l'aide de la fonction d'assistance suivante qui répertorie toutes les adresses IP de destination sortantes distinctes.
```
CREATE OR REPLACE VIEW valid_outbound_ips_helper AS 
SELECT DISTINCT "destination_ip"
FROM outbound_connection_agent;
```

Créez la vue outbound_query_helper à l'aide de la fonction d'assistant suivante, qui détermine la fréquence de communication pour le trafic sortant.


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

Après avoir créé la table iana_service_ports_import et vos deux fonctions d'assistant, vous pouvez exécuter la requête suivante pour obtenir les détails relatifs au trafic sortant pour chaque service, ainsi que le numéro de port et les détails de traitement.


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

Cette requête permet d’obtenir des informations sur le trafic entrant pour chaque service, ainsi que le numéro de port et les détails du processus.

Avant d'exécuter cette requête, si vous ne l'avez pas déjà fait, vous devez créer la table iana_service_ports_import qui contient la base de données de registre des ports IANA téléchargée depuis IANA. Pour plus d’informations sur la création de cette table, veuillez consulter Création de la table d'importation du registre des ports IANA.

Une fois la table iana_service_ports_import créée, créez deux fonctions d'assistant de vue pour le suivi du trafic entrant. Pour plus d'informations sur la création d'une vue, consultez CREATE VIEW dans le guide de l'utilisateur d'Amazon Athena.

Pour créer des fonctions d'assistant de suivi d'importation

Ouvrez la console à l'adresse https://console.aws.amazon.com/athena/.
Créez la vue valid_inbound_ips_helper à l'aide de la fonction d'assistant suivante, qui répertorie toutes les adresses IP source entrantes distinctes.
```
CREATE OR REPLACE VIEW valid_inbound_ips_helper AS 
SELECT DISTINCT "source_ip"
FROM inbound_connection_agent;
```

Créez la vue inbound_query_helper à l'aide de la fonction d'assistant suivante, qui détermine la fréquence de communication pour le trafic entrant.


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

Maintenant que vous disposez de la table iana_service_ports_import et de vos deux fonctions d'assistant, vous pouvez exécuter la requête suivante pour obtenir les détails sur le trafic entrant pour chaque service, ainsi que le numéro de port et les détails de traitement.


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

Cette requête identifie le logiciel en cours d'exécution en fonction des numéros de port.

La requête suivante peut être utilisée pour identifier le logiciel en cours d'exécution en fonction des numéros de 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;

Création de la table d'importation du registre des ports IANA

Certaines des requêtes prédéfinies nécessitent une table nommée iana_service_ports_import qui contient des informations téléchargées depuis Internet Assigned Numbers Authority (IANA).

Pour créer la table iana_service_ports_import

Téléchargez le fichier CSV de la base de données de registre de ports IANA à partir de Service Name and Transport Protocol Port Number Registry sur iana.org.
Téléchargez le fichier sur Amazon S3. Pour plus d'informations, consultez Comment charger des fichiers ou dossiers vers un compartiment S3 ?.

Créez une nouvelle table dans Athéna nommée. iana_service_ports_import Pour obtenir des instructions, consultez la section Créer une table dans le guide de l'utilisateur d'Amazon Athena. Dans l'exemple suivant, vous devez remplacer my_bucket_name par le nom du compartiment S3 dans lequel vous avez téléchargé le fichier CSV à l'étape précédente.


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

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Visualisation des données

Découvrir des données avec la console Migration Hub