Migration Immersion Day > Re-Host & Re-Platform > ADS > Explore Data using Athena > Explore ADS data in Athena

Explore ADS data in Athena

The following queries have been created for you to explore some additional information using Athena.

Simply expand the query you want to use and follow these instructions:

To use a predefined query

1. In the Migration Hub navigation pane, choose Servers.

2. Choose the Explore data in Amazon Athena link to be taken to your data in the Athena console.

3. Athena will request you to setup a result location in Amazon S3 before running the first query. Open S3 console. There will be one existing bucket with name starting with aws-application-discovery-service-*. Click on “set up a query result location in Amazon S3”. Use the existing bucket as the Athena bucket.

3. Expand one of the predefined queries listed below and copy it.

4. Place your cursor in Athena’s Query Editor window and paste the query.

5. Choose Run Query.

Run Query

Obtain IP Addresses and Hostnames for Servers

This view helper function retrieves IP addresses and hostnames for a given server. You can use this view in other queries.

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

Identify Servers With or Without Agents

This query can help you perform data validation. If you’ve deployed agents on a number of servers in your network, you can use this query to understand if there are other servers in your network without agents deployed on them. In this query, we look into the inbound and outbound network traffic, and filter the traffic for private IP addresses, only. That is, IP addresses starting with 192, 10, or 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.%'));

Analyze System Performance Data for Servers With Agents

You can use this query to analyze system performance and utilization pattern data for your on-premises servers that have agents installed on them. The query combines the system_performance_agent table with os_info_agent table to identify the hostname for each server. This query returns the time series utilization data (in 15 minute intervals) for all the servers where agents are running.

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;

Creating the IANA Port Registry Import Table

Some of the predefined queries require a table named iana_service_ports_import that contains information downloaded from Internet Assigned Numbers Authority (IANA).

To create the iana_service_ports_import table

Download the IANA port registry database CSV file from Service Name and Transport Protocol Port Number Registry on iana.org.
Upload the CSV file to Amazon S3. Use the bucket that starts with aws-application-discovery-service-
Create a new table in Athena named iana_service_ports_import. In the following example, you need to replace my_bucket_name with the name of the S3 bucket that you uploaded the CSV file to in the previous step.

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

Track Outbound Communication Between Servers Based On Port Number

This query gets the details on the outbound traffic for each service, along with the port number and process details.

To create outbound tracking helper functions

Create the view valid_outbound_ips_helper using the following helper function that lists of all distinct outbound source ip addresses.

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

Create the view outbound_query_helper using the following helper function that determines the frequency of communication for outbound traffic.

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

After you create the iana_service_ports_import table and your two helper functions, you can run the following query to get the details on the outbound traffic for each service, along with the port number and process details.

SELECT DISTINCT
  "hin1"."host_name" "Source Host Name"
, "hin2"."host_name" "Destination Host Name"
, "o"."source_ip" "Source IP Address"
, "o"."destination_ip" "Destination IP Address"
, "o"."frequency" "Connection Frequency"
, "o"."destination_port" "Destination Communication Port"
, "p"."name" "Process Name"
, "ianap"."servicename" "Process Service Name"
, "ianap"."description" "Process Service Description"
FROM
  outbound_query_helper o
, hostname_ip_helper hin1
, hostname_ip_helper hin2
, processes_agent p
, iana_service_ports_import ianap
WHERE ((((("o"."source_ip" = "hin1"."ip_address") AND ("o"."destination_ip" = "hin2"."ip_address")) AND ("p"."agent_assigned_process_id" = "o"."agent_assigned_process_id")) AND ("hin1"."host_name" <> "hin2"."host_name")) AND (("o"."destination_port" = TRY_CAST("ianap"."portnumber" AS integer)) AND ("ianap"."transportprotocol" = 'tcp')))
ORDER BY "hin1"."host_name" ASC, "o"."frequency" DESC;

Track Inbound Communication Between Servers Based On Port Number

This query gets information about inbound traffic for each service, along with the port number and process details.

To create import tracking helper functions

Create the view valid_inbound_ips_helper using the following helper function lists all the distinct inbound source ip addresses.

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

Create the view inbound_query_helper using the following helper function that determines the frequency of communication for inbound traffic.

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

After you create the iana_service_ports_import table and your two helper functions, you can run the following query to get the details on the inbound traffic for each service, along with the port number and process details.

SELECT DISTINCT
  "hin1"."host_name" "Source Host Name"
, "hin2"."host_name" "Destination Host Name"
, "i"."source_ip" "Source IP Address"
, "i"."destination_ip" "Destination IP Address"
, "i"."frequency" "Connection Frequency"
, "i"."destination_port" "Destination Communication Port"
, "p"."name" "Process Name"
, "ianap"."servicename" "Process Service Name"
, "ianap"."description" "Process Service Description"
FROM
  inbound_query_helper i
, hostname_ip_helper hin1
, hostname_ip_helper hin2
, processes_agent p
, iana_service_ports_import ianap
WHERE ((((("i"."source_ip" = "hin1"."ip_address") AND ("i"."destination_ip" = "hin2"."ip_address")) AND ("p"."agent_assigned_process_id" = "i"."agent_assigned_process_id")) AND ("hin1"."host_name" <> "hin2"."host_name")) AND (("i"."destination_port" = TRY_CAST("ianap"."portnumber" AS integer)) AND ("ianap"."transportprotocol" = 'tcp')))
ORDER BY "hin1"."host_name" ASC, "i"."frequency" DESC;

Identify Running Software From Port Number

This query identifies the running software based on port numbers.

To run the query

Before running this query, if you have not already done so, you must create the iana_service_ports_import table that contains the IANA port registry database downloaded from IANA. For information about how to create this table, see Creating the IANA Port Registry Import Table.

Run the following query to identify the running software based on port numbers.

SELECT DISTINCT
  "o"."host_name" "Host Name"
, "ianap"."servicename" "Service"
, "ianap"."description" "Description"
, "con"."destination_port"
, "count"("con"."destination_port") "Destination Port Count"
FROM
  inbound_connection_agent con
, os_info_agent o
, iana_service_ports_import ianap
, network_interface_agent ni
WHERE ((((("con"."destination_ip" = "ni"."ip_address") AND (NOT ("con"."destination_ip" LIKE '172%'))) AND (("con"."destination_port" = "ianap"."portnumber") AND ("ianap"."transportprotocol" = 'tcp'))) AND ("con"."agent_id" = "o"."agent_id")) AND ("o"."agent_id" = "ni"."agent_id"))
GROUP BY "o"."host_name", "ianap"."servicename", "ianap"."description", "con"."destination_port"
ORDER BY "Destination Port Count" DESC;

This is the end of this module