GitHub Enterprise Cloudの監査ログをS3にストリーミングして永続化しAthenaで検索してみた

AWSTemplateFormatVersion: '2010-09-09'
Description: 'CloudFormation template for GitHub Enterprise Audit Log streaming to S3 with OIDC authentication'

Parameters:
  BucketName:
    Type: String
    Description: Name of the S3 bucket to store GitHub Enterprise audit logs
    Default: github-enterprise-audit-logs

  GitHubAccount:
    Type: String
    Default: https://github.com/YOUR-ENTERPRISE-ACCOUNT

  LogRetentionDays:
    Type: Number
    Description: Number of days to retain logs
    Default: 365
    MinValue: 1
    MaxValue: 3653

Resources:
  # OIDC プロバイダーの作成
  GitHubAuditLogOIDCProvider:
    Type: AWS::IAM::OIDCProvider
    Properties:
      Url: https://oidc-configuration.audit-log.githubusercontent.com
      ClientIdList: 
        - sts.amazonaws.com
      ThumbprintList:
        - 6938fd4d98bab03faadb97b34396831e3780aea1

  # S3 バケットの作成
  AuditLogBucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Ref BucketName
      VersioningConfiguration:
        Status: Suspended
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: AES256
            BucketKeyEnabled: true
      LifecycleConfiguration:
        Rules:
          - Id: ExpireOldLogs
            Status: Enabled
            ExpirationInDays: !Ref LogRetentionDays

  # GitHub OIDC 用の IAM ロール
  GitHubAuditLogRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: GitHubEnterpriseAuditLogRole
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Federated: !GetAtt GitHubAuditLogOIDCProvider.Arn
            Action: 'sts:AssumeRoleWithWebIdentity'
            Condition:
              StringEquals:
                "oidc-configuration.audit-log.githubusercontent.com:aud": "sts.amazonaws.com"
                "oidc-configuration.audit-log.githubusercontent.com:sub": !Ref GitHubAccount
      Policies:
        - PolicyName: GitHubAuditLogS3WritePolicy
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Sid: VisualEditor0
                Effect: Allow
                Action:
                  - 's3:PutObject'
                Resource: !Sub 'arn:aws:s3:::${AuditLogBucket}/*'

Outputs:
  AuditLogBucketName:
    Description: Name of the S3 bucket storing GitHub Enterprise audit logs
    Value: !Ref AuditLogBucket

  AuditLogBucketArn:
    Description: ARN of the S3 bucket storing GitHub Enterprise audit logs
    Value: !GetAtt AuditLogBucket.Arn

  GitHubOIDCRoleArn:
    Description: ARN of the IAM role for GitHub OIDC authentication
    Value: !GetAtt GitHubAuditLogRole.Arn

  OIDCProviderArn:
    Description: ARN of the GitHub Audit Log OIDC provider
    Value: !GetAtt GitHubAuditLogOIDCProvider.Arn

$ tree .
.
├── 2025
│   ├── 02
│   ├── 02
│   │   ├── 27
│   │   │   └── 23
│   │   │       ├── 26
│   │   │       │   ├── aaa-bbb-ccc-ddd-eee.json.log.gz
│   │   │       │   └── aaa-bbb-ccc-ddd-eee.json.log.gz
...
└── _check

CREATE EXTERNAL TABLE `default`.`github_audit_logs` (
  `json_objects` string
)
PARTITIONED BY (
  `date` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
LOCATION 's3://YOUR-BUCKET-NAME/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.date.type' = 'date',
  'projection.date.format' = 'yyyy/MM/dd/HH',
  'projection.date.range' = '2025/01/01/01,NOW',
  'projection.date.interval' = '1',
  'projection.date.interval.unit' = 'HOURS',
  'storage.location.template' = 's3://YOUR-BUCKET-NAME/${date}/'
);

CREATE EXTERNAL TABLE github_workflow_logs (
	`@timestamp` BIGINT,
	`_document_id` STRING,
	`action` STRING,
	`actor` STRING,
	`actor_id` BIGINT,
	`actor_is_bot` BOOLEAN,
	`business` STRING,
	`business_id` BIGINT,
	`created_at` BIGINT,
	`event` STRING,
	`hashed_token` STRING,
	`head_branch` STRING,
	`head_sha` STRING,
	`name` STRING,
	`operation_type` STRING,
	`org` STRING,
	`org_id` BIGINT,
	`programmatic_access_type` STRING,
	`public_repo` BOOLEAN,
	`repo` STRING,
	`repo_id` BIGINT,
	`request_access_security_header` STRING,
	`run_number` INT,
	`started_at` STRING,
	`token_id` BIGINT,
	`trigger_id` BIGINT,
	`user_agent` STRING,
	`workflow_id` BIGINT,
	`workflow_run_id` BIGINT,
	`completed_at` STRING,
	`conclusion` STRING,
	`run_attempt` INT,
	`topic` STRING
)
PARTITIONED BY (
  `date` string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
	'ignore.malformed.json' = 'FALSE',
	'dots.in.keys' = 'FALSE',
	'case.insensitive' = 'TRUE',
	'mapping' = 'TRUE'
)
LOCATION 's3://YOUR-BUCKET-NAME/'
TBLPROPERTIES (
	'projection.enabled' = 'true',
	'projection.date.type' = 'date',
	'projection.date.format' = 'yyyy/MM/dd/HH',
	'projection.date.range' = '2025/01/01/01,NOW',
	'projection.date.interval' = '1',
	'projection.date.interval.unit' = 'HOURS',
	'storage.location.template' = 's3://YOUR-BUCKET-NAME//${date}/',
	'classification' = 'json'
);

select json_extract_scalar(json_objects, '$.action') as action,
	count(*) as cnt
from default.github_audit_logs
where date between '2025/03/01' AND '2025/04/01'
group by json_extract_scalar(json_objects, '$.action')
order by cnt desc

action:repo.destroy repo:ORG_NAME/REPO_NAME

select *
from default.github_audit_logs
where date between '2025/03/01' AND '2025/04/01'
	and json_extract_scalar(json_objects, '$.action') = 'repo.destroy'
	and json_extract_scalar(json_objects, '$.repo') = 'ORG_NAME/REPO_NAME'

select json_extract_scalar(json_objects, '$.repo') as repo,
	count(*) as cnt
from default.github_audit_logs
where date between '2025/03/01' AND '2025/03/05'
	and json_extract_scalar(json_objects, '$.action') = 'workflows.completed_workflow_run'
group by json_extract_scalar(json_objects, '$.repo')
order by cnt desc

select repo,
	count(*) as cnt
from github_workflow_logs
where action = 'workflows.completed_workflow_run'
	and date between '2025/03/01' AND '2025/03/05'
group by repo
order by cnt desc