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