Cloud Load BalancingのログをBigQueryで解析

世界各国で攻撃が増えているので、手元のGoogle Cloudハニーポット環境のログを解析してみました。
以前にWafCharmを導入した環境です。
https://qiita.com/pict3/items/6f408a95c04a80dadb6e

確認したついでに、その内容と手順を残しがてらに、公開しておこうと思います。どなたかのお役に立てば！

準備

ログをBigQueryに流し込む

LoggingのRouterから容易に設定可能。
Inclusion filterは、以下で設定した。

resource.type = http_load_balancer AND
resource.labels.backend_service_name = "<リソース名>"

解析

日付を指定してCloud Armorで検知したものを抜き出す

遮断したものは、enforcedsecuritypolicyにて確認できるが、検知（Preview）なものはpreviewsecuritypolicyを確認する必要がある。
この辺は、WafCharmさんのBlogがわかりやすい。
https://www.wafcharm.com/blog/gcp-check-waf-detection-status-ja/

SELECT 
  timestamp,
  jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.priority enf_priority,
  jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.priority pre_priority,
  httpRequest.remoteIp ip,
  jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.outcome enf_outcome,
  jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.outcome pre_outcome,
  httpRequest,
  jsonpayload_type_loadbalancerlogentry
FROM 
`<プロジェクトID>.log_analytics01_ds.requests` 
WHERE 
  DATE(timestamp) = "<指定日付>" 
  AND jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.outcome = "DENY"
  OR jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.outcome = "DENY"

LIMIT 1000

こんな感じで遮断あるいはDENY判定されたアクセスログを見ることができる。

Cloud Armorのルールに割り当てたPriorityと見比べることで、どのルールに引っかかったのか確認することができる。

アクセス元の国を調べる

過去の公式ブログを参考に。
https://cloud.google.com/blog/products/data-analytics/geolocation-with-bigquery-de-identify-76-million-ip-addresses-in-20-seconds

GeoLite2からデータをDL & BigQueryにアップロード

https://dev.maxmind.com/geoip/geolite2-free-geolocation-data
MAXMINDさんのアカウントを作って、GeoLite2 Country CSVをDL。
そこからBigQueryにデータをアップロードする。

今回はgeolite2というデータセットを作成し、以下のテーブル名で作成した。

• country-blocks-ipv4: GeoLite2-Country-Blocks-IPv4.csv
• country-locations: GeoLite2-Country-Locations-en.csv

IPアドレスと国名を包含したテーブルを作る

適当に英語ブログを読み飛ばしていると最初は気づかなかったが、アップロードして作ったテーブルからさらにテーブルを作る。
network_binとmaskが複雑な計算をしないミソのもよう。これはデータが増えた場合のBigQueryの利用料に関わってくるポイントと思う。

ここで出力されるIPアドレス群は、さまざまなサブネットで指定されている。

create table <プロジェクトID>.geolite2.coutry_ip4_locs as
(
SELECT *
  , NET.IP_FROM_STRING(REGEXP_EXTRACT(network, r'(.*)/' )) network_bin
  , CAST(REGEXP_EXTRACT(network, r'/(.*)' ) AS INT64) mask
FROM `<プロジェクトID>.geolite2.country-blocks-ipv4` 
JOIN `<プロジェクトID>.geolite2.country-locations`
USING(geoname_id)
)

アクセス元の国ごとにグルーピング

アクセスログから、アクセス元IPだけを抜き出したもの（source_of_ip_addresses）と、上記で作ったテーブルを照らし合わせて、アクセス元ランキングを算出する。
source_of_ip_addressesの各IPアドレスに対し、サブネットマスクがとりうる値（9~32）でマスクを掛けていき、順繰りにGeoLite2のデータ群と合致するものを探すようなイメージ。

WITH source_of_ip_addresses AS (
  SELECT REGEXP_REPLACE(httpRequest.remoteIp, 'xxx', '0')  ip, COUNT(*) c
  FROM `<プロジェクトID>.log_analytics01_ds.requests` 
  WHERE httpRequest.remoteIp IS NOT null  
  GROUP BY 1
)

SELECT country_name, SUM(c) c
FROM (
  SELECT ip, country_name, c
  FROM (
    SELECT *, NET.SAFE_IP_FROM_STRING(ip) & NET.IP_NET_MASK(4, mask) network_bin
    FROM source_of_ip_addresses, UNNEST(GENERATE_ARRAY(9,32)) mask
    WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4
  )
  JOIN `<プロジェクトID>.geolite2.coutry_ip4_locs`

  USING (network_bin, mask)
)
GROUP BY 1
ORDER BY 2 DESC

以下が結果。ハニーポットの役目を果たし、海外からの不正アクセスが多い。

まとめ的：日付を指定してCloud Armorで検知したものを抜き出したものに国名追加

httpRequest,　jsonpayload_type_loadbalancerlogentryは、DISTINCTと併用できないので諦める。

WITH accesslogs AS (
  SELECT 
  insertId,
  timestamp,
  jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.priority enf_priority,
  jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.priority pre_priority,
  httpRequest.remoteIp ip,
  jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.outcome enf_outcome,
  jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.outcome pre_outcome,
  severity,
FROM 
`<プロジェクトID>.log_analytics01_ds.requests` 
  , UNNEST(GENERATE_ARRAY(9,32)) mask 
WHERE 
  DATE(timestamp) = "<指定日付>" 
  AND jsonpayload_type_loadbalancerlogentry.enforcedsecuritypolicy.outcome = "DENY"
  OR jsonpayload_type_loadbalancerlogentry.previewsecuritypolicy.outcome = "DENY"
LIMIT 1000
) 

SELECT *
FROM (
  SELECT 
    DISTINCT insertId,
    timestamp,
    enf_priority,
    pre_priority,
    ip,
    country_name,
    enf_outcome,
    pre_outcome,
    severity,
  FROM (
    SELECT *, NET.SAFE_IP_FROM_STRING(ip) & NET.IP_NET_MASK(4, mask) network_bin
    FROM accesslogs , UNNEST(GENERATE_ARRAY(9,32)) mask
    WHERE BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4
  )
  JOIN `<プロジェクトID>.geolite2.coutry_ip4_locs`

  USING (network_bin, mask)
)

出力結果。
んーー。そこまでばら撒いていないハニーポット環境なのに攻撃が多い。気をつけねば。。。
そして、そのほとんどをちゃんとArmorで検知できていました。やっぱりWAFは重要だと思いました。