AWS WAFログの解析 Athena

AWS

事前準備

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

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

「Settings」をクリックします

バケットを指定し、

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

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

データベース作成

create database waflog;

1	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 {テーブル名}

(

`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/';

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;

1	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;

1	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;

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

爆速レンタルサーバならConoHa WING

サーバはプロに全部お任せ！「仕事」に専念したいあなたにおすすめです。

ConoHa VPSで運営してま🐱

サーバの勉強がしたいあなたにおすすめ！現役エンジニアの管理人が選んだ、WordPress運用に適したVPSの終着点

ConoHa VPSで爆速WordPressを作ろう！

Laravel, AWS ポートフォリオ作成サポート！定員3名

https://menta.work/plan/2381

Laravel, AWSによるポートフォリオ作成を通して、エンジニア転職をサポートします

優技録

AWS WAFログの解析 Athena

事前準備

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

テーブルの作成

爆速レンタルサーバならConoHa WING

ConoHa VPSで運営してま🐱

Laravel, AWS ポートフォリオ作成サポート！定員3名

関連記事 - More from my site -

コメントを残すコメントをキャンセル

事前準備

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

テーブルの作成

爆速レンタルサーバならConoHa WING

ConoHa VPSで運営してま🐱

Laravel, AWS ポートフォリオ作成サポート！ 定員3名

関連記事 - More from my site -

コメントを残す コメントをキャンセル

Laravel, AWS ポートフォリオ作成サポート！定員3名

コメントを残すコメントをキャンセル