BigQuery サンプルクエリへのエクスポート

このページでは、BigQuery にデータをエクスポートした後、アセットデータを分析するための SQL クエリの例を紹介します。詳しくは、標準 SQL クエリ構文をご覧ください。

使用可能な列で直接照会する

各アセットタイプの数量を検索するには、次のクエリを実行します。

SELECT asset_type, COUNT(*) AS asset_count
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
GROUP BY asset_type
ORDER BY asset_count DESC

繰り返しフィールドで照会する

メンバーとして Gmail アカウントを含む Identity and Access Management(IAM)ポリシーを検索するには、次のクエリを実行します。BigQuery は、UNNEST を使用して繰り返しフィールドを平坦化し、直接照会できるテーブルを作成します。

SELECT name, asset_type, bindings.role
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(iam_policy.bindings) AS bindings
JOIN UNNEST(bindings.members) AS members
WHERE members like "%@gmail.com"

パブリック IP を使用して作成できる組織、フォルダ、プロジェクトを検索するには、次のクエリを実行します。SSL またはプロキシが構成されている場合を除き、Cloud SQL インスタンスでパブリック IP を許可することで脆弱性を引き起こす可能性があるため、このクエリが役立ちます。

SELECT name
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(org_policy) AS op
WHERE
  op.constraint = "constraints/sql.restrictPublicIp"
  AND (op.boolean_policy IS NULL OR op.boolean_policy.enforced = FALSE);

同じ VPC Service Controls のサービス境界内の組織、フォルダ、プロジェクトを検索するには、次のクエリを実行します。

SELECT service_perimeter.title, service_perimeter.status.resources
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
CROSS JOIN UNNEST(service_perimeter.status.resources) as resource
WHERE resource = "projects/PROJECT_NUMBER";

抽出された JSON 文字列で照会する

ファイアウォール開放ルールを検索するには、次のクエリを実行します。BigQuery で使用される JSON 関数をご覧ください。

CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
""";

SELECT firewall.name, firewall.resource.parent, JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') AS sourceRanges
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS firewall
JOIN UNNEST(json2array(JSON_EXTRACT(firewall.resource.data, '$.sourceRanges'))) AS source_ranges
WHERE asset_type="compute.googleapis.com/Firewall" AND JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') IS NOT NULL AND JSON_EXTRACT_SCALAR(source_ranges, '$') = "0.0.0.0/0"

リソースタイプごとにテーブルを分割すると、より簡単で速いクエリにより、オープンなファイアウォール ルールを検索できます。

SELECT
  firewall.name, firewall.resource.parent, sourceRanges
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` AS firewall
JOIN UNNEST(firewall.resource.data.sourceRanges) AS sourceRanges
WHERE sourceRanges = "0.0.0.0/0";

結合されたリソースタイプで照会する

さまざまなリソースタイプのテーブルを結合するには、次のクエリを実行します。次の例は、VM が接続されていないすべてのサブネットワークを検索する方法を示しています。まず、クエリがすべてのサブネットワークを検索します。次に、そのリストから、selfLinks が存在しないサブネットワークを選択します。

SELECT name, JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') AS selflink
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS subnetwork
WHERE asset_type = "compute.googleapis.com/Subnetwork" AND (JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') NOT IN
(SELECT DISTINCT JSON_EXTRACT(network_interfaces, '$.subnetwork')
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` as instance
JOIN UNNEST(json2array(JSON_EXTRACT(instance.resource.data, '$.networkInterfaces'))) AS network_interfaces
WHERE asset_type ="compute.googleapis.com/Instance"
AND JSON_EXTRACT(instance.resource.data, '$.networkInterfaces') IS NOT NULL
)) IS NULL

リソースタイプごとにテーブルを分割すると、より簡単で速いクエリにより、VM が接続されていないすべてのサブネットワークを検索できます。

SELECT name, subnetwork.resource.data.selfLink
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_SUBNETWORK_TABLE_NAME` AS subnetwork
WHERE
  (
    subnetwork.resource.data.selfLink
      NOT IN (
        SELECT DISTINCT networkInterface.subnetwork
        FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` as instance
        JOIN
          UNNEST(instance.resource.data.networkInterfaces) AS networkInterface
        WHERE
          networkInterface IS NOT NULL
      )
  ) IS NULL;