redashで攻撃を可視化する

/var/log/secureをtd-agentを利用してS3とredshiftに送信。

そこからredashとredshift連携させて、redash側でクエリを書いてあげればグラフが作成できるので可視化できる。

ちなみにredshiftのDBテーブルは以下の通り。

Table "public.secure_log"
 Column  |          Type          |                Modifiers
---------+------------------------+-----------------------------------------
 time    | character varying(255) | not null default '-'::character varying
 host    | character varying(255) | not null default '-'::character varying
 ident   | character varying(255) | not null default '-'::character varying
 pid     | character varying(255) | not null default '-'::character varying
 message | character varying(255) | not null default '-'::character varying

このテーブル構成はtd-agentのformat syslogなので、td-agentの公式ドキュメントを読めば理解できる。

fluentd document

クエリを書く

さて、redash側にてクエリを書いて「ログインを試行したユーザ」「ログイン失敗したIP」などを可視化したい。

けれど、messageカラムの中に値が何個も入ってしまっていて単純にクエリを書くだけだとCOUNTできない状態だった。（同僚のプログラマによくないDB設計と言われてしまったｗ）

なのでお手伝いしてもらいつつIPだけ抜き出すクエリを作成。

※↓「Failed password」したIPだけ抜き出してくれるクエリ。（まじたすかりました）

select split_part(message, ' ', 6) from secure_log where message like 'Failed password%'

ということで、redashのここに、アレンジしたクエリを書いていく。

f:id:vtryo:20180714153332j:plain

さきほどのクエリと組み合わせて、ログイン失敗したユーザをCOUNTするクエリを作成（トップ10表示）

※split_partはredshiftで用意されている関数

SELECT split_part(message, ' ', 9),COUNT(*) AS COUNT FROM secure_log where message like 'pam_unix(sshd:auth): authentication failure;%' GROUP BY message ORDER BY COUNT DESC limit 10;

さらにログインを試行したユーザトップ10表示

SELECT split_part(message, ' ', 4),COUNT(*) AS COUNT FROM secure_log where message LIKE 'input_userauth_request: invalid user%' GROUP BY message ORDER BY COUNT desc limit 10;