Links

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