CloudTrail logs queries

Find all records for a particular AWS Access Key ID (AKID) in CloudTrail

Snowflake

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_since('1 day')
     AND array_contains('ASIAVHOW5LG5FQ4R74ZZ'::variant, p_any_trace_ids)
ORDER BY p_event_time ASC
LIMIT 100

Athena

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_since('1 day')
     AND array_contains('ASIAVHOW5LG5FQ4R74ZZ'::variant, p_any_trace_ids)
ORDER BY p_event_time ASC
LIMIT 100

Find all console "root" signins in CloudTrail

The root account should almost never sign into the AWS console; find all such signins.

Snowflake

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE
  p_occurs_between('2021-01-01', '2021-01-02')
  AND
  eventtype = 'AwsConsoleSignIn'
  AND
  useridentity:arn LIKE '%root%'
ORDER BY p_event_time ASC
LIMIT 100

Find all the sourceIPAddresses for console logins in CloudTrail and rank

This query is similar to the above query, with the IP addresses ranked for all console logins. This helps identify which IP addresses are signing into the console while ranking the relative activity. This can often highlight anomalous behaviors.

Snowflake

SELECT
 sourceipaddress,
 count(1) as total_rows
FROM panther_logs.public.aws_cloudtrail
WHERE
  p_occurs_between('2021-01-01', '2021-01-02')
  AND
  eventtype = 'AwsConsoleSignIn'
GROUP BY sourceipaddress
ORDER BY total_rows DESC
LIMIT 100

Athena

SELECT
 sourceipaddress,
 count(1) as total_rows
FROM panther_logs.aws_cloudtrail
WHERE
  p_occurs_between('2021-01-01', '2021-01-02')
  AND
  eventtype = 'AwsConsoleSignIn'
GROUP BY sourceipaddress
ORDER BY total_rows DESC
LIMIT 100

Show CloudTrail activity related to an AWS instance

During an investigation a particular instance may become the focus. For example, if it is compromised. This query uses the Panther field p_any_aws_instance_ids to easily search over all CloudTrail events for any related activity.

Snowflake

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND array_contains('i-0c4f541ef2f82481c'::variant, p_any_aws_instance_ids)
ORDER BY p_event_time ASC
LIMIT 100

Athena

SELECT
 *
FROM panther_logs.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND contains(p_any_aws_instance_ids, 'i-0c4f541ef2f82481c')
ORDER BY p_event_time ASC
LIMIT 100

Show CloudTrail activity related to an AWS role

Similar to the above query, the Panther field p_any_aws_arns can be used to quickly and easily find all CloudTrail activity related to an ARN of interest (perhaps an ARN of role known to be compromised).

Snowflake

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND array_contains('arn:aws:iam::123456789012:role/SomeRole'::variant, p_any_aws_arns)
ORDER BY p_event_time ASC
LIMIT 100

Athena

SELECT
 *
FROM panther_logs.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND contains(p_any_aws_arns, 'arn:aws:iam::123456789012:role/SomeRole')
ORDER BY p_event_time ASC
LIMIT 100

Show CloudTrail activity related to an AWS account id

This is another variation of using a Panther field to broadly query. In this case finding all CloudTrail data related to an account of interest using p_any_aws_account_ids (perhaps the account is compromised, where the concern is lateral movement).

Snowflake

SELECT
 *
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND array_contains('123456789012'::variant, p_any_aws_account_ids)
ORDER BY p_event_time ASC
LIMIT 100

Athena

SELECT
 *
FROM panther_logs.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND contains(p_any_aws_account_ids, '123456789012')
ORDER BY p_event_time ASC
LIMIT 100

Show all instance launches in CloudTrail

Often when credentials have been breached, there is concern about an actor creating or modifying infrastructure. The below query finds all RunInstances commands. These should be reviewed for anomalous activity. For example, actors have been known to spin-up large numbers of GPU instances for bitcoin mining in compromised accounts.

Snowflake

SELECT
 p_event_time,
 p_any_aws_instance_ids
FROM panther_logs.public.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND eventname = 'RunInstances'
ORDER BY p_event_time ASC
LIMIT 100

Athena

SELECT
 p_event_time,
 p_any_aws_instance_ids
FROM panther_logs.aws_cloudtrail
WHERE p_occurs_between('2021-01-01', '2021-02-01')
     AND eventname = 'RunInstances'
ORDER BY p_event_time ASC
LIMIT 100