WoWHoneypot-BigQueryをRedashで可視化(Map編)

公開日:2019-02-28
最終更新:2019-02-28

WoWHoneypotを無事にBigQueryに入れたら、今度は可視化をやってみたくなるのが人情というもの…
特に世界地図でどこからのアクセス?っていう図はどの可視化ツールでもよくみるサンプルだし、せっかく可視化するならぜひやってみたい!
ということで、やってみました

やることは以下の通り
1.可視化ツールインストール
2.データソース作成
3.BigQuery上にIPと地理情報を紐付けるテーブル作成
4.実際のアクセスログを集計しているテーブルとJOINして国別に集計
5.可視化ツールでMapでクエリ結果を描画設定
6.ダッシュボードに貼り付けて完成!

1.Redashインストール@ローカルPC

今回はローカルPCにdocker入れた
Docker Based Developer Installation Guide

#docker-compose up

で立ち上げたらブラウザでlocalhostに接続
メールアドレスやらパスワードやら設定したらログインできます

2.BigQueryに接続@Redash

下記を登録してデータソースの作成
Name / Project ID / JSON Key File / Processing Location
Test Connectionで接続テストしたらSave
※JSON Key Fileは [サービス アカウント キーの作成] ページで作成
 BigQuery クライアント ライブラリ

3.Geolite2から地理情報取得@BigQuery

@yumanoさんの記事の通り
GeoLite2をBigQueryに格納してGeoLocationを取得(IPアドレス→国情報変換)

ここでの「yumano.geolite2_city」を「geolite2_cityのテーブル名」で読み替え突き合せのためのテーブルを作成

4.Queryの作成@BigQuery&Redash

@yumanoさんのブログでの「IPアドレスが入っているテーブルとJOIN」に該当する箇所に該当するのが下記のクエリ。
このクエリを事前にBigQueryで書式チェックして問題なければRedashに貼り付け

SELECT  
    IFNULL(country_name, 'Other') AS country_name,  
     SUM(1)  
FROM (  
  SELECT  
    clientip,  
    NET.IPV4_TO_INT64(NET.IP_FROM_STRING(clientip)) AS clientIpNum,  
    TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(clientip))/(256*256)) AS classB  
  FROM  
    `WoWHoneypotのテーブル名(ワイルドカード指定可能)` ) AS a  
LEFT OUTER JOIN  
  `geolite2_cityのテーブル名` AS b  
ON  
  a.classB = b.classB  
  AND a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum  
GROUP BY country_name

BigQueryで試した結果例は下記のとおり。
テーブルをワイルドカード指定できるので、日ごとでも月ごとでも算出可能

5.Map作成@Redash

いよいよMap作成
Visualizationを作成しMap (Choropleth)を選択
Country code ColumnにCountry_nameを入れて、Country code typeにはShort nameを選択
Valie formatは小数点なしにする

6.完成!

こんな感じでダッシュボードに貼り付けとけば完成

■参考
GeoLite2をBigQueryに格納してGeoLocationを取得(IPアドレス→国情報変換)

記事が少しでもいいなと思ったらクラップを送ってみよう!
0
+1
メモ書きを晒しておけば、誰かの役にたつかも?

よく一緒に読まれている記事

0件のコメント

ブログ開設 or ログイン してコメントを送ってみよう
目次をみる

技術ブログをはじめよう

Qrunch(クランチ)は、ITエンジニアリングに携わる全ての人のための技術ブログプラットフォームです。

技術ブログを開設する

Qrunchでアウトプットをはじめよう

Qrunch(クランチ)は、ITエンジニアリングに携わる全ての人のための技術ブログプラットフォームです。

Markdownで書ける

ログ機能でアウトプットを加速

デザインのカスタマイズが可能

技術ブログ開設

ここから先はアカウント(ブログ)開設が必要です

英数字4文字以上
.qrunch.io
英数字6文字以上
ログインする