タダです.

過去記事で Aurora のログを S3 に格納することを書きました.S3 にログを格納したら検索して分析したくなりますが,この記事では監査ログの中から管理者ユーザーの利用履歴を検索してみます.

前提
テーブル作成
- 関連情報
- 管理者ユーザーの利用を抽出
まとめ

前提

この記事では,Athena で検索する Aurora のログは S3 で Aurora のクラスター名/各インスタンス名/YYYY-MM-DD/audit/ というパスで格納したとします.また,ログは Aurora MySQL 5.7 の環境のものになります.

テーブル作成

今回用のテーブルを以下のように作ります.監査ログはドキュメントにも記載がありますが,UTF-8 形式のカンマ区切り変数 (CSV) ファイルになっており,フィールドもドキュメントに記載のものを使っています.また,S3 のパスで Aurora DB インスタンスと日付を使っているため,それぞれパーティション化しています.

CREATE EXTERNAL TABLE `テーブル名` (
    `timestamp` string,
    `serverhost` string,
    `username` string,
    `host` string,
    `connectionid` string,
    `queryid` string,
    `operation` string,
    `database` string,
    `object` string,
    `retcode` string
)
PARTITIONED BY (`date_day` string, `db_identifier` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'escapeChar' = '\\',
    'quoteChar' = '`',
    'separatorChar' = ','
)
LOCATION 's3://[Aurora クラスター名]/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.db_identifier.values'='[WRITERインスタンス名],[READERインスタンス名]', 
    'projection.db_identifier.type'='enum',
    'projection.date_day.format' = 'yyyy-MM-dd',
    'projection.date_day.range' = '2020-01-01,NOW',
    'projection.date_day.type' = 'date',
    'storage.location.template' = 's3://[Aurora クラスター名]/${db_identifier}/${date_day}/audit/'
);

管理者ユーザーの利用を抽出

準備が整ったので,Aurora の管理者ユーザーの利用状況を以下のクエリで検索してみましょう.画像のように抽出することができました.

SELECT *
FROM [テーブル名]
where db_identifier between 'WRITER インスタンス名' and 'READER インスタンス名'
    and username = '管理者ユーザー名'
    and d = 'スキャンの日付'

実行結果抜粋

まとめ

Athena を使って S3 に格納した Aurora の監査ログから特定の条件を検索するクエリと抽出結果をまとめてみました.一旦,これでクエリは叩けますがいちいち Athane のクエリを叩くのは煩雑なため,仕組み化していく過程を次の記事で紹介します.

継続は力なり

タイトル通り定期的な更新を心掛けるブログです。

S3 に入れた Aurora の監査ログを Athena でクエリを行う

前提

テーブル作成

関連情報

管理者ユーザーの利用を抽出

まとめ