BigQueryで国別のアクセスリストを作成

実現したいこと

GCPのロードバランサーのログをもとにどの国からどれくらいアクセスがあるのか可視化したい

使用技術

GCP（BigQuery, Cloud Logging）

用意するもの

IPアドレスから国名を特定するため、MaxMind社が提供する無料のIP地理情報データベース「GeoLite2」を使用します。

上記サイトから「GeoLite2-City-CSV」をダウンロードしてその中の以下の2つのCSVファイルを使います。これらをBigQueryにテーブルとしてインポートしておきます。

• GeoLite2-City-Blocks-IPv4.csv（IPレンジと地域IDの対応表）
• GeoLite2-City-Locations-en.csv（地域IDと国名の対応表）

実装の大きな流れ

大きく分けて以下の2ステップを踏みます。

①ログとIPアドレス情報をBigQueryに集める（データ準備）

②アクセス元のIPアドレスを国名に変換する（BigQuery）

③国ごとにアクセス数を数える（BigQuery）

①ログとIPアドレス情報をBigQueryに集める

分析元となるCloud LoggingのログをBigQueryに転送します。
GCPの標準機能であるCloud Loggingのシンクを作成することで継続的にログをBigQueryに転送できます（シンク作成前のログは、一度GCSにエクスポートしてからBigQueryで参照する必要があります）。

②アクセス元のIPアドレスを国名に変換する（BigQuery）

ログデータとGeoLite2のデータを結合します。
JOIN①: ログのIPアドレスを geoip_blocks_ipv4 テーブルと突き合わせ、対応する「地域ID」を特定
JOIN②: 取得した「地域ID」でgeoip_locations_en テーブルとJOINし、「国名」を取得

③国ごとにアクセスをカウントする

全体の流れ

SELECT
  locations.country_name,
  COUNT(*) AS access_count
FROM
  '{BigQuery上の分析元ログテーブル名}' AS logs
JOIN
  'geoip_blocks_ipv4' AS blocks
  ON JOIN
  'geoip_locations_en' AS locations
  ON blocks.geoname_id = locations.geoname_id
GROUP BY
  locations.country_name
ORDER BY
  access_count DESC;

IPアドレスの具体的な結合方法については、以下の記事が参考になります。

まとめ

GCPの標準機能と無料IPデータベースのGeoLite2を組み合わせることで、ロードバランサーのログから国別アクセスリストを集計できることが分かりました。

参考