Quando dá problema, ninguém tem tempo de lembrar o nome da coluna do CloudTrail. Esse post é pra salvar nos favoritos. Contas, buckets e IDs são placeholders.
A tabela
Para trilha organizacional (a estrutura de pastas tem o ID da organização). Com partition projection você não precisa rodar MSCK REPAIR nem criar partição na mão.
CREATE EXTERNAL TABLE cloudtrail_logs ( eventversion STRING, useridentity STRUCT< type: STRING, principalid: STRING, arn: STRING, accountid: STRING, invokedby: STRING, accesskeyid: STRING, username: STRING, sessioncontext: STRUCT< attributes: STRUCT<mfaauthenticated: STRING, creationdate: STRING>, sessionissuer: STRUCT<type: STRING, principalid: STRING, arn: STRING, accountid: STRING, username: STRING> > >, eventtime STRING, eventsource STRING, eventname STRING, awsregion STRING, sourceipaddress STRING, useragent STRING, errorcode STRING, errormessage STRING, requestparameters STRING, responseelements STRING, additionaleventdata STRING, requestid STRING, eventid STRING, readonly STRING, resources ARRAY<STRUCT<arn: STRING, accountid: STRING, type: STRING>>, eventtype STRING, apiversion STRING, recipientaccountid STRING, sharedeventid STRING, vpcendpointid STRING ) PARTITIONED BY (account STRING, region STRING, `timestamp` STRING) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://<BUCKET_CLOUDTRAIL>/AWSLogs/<ORG_ID>/' TBLPROPERTIES ( 'projection.enabled' = 'true', 'projection.account.type' = 'enum', 'projection.account.values' = '<ACCOUNT_ID_1>,<ACCOUNT_ID_2>,<ACCOUNT_ID_3>', 'projection.region.type' = 'enum', 'projection.region.values' = 'sa-east-1,us-east-1,us-east-2,us-west-2', 'projection.timestamp.type' = 'date', 'projection.timestamp.format' = 'yyyy/MM/dd', 'projection.timestamp.interval' = '1', 'projection.timestamp.interval.unit' = 'DAYS', 'projection.timestamp.range' = '2025/01/01,NOW', 'storage.location.template' = 's3://<BUCKET_CLOUDTRAIL>/AWSLogs/<ORG_ID>/${account}/CloudTrail/${region}/${timestamp}' ); Três dicas que economizam dinheiro e dor de cabeça:
- Sempre filtre pela partição
"timestamp"(com aspas duplas, porque é palavra reservada). Sem isso o Athena lê o bucket inteiro. - Coloque em
projection.region.valuestodas as regiões, não só as que você usa. Atacante adora região que ninguém olha. Lista completa:aws ec2 describe-regions --all-regions --query 'Regions[].RegionName' --output text. - Trilha de conta única (sem Organization) tem o caminho
AWSLogs/<ACCOUNT_ID>/CloudTrail/.... Ajuste oLOCATIONe o template removendo o nível do org.
Todas as queries abaixo olham os últimos 7 dias. Troque o intervalo conforme a necessidade.
1. Uso da conta root
Root não deveria fazer nada no dia a dia. Qualquer linha aqui merece explicação.
SELECT eventtime, account, awsregion, eventname, sourceipaddress, useragent, errorcode FROM cloudtrail_logs WHERE useridentity.type = 'Root' AND useridentity.invokedby IS NULL AND eventtype <> 'AwsServiceEvent' AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; 2. Login no console sem MFA
SELECT eventtime, account, useridentity.arn, sourceipaddress, json_extract_scalar(responseelements, '$.ConsoleLogin') AS resultado FROM cloudtrail_logs WHERE eventname = 'ConsoleLogin' AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No' AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; Login via IAM Identity Center (SSO) aparece com
MFAUsed = Noporque o MFA acontece no provedor de identidade. Filtre poruseridentity.type = 'IAMUser'se quiser ver só usuários IAM.
3. Rajada de AccessDenied por identidade
Quem está tentando fazer o que não pode? Típico de credencial roubada fazendo reconhecimento.
SELECT useridentity.arn, sourceipaddress, count(*) AS negados, count(DISTINCT eventname) AS acoes_distintas, min(eventtime) AS inicio, max(eventtime) AS fim FROM cloudtrail_logs WHERE errorcode IN ('AccessDenied', 'AccessDeniedException', 'UnauthorizedOperation', 'Client.UnauthorizedOperation') AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') GROUP BY useridentity.arn, sourceipaddress HAVING count(*) > 20 ORDER BY negados DESC; 4. Access keys e usuários criados
SELECT eventtime, account, eventname, useridentity.arn AS quem, json_extract_scalar(requestparameters, '$.userName') AS usuario_alvo, sourceipaddress FROM cloudtrail_logs WHERE eventsource = 'iam.amazonaws.com' AND eventname IN ('CreateUser', 'CreateAccessKey', 'CreateLoginProfile', 'UpdateLoginProfile') AND errorcode IS NULL AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; 5. Alguém tentou cegar você
Desligar trilha, detector do GuardDuty ou gravador do Config é o primeiro passo de um atacante cuidadoso.
SELECT eventtime, account, awsregion, eventsource, eventname, useridentity.arn, sourceipaddress, errorcode FROM cloudtrail_logs WHERE eventname IN ( 'StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors', 'DeleteDetector', 'UpdateDetector', 'DisassociateFromAdministratorAccount', 'StopConfigurationRecorder', 'DeleteConfigurationRecorder', 'DeleteDeliveryChannel', 'DisableSecurityHub', 'DeleteAnalyzer', 'DeleteFlowLogs' ) AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; 6. Bucket S3 ficando público
SELECT eventtime, account, eventname, useridentity.arn, json_extract_scalar(requestparameters, '$.bucketName') AS bucket, sourceipaddress FROM cloudtrail_logs WHERE eventsource = 's3.amazonaws.com' AND eventname IN ('PutBucketPolicy', 'PutBucketAcl', 'DeleteBucketPublicAccessBlock', 'PutBucketPublicAccessBlock', 'DeleteBucketPolicy') AND errorcode IS NULL AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; 7. Security group aberto para a internet
SELECT eventtime, account, awsregion, useridentity.arn, json_extract_scalar(requestparameters, '$.groupId') AS sg, requestparameters FROM cloudtrail_logs WHERE eventname IN ('AuthorizeSecurityGroupIngress', 'ModifySecurityGroupRules') AND (requestparameters LIKE '%0.0.0.0/0%' OR requestparameters LIKE '%::/0%') AND errorcode IS NULL AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; 8. AssumeRole vindo de outra conta
SELECT eventtime, recipientaccountid AS conta_destino, useridentity.accountid AS conta_origem, useridentity.arn AS quem, json_extract_scalar(requestparameters, '$.roleArn') AS role, sourceipaddress FROM cloudtrail_logs WHERE eventsource = 'sts.amazonaws.com' AND eventname = 'AssumeRole' AND useridentity.accountid IS NOT NULL AND useridentity.accountid <> recipientaccountid AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; Cruze o resultado com a lista de contas da sua Organization. Conta que não é sua é alerta vermelho.
9. Instâncias lançadas em região incomum
SELECT eventtime, account, awsregion, useridentity.arn, json_extract_scalar(requestparameters, '$.instanceType') AS tipo, sourceipaddress FROM cloudtrail_logs WHERE eventname = 'RunInstances' AND errorcode IS NULL AND awsregion NOT IN ('sa-east-1', 'us-east-1') AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') ORDER BY eventtime DESC; Troque a lista do NOT IN pelas regiões que você realmente usa. Pegar instância GPU (p*, g*) em região estranha é minerador quase com certeza.
10. IP novo para uma identidade
O CloudTrail não traz geolocalização, mas dá pra achar IP que nunca tinha aparecido para aquela identidade nos 30 dias anteriores:
WITH historico AS ( SELECT DISTINCT useridentity.arn AS arn, sourceipaddress AS ip FROM cloudtrail_logs WHERE "timestamp" BETWEEN date_format(current_date - INTERVAL '37' DAY, '%Y/%m/%d') AND date_format(current_date - INTERVAL '8' DAY, '%Y/%m/%d') AND useridentity.arn IS NOT NULL ), recente AS ( SELECT useridentity.arn AS arn, sourceipaddress AS ip, min(eventtime) AS primeiro, count(*) AS chamadas FROM cloudtrail_logs WHERE "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d') AND useridentity.arn IS NOT NULL AND sourceipaddress NOT LIKE '%.amazonaws.com' GROUP BY useridentity.arn, sourceipaddress ) SELECT r.arn, r.ip, r.primeiro, r.chamadas FROM recente r LEFT JOIN historico h ON r.arn = h.arn AND r.ip = h.ip WHERE h.ip IS NULL ORDER BY r.primeiro DESC; Essa é mais pesada (lê 37 dias). Rode de vez em quando, não todo dia.
Deixando salvas
No Athena, salve cada uma como named query pra todo o time ter acesso:
aws athena create-named-query \ --name "secops-01-uso-root" \ --database default \ --work-group primary \ --query-string file://01-uso-root.sql E se quiser rodar via CLI e já pegar o resultado:
QID=$(aws athena start-query-execution \ --work-group primary \ --query-string file://01-uso-root.sql \ --query QueryExecutionId --output text) aws athena get-query-execution --query-execution-id "$QID" \ --query QueryExecution.Status.State --output text aws athena get-query-results --query-execution-id "$QID" --output table O workgroup precisa ter um local de resultado configurado no S3. Se não tiver, passe
--result-configuration OutputLocation=s3://<BUCKET_RESULTADOS>/.
O próximo passo natural é transformar as queries 1, 5 e 6 em alerta em tempo real com EventBridge, que é o assunto de outro post da série de segurança aqui do blog.