redash - ゆるふわキャンパー

Rewrite SQL for GCP billing export v1

先日課金情報エクスポート機能の正式版が発表され、課金情報のテーブルのスキーマが変更されました。 GCP billing export機能のGA版がリリースされて作成されるカラム名等が変わった GCP の課金情報を Google BigQuery へ送り Redash で可視化しているので、このための SQL を書き直す必要があります。 Visualize GCP Billing Data 書き換えた箇所こんな感じで毎月の Billing 情報を集計しています。これのための SQL を書き換えていきます。 before SELECT SUM(cost) AS sum_cost,

sshd

Visualize illegal ssh access attempts and access source

/var/log/secureをBigQueryに送り始め、sshdへのアクセスがあるIPアドレスを多い順に集計してみたりしています。 Send secure log to BigQuery Visualization of IP address accessing sshd 今回は日毎にssh接続の試行失敗回数を集計して、それに対するIPアドレスの内訳も出してみます。こんなクエリを作成してみます。 SELECT LEFT(FORMAT_UTC_USEC(UTC_USEC_TO_DAY(time * 1000000)), 10) AS day, COUNT(

heroku

Re:dash no longer supports Heroku

Re:dashをHeroku上にデプロイして利用しています。 redash - ゆるふわキャンパー Re:dashのバージョンはv0.12で、いい加減古い(最新はv1.0.3)のでアップデートしようとしたのですが…リポジトリにProcfileが無い！調べてみると、Herokuのサポートは公式ではしなくなるとのこと…。(´；ω；｀) Remove Procfile.heroku - it's no longer up to date. · getredash/redash@0e68228 · GitHub 皆によく使われていたであろう、Herokuの無料枠での利用がスペック不足でできなくなるのが主な理由みたいです。

security

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桁.

gcp

Report GCP billing and Referral domain everyday

少し前から、個人Slackに個人で利用しているGCPの使用料金とブログのリファラを集計して日時レポートとして出すようにしています。 Slack × Re:dash 以下のように、毎日午前11時にredash-reportチャンネルにレポートが届きます。忘れないうちに良かったと感じたことをメモしておきます。よかったこと GCPの使用状況を毎日意識することができる月ごとのグラフを出すようにしたので推移がわかり良い感じ…！ 2月から跳ね上がっている(といっても料金的には微量ですが)のはmackerelでGCP料金をホストメトリックに取り始めたからですね(毎分クエリ叩いてる&サーバの情報じゃないのでサービスメトリックに移行しなきゃあかん)　← みたいなことがわかったりどこから自サイトへのアクセスが多いのか毎日意識することができるアドベントカレンダー強いな…　もう5月になるやぞ楽しい日々変化するデータが可視化され、それを労力をかけずに観察できるのが楽しいサーバのメトリックとはちがう「集計されたユーザの生のデータ」というのも楽しさを感じている要因と思う slackのチャンネルを遡って履歴を辿れるのも良いやりたいこと以下のデータも出してみたいと思っています。攻撃っぽいアクセスの集計 syslogをBigQueryに送ってssh試行失敗数とそのアクセス元のIPアドレスを集計して表示とか ✨

redash

Slack × Re:dash

slackとre:dashを連携させてみました。GCPの料金とか、勝手にレポートしてくれたら嬉しいな〜と思いやってみました。連携方法下記ページにアクセスし、Add to Slackする。 Make Your Company Data Driven | Redash slackのログイン画面に遷移するので認証する。 slackにredashユーザが追加され、DMでre:dashのurlやapikeyの設定が対話形式で始まる。 apikeyはre:dashの管理ページから見れる。こんな感じでbotと設定を行う。 /redash クエリ名と打ってクエリを実行することができるようになる！クエリ名の大文字、小文字は区別されないっぽい。こりゃ便利ですね...! クエリのリンクを張っても反応する。 slackのremindコマンドから呼び出す remindコマンドを使えばslackで定期的にグラフを取得することが出来る。例として1分後にグラフを取得してみる。 /remind

redash

Visualize GCP Billing Data

GCPのBillingデータをGoogle BigQueryに送り始めました。 Export GCP Billing data to Google BigQuery 早速re:dashで可視化してみます。可視化する内容 Google BigQueryへ送られるデータの詳細は以下です。この中からてきとうに使えそうなものを拾っていきます。 Billing data in BigQuery - Cloud Platform Console Help 今回は、 X軸は月 Y軸はコストの合計また、Y軸にプロダクトの種類(BigQuery, Computing Engineなど)も含める

redash

Schedule query execution with re:dash

heroku上にre:dashを立てて利用しています。 re:dash on heroku 今回はre:dashのスケジュール機能を使って、特定のクエリを定期実行させてみます。設定方法クエリ編集画面からスケジューリングしたいクエリを選択して編集。 Yay!

redash

Visualization referral domains using google BigQuery

re:dashとGoogle BigQueryでアクセスログを可視化して遊んでいます。 bigquery - ゆるふわキャンパー redash - ゆるふわキャンパー最近Google BigQueryのドキュメントを眺めていてDOMAIN関数の存在を知ったので、これを使ってリファラのドメインを集計してre:dashで可視化してみます。 h2oでリファラをログに出力する方法ログフォーマットでリクエストヘッダを指定(%{Referer}i)することでリファラをアクセスログに出力することができます。 Access Log Directives - Configure - H2O - the optimized HTTP/2 server こんな感じでGoogle BigQueryに送られていっています。

h2o

Specify the status code by H2O

re:dash見てたら302ステータスコードがめっちゃ多い！本来301(恒久的な転送)であるべきなので301リダイレクトするようにh2oの設定を修正しました(301でリダイレクトしているだろうと勝手に思っていた...)。こんな感じで指定できる。 "ghost.ponpokopon.me:443": listen: port: 443 ... paths: "/": redirect: status: 301 url: "https://blog.lorentzca.me/" 適用後curlを叩いて動作確認。 Before $ curl --head

redash

re:dash on heroku

※ Re:dashのバージョン0.12.0向けの記事です(古い…)。 Re:dashの1系からは公式でherokuをサポートしなくなったようです。 Remove Procfile.heroku - it's no longer up to date. · getredash/redash@0e68228 · GitHub bigqueryにサーバのログを送っています。 Send h2o log to BigQuery せっかくなので、ビジネス・インテリジェンスツールの一つであるre:dashを使って可視化環境を構築してみます。 GitHub -