事前準備

• WAF Charm設定済み
  
• 検索結果を格納するquerry-resultsを作成

クエリ結果を格納するバケットの設定

「Settings」をクリックします

バケットを指定し、

「Autocomplete」にチェックを入れて、

「Save」をクリックします。

データベース作成

create database waflog;

テーブルの作成

CREATE EXTERNAL TABLE IF NOT EXISTS {テーブル名}
(
`timestamp` bigint,
formatVersion int,
webaclId string,
terminatingRuleId string,
terminatingRuleType string,
action string,
terminatingRuleMatchDetails array < struct <
    conditionType: string,
    location: string,
    matchedData: array < string >
> >,
httpSourceName string,
httpSourceId string,
ruleGroupList array < struct <
    ruleGroupId: string,
    terminatingRule: struct < ruleId: string, action: string >,
    nonTerminatingMatchingRules: array < struct < action: string, ruleId: string > >,
    excludedRules: array < struct < exclusionType: string, ruleId: string > >
> >,
rateBasedRuleList array < struct < rateBasedRuleId: string, limitKey: string, maxRateAllowed: int > >,
nonTerminatingMatchingRules array < struct < action: string, ruleId: string > >,
httpRequest struct <
    clientIp: string,
    country: string,
    headers: array < struct < name: string, value: string > >,
    uri: string,
    args: string,
    httpVersion: string,
    httpMethod: string,
    requestId: string
>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://{バケットとフォルダの指定}';

実際のコード

CREATE EXTERNAL TABLE IF NOT EXISTS waflog_2020_11
(
`timestamp` bigint,
formatVersion int,
webaclId string,
terminatingRuleId string,
terminatingRuleType string,
action string,
terminatingRuleMatchDetails array < struct <
    conditionType: string,
    location: string,
    matchedData: array < string >
> >,
httpSourceName string,
httpSourceId string,
ruleGroupList array < struct <
    ruleGroupId: string,
    terminatingRule: struct < ruleId: string, action: string >,
    nonTerminatingMatchingRules: array < struct < action: string, ruleId: string > >,
    excludedRules: array < struct < exclusionType: string, ruleId: string > >
> >,
rateBasedRuleList array < struct < rateBasedRuleId: string, limitKey: string, maxRateAllowed: int > >,
nonTerminatingMatchingRules array < struct < action: string, ruleId: string > >,
httpRequest struct <
    clientIp: string,
    country: string,
    headers: array < struct < name: string, value: string > >,
    uri: string,
    args: string,
    httpVersion: string,
    httpMethod: string,
    requestId: string
>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://wafcharm-example/waflog/2020/11/';

• テーブル名：waflog_2020_11

• S3パス：s3://wafcharm-example/waflog/2020/11/

検索範囲のデータ量の従量課金なので、絞ってテーブルを作成しています。

action = ALLOWで検索

SELECT from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, * FROM waflog_2020_11 WHERE action = 'ALLOW' LIMIT 10;

action = BLOCK

SELECT from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, * FROM waflog_2020_11 WHERE action = 'BLOCK' LIMIT 10;

クライアントIP 152.xxx.xxx.226で検索

SELECT from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, * FROM waflog_2020_11 WHERE httprequest.clientip = '152.xxx.xxx.226' LIMIT 10;