sql-cloudtrail-snippets.md

CloudTrail Lake Snippets

CloudTrail Lake supports Presto SQL. This doc focuses less on SQL itself and more on snippets specific to CloudTrail Lake schema.

Example Query

This query ties together key concepts and, in one shape or another, is often the starting point for my analyses of events:

SELECT
  eventTime AT TIME ZONE 'US/Pacific' AS eventTime_EST,
  userIdentity.accountid != recipientAccountId AS is_cross_account,
  userIdentity.accountid as sourceAccountId,
  recipientAccountId, 
  awsRegion,
  eventSource,
  eventName,
  errorCode is not null is_error,
  errorCode,
  errorMessage,
  CASE 
    WHEN userIdentity.invokedBy IS NULL AND userIdentity.onbehalfof IS NULL AND useridentity.principalid IS NULL
      THEN userIdentity.invokedby
    WHEN SUBSTR(userIdentity.sessioncontext.sessionissuer.username,1,14) = 'AWSReservedSSO'
      THEN split_part(userIdentity.principalid, ':', 2)
    WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.principalid IS NOT NULL
      THEN userIdentity.invokedBy
    ELSE
      'Not captured by SELECT'
    END AS principal,
  CASE 
    WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.onbehalfof IS NULL AND useridentity.principalid IS NULL
      THEN 'aws'
    WHEN SUBSTR(userIdentity.sessioncontext.sessionissuer.username,1,14) = 'AWSReservedSSO'
      THEN 'sso_user'
    WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.principalid IS NOT NULL
      THEN 'account_service'
    ELSE
      'Not captured by SELECT'
    END AS principal_type,
  CASE 
    WHEN userIdentity.type = 'AssumedRole'
      THEN userIdentity.sessioncontext.sessionissuer.username
    ELSE NULL
      END as assumedRole,
  requestParameters,
  resources,
  additionalEventData, 
  managementEvent,
  readOnly
FROM <YOUR_DATA_STORE/TABLE_NAME>
WHERE eventTime >= CAST('2023-10-17' AS DATE)
  AND DATE_DIFF('minute', now(), eventTime AT TIME ZONE 'UTC') < 30
  AND errorCode IS NOT NULL
  AND awsRegion IN('us-east-1')
  AND eventSource IN ('ec2.amazonaws.com')
  AND recipientAccountId IN ('999999999999')
ORDER BY eventTime DESC
LIMIT 5

Date Filters

Filtering by calendar date isn't bad, but timestamps can be a pain. See below: see:https://stackoverflow.com/questions/50832977/converting-to-timestamp-with-time-zone-failed-on-athena

Note that eventTime are timestamps without timezone, though they are all UTC timestamps:

2023-10-14 23:36:44.000

Filter by date

eventTime >= CAST('2023-10-17' AS DATE)

eventTime BETWEEN CAST('2023-10-17' AS DATE) and now()

eventTime BETWEEN CAST('2023-10-17' AS DATE) and CAST('2023-10-20' AS DATE)

userIdentity

This might not be perfect, so consider including original userIdentity in your query to double-check if things look incomplete or inaccurate:

CASE 
  WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.onbehalfof IS NULL
    THEN userIdentity.invokedby
  WHEN SUBSTR(userIdentity.sessioncontext.sessionissuer.username,1,14) = 'AWSReservedSSO'
    THEN split_part(userIdentity.principalid, ':', 2)
  WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.principalid IS NOT NULL
    THEN userIdentity.invokedBy
  ELSE
    'Not captured by SELECT'
END AS principal,

CASE 
  WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.onbehalfof IS NULL
    THEN 'aws'
  WHEN SUBSTR(userIdentity.sessioncontext.sessionissuer.username,1,14) = 'AWSReservedSSO'
    THEN 'sso_user'
  WHEN userIdentity.invokedBy IS NOT NULL AND userIdentity.principalid IS NOT NULL
    THEN 'account_service'
  ELSE
    'Not captured by SELECT'
END AS principal_type,

CASE 
  WHEN userIdentity.type = 'AssumedRole'
    THEN userIdentity.sessioncontext.sessionissuer.username
  ELSE 
    NULL
END as assumedRole,

Searching errors

Any error

errorCode IS NOT NULL

Error for specific service and action

Filter where eventSource is <service>.amazonaws.com.

To the best of my knowledge, the allowed values of <service> are represented by the same prefixes you can find in the [AWS Service Endpoints documentation]https://docs.aws.amazon.com/general/latest/gr/aws-service-information.html.

errorCode IS NOT NULL 
AND eventSource IN ('ec2.amazonaws.com')
AND eventName IN ('run-instances')

Request parameters

If populated, the requestParameters column can be incredibly useful in troubleshooting why a request is being denied. At the end of the day, IAM is matching strings from request parameters and request context (like identity) against strings in your policy. It's helpful being able to see both :)