VTRyo Blog

一歩ずつ前に進むブログ

/var/log/secureを解析するクエリ

f:id:vtryo:20180714152616p:plain

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設計と言われてしまったw)

なのでお手伝いしてもらいつつ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;

グラフを作成

あとはredashのグラフを作成する。

クエリを実行したページを下にスクロールしていくと「+ VISUALIZATION」があるのでそこで作成

f:id:vtryo:20180714154038j:plain

 

これを見る限り、adminで適当にパスワードを設定している激弱サーバは簡単にログインされてしまうなあと。

ちなみにSSHのポートをデフォルトから変更している場合は、設定しているポート番号でアクセスしに来たときにはじめて「pam_unix(sshd:auth): authentication failure;」がログに残るらしい。

なので標準ではログに「pam_unix(sshd:auth): authentication failure;」が残ってない場合があるので注意。

 

redashのダッシュボードにグラフを追加してあげれば出来上がり。

f:id:vtryo:20180714154113j:plain

余談

「DBのカラムに対して値はひとつにすべき」という指摘を頂いて、syslogのフォーマット自体を変えてログをredshiftに流そうと試みた。

正規表現をチェックするサイトがあるので、そこで泥臭く検証(スペース区切ったりとか)で頑張るも、あまり生産性があるとはいえなかったので、結局messageはそのままにしてクエリでうまいことやることにした。

syslog自体のLogFormatを変更する方法もなくはないようだったが(syslog.conf)、可視化のためだけに変更するリスクを考えてとりあえずやめておいた。

fluentdの正規表現チェックツール

参考

secureログを分析してみた (CentOS->fluentd->BigQuery)

[redash][fluentd]/var/log/secureのログをS3とRedshiftに投げてre:dashと連携してみた