VPC logs queries

This page contains examples for VPC Flow logs and VPC DNS logs.
VPC Flow Logs queries
Show VPC Flowlog activity for SSH and RDP
Remote shells typically have a human at one end. During an investigation, isolating sessions from SSH and RDP is often a standard procedure to identify specific actor activity.
Snowflake
Athena
SELECT
 *
FROM panther_logs.public.aws_vpcflow
WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
 AND
 (srcport IN (22, 3389) OR dstport IN (22, 3389))
ORDER BY p_event_time ASC
LIMIT 100
SELECT
 *
FROM panther_logs.aws_vpcflow
WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
 AND
 (srcport IN (22, 3389) OR dstport IN (22, 3389))
ORDER BY p_event_time ASC
LIMIT 100
Show VPC Flowlog activity for an IP address
During an investigation often particular IP addresses are identified as being of interest (e.g, a known command and control node). Once the role of an IP address is identified, isolating and explaining that activity is of interest. This can indicate which resources are likely to be compromised.
Snowflake
Athena
SELECT
 *
FROM panther_logs.public.aws_vpcflow
WHERE p_occurs_between('2021-01-01', '2021-01-02')
 AND array_contains('1.2.3.4'::variant, p_any_ip_addresses)
ORDER BY p_event_time ASC
LIMIT 100
SELECT
 *
FROM panther_logs.aws_vpcflow
WHERE p_occurs_between('2021-01-01', '2021-01-02')
 AND contains(p_any_ip_addresses, '1.2.3.4')
ORDER BY p_event_time ASC
LIMIT 100
Show VPC Flowlog Activity Related to CloudTrail sourceIPAddresses Doing Console Signins
If there are concerns of a credential breach, then accounting for all AWS console activity is of critical importance. This query will find all the CloudTrail sourceIPaddresses involved in console signins and then return all the VPC Flow activity related. This will show if there are common IP addresses. Of particular interest are IP addresses outside of your organization communicating with the instances as well as logging into the console. This may indicate a compromise where an unauthorized actor is accessing account resources.
Snowflake
Athena
WITH cloudTrailIPs as
(SELECT
 DISTINCT sourceIPAddress AS ip
 FROM panther_logs.public.aws_cloudtrail
 WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
 AND
 eventtype = 'AwsConsoleSignIn'
)
SELECT
 *
FROM cloudTrailIPs ips JOIN panther_logs.public.aws_vpcflow flow ON (ips.ip = flow.srcaddr OR ips.ip = flow.dstaddr)
WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
ORDER BY p_event_time ASC
LIMIT 100
WITH cloudTrailIPs as
(SELECT
 DISTINCT sourceIPAddress AS ip
 FROM panther_logs.aws_cloudtrail
 WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
 AND
 eventtype = 'AwsConsoleSignIn'
)
SELECT
 *
FROM cloudTrailIPs ips JOIN panther_logs.aws_vpcflow flow ON (ips.ip = flow.srcaddr OR ips.ip = flow.dstaddr)
WHERE
 p_occurs_between('2021-01-01', '2021-01-02')
ORDER BY p_event_time ASC
LIMIT 100
​
VPC DNS query examples
The queries below are for VPC DNS logs, but could be adapted for other DNS logs.
Sources with most queries in past 1 week 
SELECT 
 srcids:instance, COUNT(*) 
FROM 
 panther_logs.public.aws_vpcdns 
WHERE 
 p_occurs_since(1w) 
GROUP BY 1 
LIMIT 100
Rare queries in past 4 weeks
-- Rare queries in past 4 weeks
SELECT 
 query_name, COUNT(*) as total
FROM 
 panther_logs.public.aws_vpcdns
WHERE 
 p_occurs_since(4w)
GROUP BY 1
ORDER BY 2
LIMIT 100
List queries, remove AWS internal queries
-- List of queries
SELECT
 p_event_time, srcids, query_name, query_type, rcode
FROM
 panther_logs.public.aws_vpcdns
WHERE 
 -- Remove aws internal queries
 not query_name LIKE ANY ('%amazonaws.com.', '%.compute.internal.')
LIMIT 10
​
Split and list the top 10 TLDs
-- Count the top 10 top-level domains
SELECT
 split_part(query_name, '.', -2) as TLD, count(*) as total
FROM
 panther_logs.public.aws_vpcdns
GROUP by 1
ORDER BY 2 desc
LIMIT 10

S3 Access logs queries

Visualization and Dashboards

Last modified 11mo ago