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

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

Athena

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

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

Athena

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

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

Athena

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