CDKでAthenaを使用したWAFログ分析環境を実装する方法

AthenaResultBucket/
└── {クエリ結果ファイル}

export class AthenaWafAnalysisCdkStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    // 既存のWAFログバケットを参照
    const wafLogBucket = s3.Bucket.fromBucketName(this, "WafLogBucket", "既存のWAFログバケット名をここに入力");

    // Athenaのクエリ結果を保存するS3バケットを作成
    const athenaResultBucket = new s3.Bucket(this, "AthenaResultBucket", {
      removalPolicy: cdk.RemovalPolicy.DESTROY,
      autoDeleteObjects: true,
      blockPublicAccess: s3.BlockPublicAccess.BLOCK_ALL,
      encryption: s3.BucketEncryption.S3_MANAGED,
    });

    // 以下、Athena関連リソースの実装...
  }
}

// AWS Glueデータベースを作成
const glueDatabase = new glue.CfnDatabase(this, "WafLogDatabase", {
  catalogId: this.account,
  databaseInput: {
    name: "waf_logs_db",
    description: "Database for WAF logs",
  },
});

// AWS Glueテーブルを作成
const glueTable = new glue.CfnTable(this, "WafLogTable", {
  catalogId: this.account,
  databaseName: glueDatabase.ref,
  tableInput: {
    name: "waf_logs",
    tableType: "EXTERNAL_TABLE",
    parameters: {
      classification: "json",
      typeOfData: "file",
    },
    storageDescriptor: {
      location: `s3://${wafLogBucket.bucketName}/waf-logs/`,
      inputFormat: "org.apache.hadoop.mapred.TextInputFormat",
      outputFormat: "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
      serdeInfo: {
        serializationLibrary: "org.openx.data.jsonserde.JsonSerDe",
        parameters: {
          paths:
            "action,httpRequest,nonTerminatingMatchingRules,rateBasedRuleList,ruleGroupList,terminatingRuleId,terminatingRuleType",
        },
      },
      columns: [
        { name: "timestamp", type: "timestamp" },
        { name: "formatversion", type: "int" },
        { name: "webaclid", type: "string" },
        { name: "terminatingruleid", type: "string" },
        { name: "terminatingruletype", type: "string" },
        { name: "action", type: "string" },
        { name: "httpsourcename", type: "string" },
        { name: "httpsourceid", type: "string" },
        { name: "rulegrouplist", type: "array<string>" },
        { name: "ratebasedrulelist", type: "array<string>" },
        { name: "nonterminatingmatchingrules", type: "array<string>" },
        {
          name: "httprequest",
          type: "struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string>",
        },
      ],
    },
  },
});

type: "struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string>";

// Athena Workgroupを作成
const athenaWorkgroup = new athena.CfnWorkGroup(this, "WafAnalysisWorkGroup", {
  name: "waf-analysis-workgroup",
  recursiveDeleteOption: true,
  state: "ENABLED",
  workGroupConfiguration: {
    resultConfiguration: {
      outputLocation: `s3://${athenaResultBucket.bucketName}/`,
    },
    publishCloudWatchMetricsEnabled: true,
    enforceWorkGroupConfiguration: true,
  },
});

// Athenaクエリを実行するためのIAMロールを作成
const athenaQueryRole = new iam.Role(this, "AthenaQueryRole", {
  assumedBy: new iam.ServicePrincipal("athena.amazonaws.com"),
});

// 権限を追加
athenaQueryRole.addManagedPolicy(iam.ManagedPolicy.fromAwsManagedPolicyName("AmazonAthenaFullAccess"));
wafLogBucket.grantRead(athenaQueryRole);
athenaResultBucket.grantReadWrite(athenaQueryRole);

// リソース情報を出力
new cdk.CfnOutput(this, "AthenaWorkgroupName", {
  value: athenaWorkgroup.name,
  description: "The name of the Athena workgroup for querying WAF logs",
});

new cdk.CfnOutput(this, "GlueDatabaseName", {
  value: glueDatabase.ref,
  description: "The name of the Glue database for WAF logs",
});

new cdk.CfnOutput(this, "GlueTableName", {
  value: glueTable.ref,
  description: "The name of the Glue table for WAF logs",
});

new cdk.CfnOutput(this, "AthenaResultBucketName", {
  value: athenaResultBucket.bucketName,
  description: "The name of the S3 bucket storing Athena query results",
});

SELECT
  DATE(from_unixtime(timestamp/1000)) as day,
  count(*) as request_count
FROM
  waf_logs_db.waf_logs
WHERE
  action = 'BLOCK'
GROUP BY
  DATE(from_unixtime(timestamp/1000))
ORDER BY
  day DESC;

SELECT
  httprequest.country,
  count(*) as blocked_requests
FROM
  waf_logs_db.waf_logs
WHERE
  action = 'BLOCK'
GROUP BY
  httprequest.country
ORDER BY
  blocked_requests DESC
LIMIT 10;

SELECT
  terminatingruleid,
  count(*) as rule_blocks
FROM
  waf_logs_db.waf_logs
WHERE
  action = 'BLOCK'
GROUP BY
  terminatingruleid
ORDER BY
  rule_blocks DESC;

SELECT
  httprequest.clientip,
  count(*) as request_count
FROM
  waf_logs_db.waf_logs
GROUP BY
  httprequest.clientip
ORDER BY
  request_count DESC
LIMIT 20;

SELECT
  HOUR(from_unixtime(timestamp/1000)) as hour_of_day,
  count(*) as blocked_count
FROM
  waf_logs_db.waf_logs
WHERE
  action = 'BLOCK'
GROUP BY
  HOUR(from_unixtime(timestamp/1000))
ORDER BY
  hour_of_day;

SELECT
  httprequest.uri,
  count(*) as access_count
FROM
  waf_logs_db.waf_logs
GROUP BY
  httprequest.uri
ORDER BY
  access_count DESC
LIMIT 20;