Beispielabfragen für BigQuery exportieren

Auf dieser Seite werden Beispiel-SQL-Abfragen aufgeführt, mit denen Sie Ihre Asset-Daten nach dem Export in BigQuery analysieren können. Weitere Informationen finden Sie unter Standard-SQL-Abfragesyntax.

Verfügbare Spalten direkt abfragen

Führen Sie die folgende Abfrage aus, um die Mengen der einzelnen Asset-Typen zu ermitteln:

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

Wiederkehrende Felder abfragen

Führen Sie die folgende Abfrage aus, um IAM-Richtlinien (Identify and Access Management) zu suchen, die Gmail-Konten als Mitglied enthalten. BigQuery verwendet UNNEST, um wiederkehrende Felder in einer Tabelle zu vereinfachen, die direkt abgefragt werden können:

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

Führen Sie die folgende Abfrage aus, um eine Organisation, einen Ordner oder ein Projekt zu finden, das die Erstellung mithilfe einer öffentlichen IP-Adresse zulässt. Diese Abfrage ist nützlich, da das Zulassen öffentlicher IP-Adressen bei Cloud SQL-Instanzen Sicherheitslücken verursachen kann, es sei denn SSL oder ein Proxy ist konfiguriert:

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

Führen Sie die folgende Abfrage aus, um eine Organisation, einen Ordner oder ein Projekt innerhalb desselben VPC Service Controls-Dienstperimeters eines Projekts zu finden:

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

Extrahierten JSON-String abfragen

Führen Sie die folgende Abfrage aus, um offene Firewallregeln zu suchen: Weitere Informationen zu JSON-Funktionen in 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"

Durch das Verteilen von Tabellen pro Ressourcentyp sind offene Firewallregeln mit einer einfacheren und schnelleren Abfrage verfügbar.

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

Verknüpfte Ressourcentypen abfragen

Führen Sie die folgende Abfrage aus, um Tabellen verschiedener Ressourcentypen zu verknüpfen. Das folgende Beispiel zeigt, wie Sie alle Subnetzwerke finden, denen keine VM angehängt ist. Zuerst sucht die Abfrage alle Subnetzwerke. In dieser Liste werden dann die Subnetzwerke ausgewählt, deren selfLinks nicht vorhanden sind.

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

Durch das Unterteilen der Tabellen pro Ressourcentyp können wir alle Subnetzwerke ermitteln, denen keine VM mit einer einfacheren und schnelleren Abfrage angehängt wurde.

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;