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:
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.
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");
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.%'));
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;
Some of the predefined queries require a table named iana_service_ports_import that contains information downloaded from Internet Assigned Numbers Authority (IANA).
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");
This query gets the details on the outbound traffic for each service, along with the port number and process details.
CREATE OR REPLACE VIEW valid_outbound_ips_helper AS
SELECT DISTINCT "source_ip"
FROM
outbound_connection_agent;
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";
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;
This query gets information about inbound traffic for each service, along with the port number and process details.
CREATE OR REPLACE VIEW valid_inbound_ips_helper AS
SELECT DISTINCT "source_ip"
FROM
inbound_connection_agent;
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";
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;
This query identifies the running software based on port numbers.
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.
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;