Day 2 - CloudTrail Lake 심화: SQL 감사 분석, Insights 이상 탐지, 크로스 계정 쿼리

aws cloudtrail create-event-data-store \
  --name "org-management-events-7yr" \
  --advanced-event-selectors '[
    {
      "Name": "All Management Events",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Management"]}
      ]
    }
  ]' \
  --retention-period 2555 \
  --multi-region-enabled \
  --organization-enabled \
  --termination-protection-enabled \
  --kms-key-id arn:aws:kms:ap-northeast-2:111122223333:key/mrk-abc123

# S3 민감 버킷의 Data Events만 수집
aws cloudtrail create-event-data-store \
  --name "sensitive-s3-data-events" \
  --advanced-event-selectors '[
    {
      "Name": "S3 PII buckets - Object level",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Data"]},
        {"Field": "resources.type", "Equals": ["AWS::S3::Object"]},
        {"Field": "resources.ARN", "StartsWith": [
          "arn:aws:s3:::pii-data-prod/",
          "arn:aws:s3:::financial-records/"
        ]},
        {"Field": "readOnly", "Equals": ["false"]}
      ]
    }
  ]' \
  --retention-period 365

SELECT <컬럼 목록>
FROM <event-data-store-id>
WHERE <조건>
  AND eventTime > DATE_ADD('day', -N, NOW())
ORDER BY <정렬>
LIMIT <행 수>;

SELECT
  eventTime,
  userIdentity.type,
  sourceIPAddress,
  userAgent,
  additionalEventData
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName = 'ConsoleLogin'
  AND userIdentity.type = 'Root'
  AND eventTime > DATE_ADD('day', -90, NOW())
ORDER BY eventTime DESC;

SELECT
  eventTime,
  userIdentity.arn AS actor,
  eventName,
  requestParameters,
  sourceIPAddress
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName IN (
  'AttachRolePolicy',
  'PutRolePolicy',
  'CreatePolicy',
  'CreatePolicyVersion',
  'SetDefaultPolicyVersion',
  'CreateAccessKey',
  'UpdateAssumeRolePolicy',
  'AddUserToGroup',
  'PutGroupPolicy',
  'AttachGroupPolicy'
)
  AND eventTime > DATE_ADD('day', -7, NOW())
ORDER BY eventTime DESC;

SELECT
  userIdentity.arn,
  requestParameters.bucketName,
  COUNT(*) AS download_count,
  MIN(eventTime) AS first_download,
  MAX(eventTime) AS last_download
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName IN ('GetObject', 'HeadObject')
  AND eventTime > DATE_ADD('hour', -24, NOW())
GROUP BY userIdentity.arn, requestParameters.bucketName
HAVING COUNT(*) > 1000
ORDER BY download_count DESC;

SELECT
  userIdentity.arn AS source_identity,
  requestParameters.roleArn AS assumed_role,
  COUNT(*) AS assume_count,
  COUNT(DISTINCT sourceIPAddress) AS distinct_ips,
  MIN(eventTime) AS first_seen,
  MAX(eventTime) AS last_seen
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName = 'AssumeRole'
  AND errorCode IS NULL
  AND eventTime > DATE_ADD('hour', -1, NOW())
GROUP BY userIdentity.arn, requestParameters.roleArn
HAVING COUNT(*) > 20 OR COUNT(DISTINCT sourceIPAddress) > 3
ORDER BY assume_count DESC;

SELECT
  eventTime,
  eventSource,
  eventName,
  sourceIPAddress,
  userAgent,
  requestParameters,
  responseElements,
  errorCode,
  errorMessage
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE (
  userIdentity.arn LIKE '%:user/alice%'
  OR userIdentity.sessionContext.sessionIssuer.arn LIKE '%:user/alice%'
)
  AND eventTime BETWEEN '2026-05-01T00:00:00Z' AND '2026-05-27T23:59:59Z'
ORDER BY eventTime ASC
LIMIT 500;

# Trail에 Insights 활성화
aws cloudtrail put-insight-selectors \
  --trail-name "my-org-trail" \
  --insight-selectors '[
    {"InsightType": "ApiCallRateInsight"},
    {"InsightType": "ApiErrorRateInsight"}
  ]'
 
# EDS에 직접 Insights 활성화
aws cloudtrail create-event-data-store \
  --name "insights-eds" \
  --advanced-event-selectors '[
    {
      "Name": "Insight Events",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Insight"]}
      ]
    }
  ]' \
  --retention-period 90

// EventBridge Rule for CloudTrail Insights
{
  "source": ["aws.cloudtrail"],
  "detail-type": ["AWS Insight via CloudTrail"],
  "detail": {
    "insightDetails": {
      "insightType": ["ApiCallRateInsight"],
      "insightContext": {
        "requestFrequency": [{
          "average": [{"numeric": [">", 100]}]
        }]
      }
    }
  }
}

# 관리 계정 또는 위임된 CloudTrail 관리자 계정에서 실행
aws cloudtrail create-trail \
  --name "org-master-trail" \
  --s3-bucket-name "org-audit-logs-archive" \
  --is-organization-trail \
  --is-multi-region-trail \
  --enable-log-file-validation \
  --include-global-service-events \
  --cloud-watch-logs-log-group-arn arn:aws:logs:ap-northeast-2:111122223333:log-group:/aws/cloudtrail/org \
  --cloud-watch-logs-role-arn arn:aws:iam::111122223333:role/CloudTrailToCloudWatchLogs
 
aws cloudtrail start-logging --name "org-master-trail"

# CloudTrail 관리를 보안 계정으로 위임
aws organizations enable-aws-service-access \
  --service-principal cloudtrail.amazonaws.com
 
aws cloudtrail register-organization-delegated-admin \
  --member-account-id 999988887777  # 보안(Audit) 계정 ID

-- Organization 전체에서 특정 기간 루트 계정 사용 조회
SELECT
  recipientAccountId AS account_id,
  eventTime,
  userIdentity.type,
  sourceIPAddress,
  userAgent
FROM org-eds-uuid-here
WHERE userIdentity.type = 'Root'
  AND eventTime > DATE_ADD('day', -30, NOW())
ORDER BY eventTime DESC;
 
-- 계정별 IAM 변경 건수 집계 (전사 컴플라이언스 현황)
SELECT
  recipientAccountId AS account_id,
  COUNT(*) AS iam_changes,
  COUNT(DISTINCT userIdentity.arn) AS distinct_actors
FROM org-eds-uuid-here
WHERE eventSource = 'iam.amazonaws.com'
  AND eventName NOT LIKE 'Get%'
  AND eventName NOT LIKE 'List%'
  AND eventName NOT LIKE 'Describe%'
  AND eventTime > DATE_ADD('day', -7, NOW())
GROUP BY recipientAccountId
ORDER BY iam_changes DESC;