Visualization of IP address accessing sshd

/var/log/secureをBigQueryに送り始めました。

• Send secure log to BigQuery

1日あたり十数MB分ほどのペースで溜まってきているようです。

これを使って何かしたいなということで、とりあえずsshdへアクセスしてきているIPアドレスを可視化してみます。

sshdへのアクセスのログはFailed keyboard-interactive/pam for root from <IPアドレス> port 46472 ssh2のような感じでメッセージ内にアクセス元のIPアドレスが記録されているので、これを使います。

以下のクエリを使います。

• REGEXP_EXTRACTで正規表現に合致した文字列を抽出できるので頑張ってIPアドレスを抽出(数値1~3桁.数値1~3桁.数値1~3桁.数値1~3桁からなる文字列)
• 期間はTABLE_DATE_RANGE関数で指定
• ident(identification?識別子?)がsshd
• アクセスが多い順にTOP50個に絞っている

SELECT
  REGEXP_EXTRACT(message, r'(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})') AS ip_address,
  COUNT(ident) AS count_ident
FROM
  TABLE_DATE_RANGE(prod.syslog_secure_, DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())
WHERE
  ident = "sshd"
GROUP BY
  ip_address
ORDER BY
  count_ident DESC
LIMIT
  50

Re:dashで以下のような円グラフにしました。

感想

TOP50個全て心当たりのないIPアドレスなんですが… ((((;゜Д゜)))

どんな弱小サーバーでもインターネットに公開されている限り、不正ログインBOTによる不正ログインの試みをされ続けているという現実を改めて認識することができました…。

可視化されたので目立ってアクセスが多いものはBANしていこう。

参考

• クエリ リファレンス