Contoh kueri SQL BigQuery

Halaman ini mencantumkan contoh kueri SQL untuk menganalisis data aset setelah Anda mengekspornya ke BigQuery. Lihat sintaksis Kueri SQL standar untuk mengetahui informasi selengkapnya.

Catatan: Beberapa kolom eksperimental mungkin terlihat kosong pada awalnya di BigQuery. Jangan menetapkan dependensi pada kolom yang terpengaruh hingga kolom tersebut diisi. Untuk mengetahui kolom mana yang bersifat publik, lihat dokumen penemuan untuk mengetahui referensi tersebut.

Kueri langsung pada kolom yang tersedia

Untuk menemukan jumlah setiap jenis aset, jalankan kueri berikut:

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

Kueri pada kolom berulang

Untuk menemukan kebijakan Identity and Access Management (IAM) yang memberikan akses ke akun Gmail, jalankan kueri berikut. BigQuery menggunakan UNNEST untuk meratakan kolom berulang menjadi tabel yang dapat Anda buat kuerinya secara langsung:

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 principals
WHERE principals like "[e-mail]"

Untuk menemukan organisasi, folder, atau project yang mengizinkan pembuatan menggunakan IP publik, jalankan kueri berikut. Kueri ini berguna karena mengizinkan alamat IP publik dengan instance Cloud SQL dapat menyebabkan kerentanan, kecuali jika SSL atau proxy dikonfigurasi:

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);

Untuk menemukan organisasi, folder, atau project dalam perimeter layanan Kontrol Layanan VPC yang sama, jalankan kueri berikut:

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_ID";

Kueri pada string JSON yang diekstrak

Untuk menemukan aturan firewall yang terbuka, jalankan kueri berikut. Pelajari lebih lanjut fungsi JSON yang digunakan di BigQuery.

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"

Dengan memisahkan tabel per jenis resource, kita bisa menemukan aturan firewall terbuka dengan kueri yang lebih mudah dan cepat.

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";

Kueri tentang jenis resource yang digabungkan

Untuk menggabungkan tabel dari berbagai jenis resource, jalankan kueri berikut. Contoh berikut menunjukkan cara menemukan semua subnetwork yang tidak memiliki VM yang terpasang. Pertama, kueri akan menemukan semua subnetwork. Kemudian, dari daftar tersebut, sistem akan memilih subjaringan yang selfLinks-nya tidak ada.

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

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

Dengan memisahkan tabel per jenis resource, kita dapat menemukan semua subnetwork tanpa VM terpasang, dengan kueri yang lebih mudah dan cepat.

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;

Kueri untuk menemukan cluster Dataproc yang rentan karena CVE-2021-44228

CREATE TEMP FUNCTION vulnerable_version(imageVersion STRING)
RETURNS BOOL
LANGUAGE js AS r"""
    const version_regexp = /(?<major>\d+)(?:\.)(?<minor>\d+)(?:\.)?(?<sub>\d+)?/g;
    let match = version_regexp.exec(imageVersion);
    if(match.groups.major < 1){
        return true;
    }
    if (match.groups.major == 1){
        if (match.groups.minor < 3){
            return true;
        }
        if(match.groups.minor == 3 &amp;&amp; match.groups.sub < 95){
            return true;
        }
        if(match.groups.minor == 4 &amp;&amp; match.groups.sub < 77){
            return true;
        }
        if(match.groups.minor == 5 &amp;&amp; match.groups.sub < 53){
            return true;
        }
    }
    if (match.groups.major == 2 &amp;&amp; match.groups.minor == 0 &amp;&amp; match.groups.sub < 27){
        return true;
    }
    return false;
""";

SELECT
  c.name,
  c.resource.data.projectId AS project_id,
  c.resource.data.clusterName AS cluster_name,
  c.resource.data.config.softwareConfig.imageVersion AS image_version,
  c.resource.data.status.state AS cluster_state,
  vulnerable_version(c.resource.data.config.softwareConfig.imageVersion) AS is_vulnerable
FROM
  `PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_dataproc_googleapis_com_Cluster` c
  INNER JOIN `PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_cloudresourcemanager_googleapis_com_Project` p
    ON p.resource.data.projectId = c.resource.data.projectId
WHERE
  c.resource.data.config.softwareConfig.imageVersion IS NOT NULL
  AND c.resource.data.status.state = "RUNNING"
  AND p.resource.data.lifecycleState = "ACTIVE";