CloudTrail Lake supports Presto SQL. This doc focuses less on SQL itself and more on snippets specific to CloudTrail Lake schema.
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
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
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)
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,
errorCode IS NOT NULL
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')
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 :)