redash

A collection of 11 posts
Rewrite SQL for GCP billing export v1
gcp

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, product, LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(TIMESTAMP_TO_USEC(start_time))), 7) AS month
— 1 min read
Visualize illegal ssh access attempts and access source
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(ident) AS count_ident, REGEXP_EXTRACT(message, r'(\d{1,3}\.\d{1,3}\.\d{1,3}
— 1 min read
Re:dash no longer supports Heroku
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の無料枠での利用がスペック不足でできなくなるのが主な理由みたいです。 一応Procfileをサルベージしてgunicorn -b 0.0.0.0:$PORT -w4 redash.wsgi:appと修正すれば使えるようですが…。 頑張れば利用できるけどおすすめはしないよとのこと。Herokuでちゃんと全てまかなっている人ならいいかもしれないってコメントされていた。 * Crashes on Her
— 1 min read
Visualization of IP address accessing sshd
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 port 46472 ssh2のような感じでメッセージ内にアクセス元のIPアドレスが記録されているので、これを使います。 以下のクエリを使います。 * REGEXP_EXTRACTで正規表現に合致した文字列を抽出できるので頑張ってIPアドレスを抽出(数値1~3桁.数値1~3桁.数値1~3桁.数値1~3桁からなる文字列) * 期間はTABLE_DATE_RANGE関数で指定 * ident(identification?識別子?)がsshd * アクセスが多い順にTOP50個に絞っている SELECT REGEXP
— 1 min read
Report GCP billing and Referral domain everyday
gcp

Report GCP billing and Referral domain everyday

少し前から、個人Slackに個人で利用しているGCPの使用料金とブログのリファラを集計して日時レポートとして出すようにしています。 * Slack × Re:dash 以下のように、毎日午前11時にredash-reportチャンネルにレポートが届きます。 忘れないうちに良かったと感じたことをメモしておきます。 よかったこと * GCPの使用状況を毎日意識することができる * 月ごとのグラフを出すようにしたので推移がわかり良い感じ…！ * 2月から跳ね上がっている(といっても料金的には微量ですが)のはmackerelでGCP料金をホストメトリックに取り始めたからですね(毎分クエリ叩いてる&サーバの情報じゃないのでサービスメトリックに移行しなきゃあかん)　← みたいなことがわかったり * どこから自サイトへのアクセスが多いのか毎日意識することができる * アドベントカレンダー強いな…　もう5月になるやぞ * 楽しい * 日々変化するデータが可視化され、それを労力をかけずに観察できるのが楽し
— 1 min read
Slack × Re:dash
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 @here t
— 2 min read
Visualize GCP Billing Data
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など)も含める みたいな感じで集計してみます。 クエリ こんなクエリでやってみる。 SELECT SUM(cost) AS sum_cost, product, LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_
— 2 min read
Visualization referral domains using google BigQuery
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に送られていっています。 $ bq query "SELECT referer FROM prod.h2o_access_log_20170118 where referer != \"-\" group by referer LIMIT
— 2 min read
Specify the status code by H2O
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 http://ghost.ponpokopon.me/ HTTP/1.1 302 Redirected ... After $ curl --head http://ghost.ponpokopon.me/ HTTP/1.1 301 Redirected ... Yay! 参考
— 1 min read
re:dash on heroku
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 - getredash/redash herokuでre:dashを動かしてパスワードログイン出来るところまでやってみます。基本的にドキュメントに沿ってやってます。 * Setting up Re:dash instance — Re:dash documentation まずはre:dashのダウンロードから。 * git cloneで取ってくるとうまくgit
— 2 min read