Consultas predefinidas Creación de la tabla de importación del registro de puertos de la IANA

Uso de consultas predefinidas en Amazon Athena

Esta sección contiene un conjunto de consultas predefinidas que realizan casos de uso típicos, como análisis de TCO y visualización de red. Puede utilizar estas consultas tal cual están o modificarlas para adaptarlas a sus necesidades.

Para utilizar una consulta predefinida

En la AWS Migration Hub consola, elija Servidores en el panel de navegación.
Para abrir la consola de Amazon Athena, elija Explorar datos en Amazon Athena.
En la página Editor de consulta, en el panel de navegación en Base de datos, asegúrese de que application_discovery_service_database está seleccionado.
Elija el signo más (+) en el editor de consultas para crear una pestaña para una nueva consulta.
Copie una de las consultas de Consultas predefinidas.
Pegue la consulta en el panel de consultas de la nueva pestaña de consultas que acaba de crear.
Elija Run Query (Ejecutar consulta).

Consultas predefinidas

Elija un título para ver información sobre la consulta.

Esta función auxiliar de vista recupera las direcciones IP y los nombres de host de un servidor determinado. Puede utilizar esta función de vista en otras consultas. Para obtener información sobre cómo crear una vista, consulte CREATE VIEW en la Guía del usuario de 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");

Esta consulta puede ayudarle a realizar la validación de datos. Si ha implementado agentes en varios servidores de la red, puede utilizar esta consulta para saber si hay otros servidores en la red sin agentes implementados en ellos. En esta consulta, examinamos el tráfico de red entrante y saliente, y filtramos el tráfico solo para direcciones IP privadas. Es decir, las direcciones IP que comienzan por 192, 10 o 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.%'));

Puede utilizar esta consulta para analizar el rendimiento del sistema y los datos de patrón de uso de los servidores locales que tienen agentes instalados. La consulta combina la tabla system_performance_agent con la tabla os_info_agent para identificar el nombre de host de cada servidor. Esta consulta devuelve los datos de uso de series temporales (en intervalos de 15 minutos) de todos los servidores en los que se ejecutan los agentes.


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;

Esta consulta obtiene los detalles del tráfico saliente de cada servicio, junto con el número de puerto y los detalles del proceso.

Antes de ejecutar la consulta, si aún no lo ha hecho, debe crear la tabla iana_service_ports_import que contiene la base de datos de registro de puertos IANA descargada de IANA. Para obtener información sobre cómo crear esta tabla, consulte Creación de la tabla de importación del registro de puertos de la IANA.

Después de crear la tabla iana_service_ports_import, cree dos funciones auxiliares de vista para realizar un seguimiento del tráfico saliente. Para obtener información sobre cómo crear una vista, consulte CREATE VIEW en la Guía del usuario de Amazon Athena.

Para crear funciones de ayuda de seguimiento de salida

Abra la consola Athena en https://console.aws.amazon.com/athena/.
Cree la valid_outbound_ips_helper vista mediante la siguiente función auxiliar, que muestra todas las direcciones IP de destino salientes distintas.
```
CREATE OR REPLACE VIEW valid_outbound_ips_helper AS 
SELECT DISTINCT "destination_ip"
FROM outbound_connection_agent;
```

Cree la vista outbound_query_helper, mediante la siguiente función auxiliar que determina la frecuencia de comunicación para el tráfico saliente.


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

Después de crear la tabla iana_service_ports_import y sus dos funciones auxiliares, puede ejecutar la siguiente consulta para obtener los detalles sobre el tráfico saliente de cada servicio, junto con el número de puerto y los detalles del proceso.


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

Esta consulta obtiene información sobre el tráfico entrante de cada servicio, junto con el número de puerto y los detalles del proceso.

Antes de ejecutar esta consulta, si aún no lo ha hecho, debe crear la tabla iana_service_ports_import que contiene la base de datos de registro de puertos IANA descargada de IANA. Para obtener información sobre cómo crear esta tabla, consulte Creación de la tabla de importación del registro de puertos de la IANA.

Después de crear la tabla iana_service_ports_import, cree dos funciones auxiliares de vista para realizar un seguimiento del tráfico entrante. Para obtener información sobre cómo crear una vista, consulte CREATE VIEW en la Guía del usuario de Amazon Athena.

Para crear funciones de ayuda de seguimiento de la importación

Abra la consola Athena en https://console.aws.amazon.com/athena/.
Cree la vista valid_inbound_ips_helper, utilizando la siguiente función auxiliar que muestra todas las direcciones IP de origen entrantes distintas.
```
CREATE OR REPLACE VIEW valid_inbound_ips_helper AS 
SELECT DISTINCT "source_ip"
FROM inbound_connection_agent;
```

Cree la vista inbound_query_helper, utilizando la siguiente función auxiliar que determina la frecuencia de comunicación para el tráfico entrante.


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

Después de crear la tabla iana_service_ports_import y sus dos funciones auxiliares, puede ejecutar la siguiente consulta para obtener los detalles del tráfico entrante de cada servicio, junto con el número de puerto y los detalles del proceso.


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

Esta consulta identifica el software en ejecución en función de los números de puerto.

Ejecute la siguiente consulta para identificar el software en ejecución en función de los números de puerto.


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;

Creación de la tabla de importación del registro de puertos de la IANA

Algunas de las consultas predefinidas requieren una tabla denominada iana_service_ports_import que contenga información descargada de IANA (Internet Assigned Numbers Authority).

Para crear la tabla iana_service_ports_import

Descargue el archivo CSV de la base de datos de registro de puertos IANA desde el Registro de número de puerto de nombre de servicio y protocolo de transporte en iana.org.
Cargue el archivo en Amazon S3. Para obtener más información, consulte ¿Cómo puedo cargar archivos y carpetas en un bucket de S3?

Crea una nueva tabla en Athena llamada. iana_service_ports_import Para obtener instrucciones, consulte Crear una tabla en la Guía del usuario de Amazon Athena. En el ejemplo siguiente, debe reemplazar my_bucket_name por el nombre del bucket de S3 en el que cargó el archivo CSV en el paso anterior.


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

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Visualización de datos

Descubrimiento de datos con la consola de Migration Hub