bigquery

A collection of 22 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
rsyslogd was HUPed
linux

rsyslogd was HUPed

/var/log/secureをGoogle BigQueryへ送っています。 * Send secure log to BigQuery 最近/var/log/secureがGoogle BigQueryに送られていないことに気づき(Redashのグラフを見ていて気づいた)、なんでだろうと思ったら/var/log/messagesにこんなログが複数あった。 Oct 14 18:07:01 lorentzca rsyslogd: [origin software="rsyslogd" swVersion="7.4.7" x-pid="451" x-info="http://www.rsyslog.com"] rsyslogd was HUPed logrorateがHUPを送っているだけ?今回の件に関係があるのか不明…。 Google BigQueryのテーブルを見ると9月22日を最期に止まっていたようだった。 おそらくGhost1.0にアップデートするついでにサーバも作り直したタイミング。 なぜrsyslogが止まったのか、
1 min read
GCP billing export GA version has released
gcp

GCP billing export GA version has released

Google BigQueryへGCPのBilling情報を送っています。 * Export GCP Billing data to Google BigQuery このbilling export機能について、先日メールで案内が配信されました。 内容は、billing export機能がGA(正式版)になり、それにともなってスキーマが変わるから移行してね、というものでした。ざっくり要約すると以下のような内容です。 * 暫くの間は新しいスキーマと古いスキーマ両方作成される * 2018年1月に古いスキーマの作成が行われなくなるのでそれまでに既存のワークフローの更新が必要 具体的には以下のような変更があるとのことです。 * productカラムがservice.descriptionにリネーム * resource_typeカラムがsku.descriptionにリネーム * start_timeとend_timeカラムがusage_start_timeとusage_end_timeにリネーム * service. idカラムの追加 *
1 min read
Post GCP Billing data to Mackerel with Node.js
mackerel

Post GCP Billing data to Mackerel with Node.js

Node.jsのBigQueryライブラリを使ってGCPのBillingを取得してみました。 * Get GCP Billing with Node.js 次はこのBillingの情報をMackerelにサービスメトリックとして送ってみます。 Mackerel側のインターフェースの確認 サービスメトリックは以下のデータ形式で送れば良いみたいです。 [ , , … ] 詳細はこちら。 * サービスメトリック - Mackerel API ドキュメント (v0) つまり、今回の場合は以下のようなデータを送れば良いみたいです。 [ {name: "gcp.monthly_cost", time: "1234567890", value: "0.1567"} ] Node.jsのコードを書く 以下のようなコードを用意します。 // Imports the Google Cloud client library // Instantiates a client var bigquery
2 min read
Get GCP Billing with Node.js
gcp

Get GCP Billing with Node.js

Node.jsのBigQueryクライアントライブラリを使ってみました。 * Node.js client library for Google BigQuery 今回はこれを使ってGCPのBillingデータを取得してみます。 ※ GCPのBillingデータをBigQueryに送る方法は以下。 * Export GCP Billing data to Google BigQuery 以下のようなコードを書いてみます。 // Imports the Google Cloud client library // Instantiates a client var bigquery = require('@google-cloud/bigquery')({ projectId: 'GCPのプロジェクトID', keyFilename: 'BigQueryへアクセスするためのjsonキーのpath' }); // Query var query = 'SELECT SUM(cost) AS sum_cost, LEFT (FORMAT_UTC_
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
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
Send secure log to BigQuery
td-agent

Send secure log to BigQuery

/var/log/secureをBigQueryに送ってRe:dashで攻撃っぽいアクセスなどを可視化してみたかったので送ってみました。 準備 * /var/log/secureをtd-agentでも読めるようにパーミッションを下げる * /var/log/secureがローテートされてもパーミッションが戻らないようにlogrotateの設定を変更する td-agentの設定 td-agentの設定を追加します。 * 部分でログの送信元のサーバのホスト名をレコードに追加している # syslog (secure log) @type tail format syslog path /var/log/secure tag syslog.secure pos_file /var/log/td-agent/syslog.secure.pos @type record_transformer
2 min read
Node.js client library for Google BigQuery
nodejs

Node.js client library for Google BigQuery

先日Google Cloud Functionsを試したところ、まだNode.jsしか対応していないことがわかりました。 * Hello Google Cloud Functions やりたいこととして、Google Cloud FunctionsからGoogle BigQueryにためているGCP Billingデータを取得したいというものがあるので、今回はNode.jsでGoogle BigQueryのAPIを叩くところまでやってみます。 GoogleがBigQuery用のクライアントライブラリをNode.jsでも提供しているので、それを使います。 準備 ドキュメントをみながら進めます。 * BigQueryクライアントライブラリ - Node.js まずgcloudコマンドを使ってGCPにログインしておきます。 gcloud beta auth application-default login 次にnpmコマンドでGoogle BigQueryのクライアントライブラリをインストールします。 npm install --save @
1 min read
Change H2O log format to LTSV
bigquery

Change H2O log format to LTSV

h2oのログをtd-agentを使ってGoogle BigQueryに送っています。 * Send h2o log to BigQuery 今まで私はtd-agentのtailプラグインの設定で、フォーマットを愚直に正規表現で設定していました。 format /(?[^ ]*) (?[^ ]*) (?[^ ]*) (?[^\]]*) (?[^ ]* +\S* +\S*) (?[^ ]*) (?[^ ]*) "(?[^\"]*)" "(?[^\"]*)"/ 課題として、以下の不満がありました。 * 見辛い * h2oのログフォーマットを変えたとき追従して正規表現を書き直さないといけない そこでh2oのアクセスログのフォーマットをLTSVに変更してtd-agentでltsvフォーマットを使えるよ
2 min read
Bdash
bi

Bdash

BIツールにre:dashを使っています。 * redash - ゆるふわキャンパー 今朝Twitterを見ていたら、新しいBIツールのBdashが発表されていたのでどんなものか使ってみました。 * BdashというBIツールをリリースしました - hokaccha hamalog v3 * GitHub - bdash-app/bdash: A simple business intelligence application. 特徴 BIツールはre:dashしか使ったことがないのですが、re:dashと比べると以下のような特徴があります。 * Electronで作成されたデスクトップアプリケーション * gistで共有できる 使ってみる データソースとしてGoogle BigQueryをつなぎたいのでまずGCPの管理画面から新しくBdash用の認証アカウントを作成します。 * 認証情報(とサービスアカウント)の作成参照 鍵ファイルを取得したらBdashに設定して完了。 こんな感じで直感的に使えます。 gistへの
1 min read
Send GCP Billing metrics to Mackerel
gcp

Send GCP Billing metrics to Mackerel

Google BigQueryへエクスポートしたGCP Billingデータを使ってmackerelにGCP Billingのメトリクスを送ってみます。 メトリクスを送るためのプラグインは夜な夜な作りました。オプションにプロジェクトIDとデータセット名とテーブル名を指定すると現日付けの月の合計のBillingデータを取得することが出来るプラグインです。早く使ってみたい! * GitHub - Lorentzca/mackerel-plugin-gcp-billing: GCP billing costom metrics plugin for mackerel 準備 このプラグインは実行するための条件がいくつかあります。 テーブルに以下のスキーマが必要 * cost(FLOAT型) * start_time(TIMESTAMP型) 課金データのエクスポート機能でGoogle BigQueryにデータを送っていれば特に考えることはないはず。 サーバ上で実行するにはGoogle BigQueryに対する権限を持っている認証アカウントのjsonキーが必
2 min read
Trying out golang BigQuery client library
bigquery

Trying out golang BigQuery client library

GCPのbillingをbigqueryにエクスポートしてre:dashで可視化してみました。 * Export GCP Billing data to Google BigQuery * Visualize GCP Billing Data 次はmackerelにbillingの情報を送ってメトリクスとして記録してみたいので、まずはgolangからbigqueryに対し何かしらのクエリを叩くところまでやってみます。 以下のドキュメントを見ながら試してみます。 * BigQuery クライアント ライブラリ 準備 ライブラリのインストール。 go get cloud.google.com/go/bigquery go get google.golang.org/api/iterator * cloud.google.com/go/bigqueryはbigqueryのapiを叩くライブラリ * ちなみにこのライブラリはまだβ版なので注意 * google.golang.org/api/iteratorはgoogle ap
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
Export GCP Billing data to Google BigQuery
bigquery

Export GCP Billing data to Google BigQuery

Google BigQueryを使うようになって暫く経ちました。 * bigquery - Page 1 - ゆるふわキャンパー そこで気になるのはGCPの使用料金です。Billingアラートを5ドルに設定したり、使いすぎないように気を使ってはいますが、どうせならちゃんとトラッキングしたいものです。 というわけでBillingの情報をGoogle BigQueryに記録する設定を有効にしてみました。 * Export billing data to BigQuery - Cloud Platform Console Help この機能、割と最近のもの?らしいですね。BillingデータをGoogle BigQueryに送る方法で調べると、いったんバケットにファイルとしてエクスポートしてそれをごにょごにょしてGoogle BigQueryに保存している記事が散見されました。 やりかた 「お支払い」メニューからエクスポート先にGoogle BigQueryを選んで、プロジェクト名とデータセットを選択するだけ。なんて簡単なんだ。 Billingデー
1 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
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
Monitoring Fluentd with Mackerel and Tuning Fluentd buffer config
mackerel

Monitoring Fluentd with Mackerel and Tuning Fluentd buffer config

h2oのログをbigqueryに送り始めました。 * Send h2o log to BigQuery 今のところ特に問題は無いのですが、正直バッファの各設定値やリトライ回数のチューニングについてはどうすれば良いのかよくわからないのでなんとかしたいところです。 そこで、mackerel-agent-pluginsを導入してみます。メトリクスを取ることでチューニングしやすくなるだろうと言う目論見です。 * mackerel-agent-plugins/mackerel-plugin-fluentd バッファについては、下記のサイトが分かりやすかったです。 * Fluentdの仕組み -バッファ機能でログ収集漏れを防ぐ- | Tech-Sketch * BufferedOutput pluginの代表的なoptionについて - Qiita td-agent側の準備 まずtd-agent.confでmonitor_agentプラグインを有効化します。下記設定を追加してtd-agentを再起動。 * Monitoring Fluentd | F
4 min read
Add logsource hostname to record
bigquery

Add logsource hostname to record

bigqueryに送るレコードに送り元のホスト名を追加してみます。 送り元ホストを条件に検索できるようになります。今のところ個人で複数のサーバを運用する予定はないので別に送り元ホストを条件に検索することも無いのですが、こまけーこたぁいいんだよ! filterプラグイン(組み込み)を利用して実現します。 * record_transformer Filter Plugin | Fluentd filterプラグインを使うと、td-agentのレコード(tailしたログファイルの行など)にデータを追加することが出来ます。 イメージ的には以下のような感じでしょうか。 [time, tag, record = {ip: "1.2.3.4", status: "200"}] ↓ [time, tag, record = {ip: "1.2.3.4", status: "200", logsource_hostname: "ponpokopon.me"}] td-agentの(fluentdの)データ構造については、以下の記事が分かりやすかったです。 *
2 min read
Add new column to BigQuery
bigquery

Add new column to BigQuery

h2oのログにUesr-Agent情報も追加した。ログのフォーマットで%{HEADERNAME}iとすると、任意のレスポンスヘッダを出力させることが出来る。 * Access Log Directives - Configure - H2O - the optimized HTTP/2 server access-log: path: /var/log/h2o/access.log format: "%h %l %u %{%Y/%m/%d:%H:%M:%S}t \"%r\" %s %b \"%{User-agent}i\"" bigqueryにh2oのログを送っているので、td-agentとbigquery側でも対応する。 既存のテーブルにUser-Agent用のカラムを追加する まず既存のテーブルのフォーマットを取得する。jqでフィールド情報のみを抽出すると便利。以下私のbigqueryの例。 bq --format=prettyjson
3 min read
Send h2o log to BigQuery
h2o

Send h2o log to BigQuery

h2oのアクセスログをtd-agentを使ってGoogle BigQueryに送ってみる。幾つか手順が必要。 * bigqueryの設定 * スキーマ定義ファイルの作成 * td-agentの設定 bigqueryの設定 bigqueryへアクセスするためにapiを有効化し、アクセスキーを発行するのだが、この工程が長い…。(´・_・`) 慣れてないと混乱する。 gcpアカウントの作成 作る。 * Google Cloud Computing, Hosting Services & APIs  |  Google Cloud Platform プロジェクトの作成 請求先アカウントの設定 bigqueryのapiを有効化 認証情報(とサービスアカウント)の作成 これでやっと以下のようなjson形式の鍵ファイルがダウンロードされる。td-agnetの設定で使う。 { "type": "......", "project_id": "......", "pr
4 min read